Geek asks and answers

MySql SELECT two tables and multi-row support

table event
id   idArtist    City       Img
__________________________________
1      51        Lonon     01.jpg
.. . .. .. .etc


table artist
id   Name    Img01    Img02    Img03   Img_Txt01   Img_Txt02  Img_Txt03  
_________________________________________________________________________
51   Bob    54.jpg    01.jpg   NULL     text 01     Text 02

I need to display ALL Img from the event table and display its text stored in the Artist table (you see Img == Img02, then I need Img_Txt02)

How can i do this? Many thanks xxxxx

+3
source share
2 answers

Repeat based on OP clarification

Since you do not know which fields we will associate in the executor table (img01, img02, img03), I would suggest creating a view that improves the layout of the executor table so that we can reference it. Here is the code I would use:

SELECT id, Name, Img01 AS Img, Img_Txt01 AS Img_Txt
FROM artist
UNION ALL
SELECT id, Name, Img02, Img_Txt02
FROM artist
UNION ALL
SELECT id, Name, Img03, Img_Txt03
FROM artist

, . ( Img4, Img5 ..). :

SELECT event.*, images.Img_Txt
FROM event
INNER JOIN 
    (SELECT id, Name, Img01 AS Img, Img_Txt01 AS Img_Txt
    FROM artist
    UNION ALL
    SELECT id, Name, Img02, Img_Txt02
    FROM artist
    UNION ALL
    SELECT id, Name, Img03, Img_Txt03
    FROM artist) AS images
ON event.idArtist = images.id AND event.Img = images.Img_Txt
0
SELECT event.Img,artist.Img_Txt02 
  FROM event LEFT JOIN artist 
    ON event.idArtist=artist.id

, .

+4

More articles:


All Articles