Mysql: Join only when column is empty

Question

I am trying to write a query in MySQL to fetch a list of images. The problem is that, I want to join another table only when a column is not null.

images:

|id|image                                                               |user_id|circle_id|is_deleted|
------------------------------------------------------------------------------------------------------
|1 |cdb10bee27262c597619fdef25bae0eb673f5d5d1b9b33697e40906ad851a25b.jpg|30     |1        |0         |
|2 |9e3213276225b900a3e19262a7761ec3fdf608b95441aa8fbc012f246514d394.jpg|30     |1        |0         |
|3 |a51e104c82de33a804ae0a09e719f3cd7aa205a0830eab4e1e4aa26fd8a6313d.jpg|30     |NULL     |0         |

circles_users:

|id|circle_id|user_id|
----------------------
|1 |1        |31     |

users:

|id|username|
-------------
|30|elk     |
|31|moose   |

Initially, I came up with this, but after inspection, I realised it would not work.

SELECT images.*
FROM images
    LEFT JOIN circles_users ON circles_users.circle_id = images.circle_id
WHERE (circles_users.user_id = '31' OR images.circle_id IS NULL)
    AND images.user_id = '30'
    AND images.is_deleted = '0'
LIMIT 16

I can't seem to get it right anyhow, so I decided to write some pseudo sql to illustrate what I wish to achieve

SELECT images.*
    FROM images

        CASE
            WHEN images.circle_id IS NOT NULL 
            THEN LEFT JOIN circles_users ON circles_users.circle_id = images.circle_id AND circles_users.user_id = '32'
        END CASE

    WHERE images.user_id = '30'
    AND images.is_deleted = '0'
    LIMIT 16

Hope you get what I mean. Thanks for the help.

Basically, I want to fetch all images posted by a user (poster) has if another user is in the circle shared by the poster or if the circle_id is null

If you do not want NULLs in your result try changining the LEFT JOIN to INNER JOIN. — user2989408
– user2989408, Commented Nov 26, 2013 at 16:48
Actually, I am checking for user 31s circles. I'm so dumb. My original query works as expected. I've been working so long I began testing on wrong data. Thanks a bunch for the effort and the time. I appreciate it much. — frostymarvelous
– frostymarvelous, Commented Nov 26, 2013 at 18:11

Randy · Accepted Answer · 2013-11-26 17:19:45Z

1

You should use an INNER JOIN if I understand your question.

answered Nov 26, 2013 at 17:19

Randy

16.7k2 gold badges40 silver badges56 bronze badges

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

5 Comments

Shai Over a year ago

This. Just change LEFT to INNER and you won't get any NULL joins

frostymarvelous Over a year ago

I'm guessing my question is not coming through well enough. Basically, if circle_id is null, I still want to return the image. If it isn't, then I'd like to join on the circles_users

frostymarvelous Over a year ago

I've just come across a left outer join, and I suspect that will do the trick for me.

frostymarvelous Over a year ago

But it doesn't work. It only returns rows which have a circle id.

frostymarvelous Over a year ago

Actually, my first query does what I Want it to do. I was testing it on wrong data. Thanks for the effort!

user2989408 · Accepted Answer · 2013-11-26 17:48:18Z

1

Then Change your query to something like

SELECT im.*
FROM images im
    LEFT JOIN circles_users cu ON cu.circle_id = im.circle_id AND cu.user_id = '31'
WHERE im.user_id = '30'
    AND im.is_deleted = '0'
LIMIT 16

answered Nov 26, 2013 at 17:48

user2989408

3,1371 gold badge20 silver badges15 bronze badges

1 Comment

frostymarvelous Over a year ago

Actually, my first query does what I Want it to do. I was testing it on wrong data. Thanks for the effort!

frostymarvelous · Accepted Answer · 2013-11-26 18:22:51Z

0

My test data was flawed and my trust in my sql knowledge ahaky. The original query is correct and works as expected.

SELECT images.* FROM images LEFT JOIN circles_users ON circles_users.circle_id = images.circle_id WHERE (circles_users.user_id = '31' OR images.circle_id IS NULL) AND images.user_id = '30' AND images.is_deleted = '0' LIMIT 16

EXPLANATION When the query is run, it fetches all images with user_id = 30. If circle_id is not null, it fetches the corresponding circles_users row else nothing as it is a left join The Where statement then checks if the circles_users.user_id (if it exists) = 31 else it checks if images.circle_id is null effectively achieving the What I aimed to.

answered Nov 26, 2013 at 18:22

frostymarvelous

2,82534 silver badges43 bronze badges

Collectives™ on Stack Overflow

Mysql: Join only when column is empty

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related