MySQL Join

jasemhi

Hello- I am trying to do a join, but I keep getting either too many results, or none at all. I have table2 that has a user id, picture caption, and a picture id. The user can add captions to the pictures they add to their list. I also have table1 that has picture id, and picture caption (which may be null). I want to be able to show the original caption (from table1), or if there isn't a caption, show the caption that the person has created in table2. Here is what I tried:

SELECT table1.caption, table2.caption FROM table1, table2 WHERE (table1.caption = '$caption') AND (table2.caption = '$cation'

This works only if the user has created a caption where there was an original caption- the caption from table1 and table2 show. I want to be able to pull table1 caption and table2 caption, even if either table is missing a caption (null).

I also tried:

SELECT table1.caption, table2.caption FROM table1, table2 WHERE table1.caption = 'table2.caption

This brings up two different captions for table1 and table2 since there are 2 captions in table2. Table1 has 100+ captions and has been limited by only 2 captions added to table2, I assume. Once again, I want to show table1 caption and table2 caption, based on the picture id. How can I do this?

Sorry if this is hard to understand, it is difficult writing it out clearly.

pete_bisby

Just for a test I've created 2 tables - tblCaption1 and tblCaption2 - both, of which have a field called "caption". The tables contain the following information:

tblCaption1

Caption1
Caption2

tblCaption2

Caption2
Caption3

The following 2 queries have been created to show the differences with the joins you require:

SELECT A.caption, B.caption FROM tblCaption1 A LEFT JOIN tblCaption2 B ON A.caption=B.caption
--RESULTS---
caption   caption
---------   ---------
Caption1  NULL
Caption2  Caption2

SELECT A.caption, B.caption FROM tblCaption1 A RIGHT JOIN tblCaption2 B ON A.caption=B.caption
--RESULTS---
caption   caption
---------   ---------
Caption2  Caption2
NULL        Caption3

The LEFT and RIGHT joins will allow you to view all of the results that match the join, and those results that are NULL. Hope the examples help.

xblue

not all that sure how your data is structured...

I understand that each of the entries in table2 does have
a) "caption" being a string (or NULL?) and
b) a record with a corresponding picture id in table1, with "caption" either being a string or NULL*.

If so, I think your first query gets you in the right direction, but you'd need to use "OR", and use the picture id to avoid getting all possible combinations.

SELECT 
table1.caption, 
table2.caption 
FROM table1, table2 
WHERE table1.picture_id=table2.picture_id
AND (table1.caption = '$caption' OR table2.caption = '$caption')

I'd usually default to empty strings rather than NULL on text type columns. This leaves you NULL values to detect whether a corresponding record exists or not when you use left joins.

Hope I got you right 😉

jasemhi

xblue and pere_bigsby,

Thank you for your replies. I now see the difference between left and right joins- I still need to work with them more though. In this case, I only want the table1 or table2 caption to display for the one picture_id.

I tried playing with different combos and still am stuck. Here is what I have for my tables:

--Database--- 
table1         table2 
---------       --------- 
picture_id   picture_id 
caption       user_caption

What I am shooting for is to display table1 caption. If there is no caption in table1, then display table2 user_caption. If there is no table2 user_caption then display a link to add a user caption. Table1 caption and table2 user_caption are completely different. My different queries either have too many fields displayed or nothing. I'm thinking that I may have to do a query for caption in table1 only, and if it turns up null call another query for user_caption in table1. Is this feasable?

This is so frustrating because I know that there is an answer out there!

jasemhi

Problem fixed. I realize that the table1 id wan't in the table if there was a null in the original file that I created the table with. Using the following works:

SELECT 
table1.caption, 
table2.caption 
FROM table1, table2 
WHERE table1.picture_id='$picture_id' AND table2.picture_id = '$picture_id'

Thanks for all of your help.🙂