MYSQL INNER JOIN with possible empty fields

Question

Ok, I have a problem with joining 2 tables (with INNER JOIN). First table containts categories list (ecc. for photo albums, or blog posts) while the second table contains "the data"

I tried like this:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
INNER JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

This gives me fair result, joining my tables nicely, just as I want them to with one crucial exeption.

If table "photos" doesn't have a row which contains "photos_gal_id" (for example "2"), than it will NOT return any category (galeries_id, galeries_title) for that category even if it does egzist in galeries table.

It is logical because of:

 ON galeries.galeries_id=photos.photos_gal_id

Now I need to adopt this part to show me even thoes which doesn't have a related row in the second table

The result I'm trying to get is:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2                                     
     3               etata               5            test.jpg

I hope I explained it good enough :) Thank you..

Mark · Accepted Answer · 2013-07-30 15:07:11Z

39

To retain the rows from galeries with no matching ID in photos, you will need to join photos on galeries with LEFT JOIN instead of INNER JOIN:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
LEFT JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

This will give you:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2            NULL         NULL                
     3               etata               5            test.jpg

And if you wish to replace NULL with an empty string, you can use:

SELECT
  IFNULL(photos.photos_id, ''),
  IFNULL(photos.photos_link, '')

edited Jul 30, 2013 at 15:07

answered Jul 30, 2013 at 15:00

Mark

1,1031 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Albert Over a year ago

what if i just want to get the row with photos_id = null

ragol Over a year ago

@albertDaracan, add WHERE photos_id IS NULL

Jacob Goulden · Accepted Answer · 2013-07-30 15:02:31Z

5

Wouldn't it just be a

LEFT JOIN photos

Because you want the records reguardless of wether or not they are filled?

answered Jul 30, 2013 at 15:02

Jacob Goulden

3712 silver badges15 bronze badges

Collectives™ on Stack Overflow

MYSQL INNER JOIN with possible empty fields

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related