CODEWITHSUNDEEP
phpmysql
Ste
Ste

Reputation: 1520

MySql SELECT two tables and multi row help

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 event table and display its Text stored in the Artist table ( you see Img == Img02 then i need Img_Txt02 )

How can i do?? Many thxxxxx

Upvotes: 3

Views: 186

Answers (2)

IAmTimCorey
IAmTimCorey

Reputation: 16757

Redo based upon OP clarification

Since you don't know which fields we will be linking on in the artist table (img01, img02, img03), I would suggest building a view that improves the layout of the artist table so that we can link on 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

This would give you a list that you can link on. It would also allow you to grow your artist table fairly simply (add Img4, Img5, etc.) You could also use this above statement directly in your final query like so:

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

Upvotes: 0

Sean Johnson
Sean Johnson

Reputation: 5607

SELECT event.Img,artist.Img_Txt02 
  FROM event LEFT JOIN artist 
    ON event.idArtist=artist.id

Assuming I understood your question correctly.

Upvotes: 4

Related Questions