Help sought: A complex 3 way join with subselect (MySQL)

Forum Moderators: open

Message Too Old, No Replies

Help sought: A complex 3 way join with subselect (MySQL)

Maybe a 4 way join, maybe a subselect, I really don't know

salnajjar

6:22 pm on Jun 15, 2009 (gmt 0)

I have 4 tables which I need to query, I've stripped all the non relevant fields, the tables are structured the following ways:

imagefiles: imageid(INT 11 - Unique), loginid(INT 11), filename(VARCHAR 255)
imagetext: imageid(INT 11 - Unique), title(TINYTEXT), description(MEDIUMTEXT)
imagekeywords: keywordid (INT 11 - Unique), keyword(VARCHAR 255)
keywordmappings: keywordid (INT 11), imageid (INT 11)

I want to retrieve the following information, ideally in one query:
Select all imageid's and filename's from the imagefiles table that match the specified loginid.
Select the corresponding title and description entries from the imagetext table that match each imageid.
Select the keywords from the keywords table that are mapped to each imageid in the keywordmappings table.

I'm quite new to joins, but I believe that's what I need to do, possibly with a subselect. If anyone can help shed some light then I would be extremely grateful.

Thanks ever so much

Seri

coopster

4:09 pm on Jun 16, 2009 (gmt 0)

An INNER JOIN will require each corresponding table to have an entry. You can use a LEFT JOIN to the table if there may not be a corresponding entry. However, I'm guessing you will always have a relationship ...

SELECT 
  imagefiles.imageid, 
  imagefiles.filename, 
  imagetext.title, 
  imagetext.description, 
  imagekeywords.keyword 
FROM imagefiles 
INNER JOIN imagetext ON (imagefiles.imageid = imagetext.imageid) 
INNER JOIN keywordmappings ON (imagefiles.imageid = keywordmappings.imageid) 
INNER JOIN imagekeywords ON (keywordmappings.keywordid = imagekeywordds.keywordid) 
WHERE imagefiles.loginid = $loginid 
;

Demaestro

4:11 pm on Jun 16, 2009 (gmt 0)

SELECT
im.imageid,
im.loginid,
im.filename,
it.title,
it.description,
ik.keywordid,
ik.keyword
FROM
imagefiles im
LEFT JOIN
imagetext it on im.imageid = it.imageid
LEFT JOIN
keywordmappings km on km.imageid = it.imageid
LEFT JOIN
imagekeywords ik on ik.keywordid = km.keywordid

This should work.... keep in mind it is all about the relating ids... study the query and understand why it works. Once you see the joins in your head you will be able to write others yourself.

Good Luck and ask if you have any other questions.

salnajjar

2:30 pm on Jun 18, 2009 (gmt 0)

Thanks guys, the code that I managed to use to make it work in the end was:

SELECT ifs.imageid, ifs.folder, ifs.filename, it.imagename, it.imagedescription, ikw.keyword 
 FROM imagefiles ifs 
 LEFT JOIN imagetext it ON ifs.imageid = it.imageid 
 LEFT JOIN keywordmappings kwm ON it.imageid = kwm.imageid 
 LEFT JOIN keywords ikw ON kwm.keywordid = ikw.keywordid 
 WHERE ifs.loginid = '$loginid' 
 ORDER BY imageid ASC 
 LIMIT $startnumber,25

I think I'm now starting to understand joins a bit better.