CODEWITHSUNDEEP
mysqlselectleft-joinmultiple-tables
frequent
frequent

Reputation: 28503

how to query two tables and return NULL if a record is not in the 2nd table in MySQL?

I'm running MySQL 5.0.88 and need to select data from two tables, called articles and media.

When uploading images to my server I need to query all articles of a user and see whether there is already a path specified in the media table (field type="img") to avoid running unnecessary overwrites.

I had hoped to just include the m.type in my query, but I can't get it to work.

My query looks like this:

SELECT DISTINCT a.style, a.filename, a.path, m.type
    FROM articles AS a
    LEFT JOIN mnedia AS m
       ON a.iln = m.iln
    WHERE
       a.iln = id_12345
       AND m.type = "img"
       AND m.type IS NOT NULL

This gives me ALL articles (correct) of user id_12345, but the type field is alway img although the media table only has one record for article abc.

Question:
How do I need to query, so it returns all articles of a user (working) displaying type=NULL for articles, which are NOT listed in table media and type="img" for articles, which are listed in the table media(such as abc)?

Thanks for help!

EDIT: table articles may include records:

abc 
d
e
f
g

table media may include

abc type=img

the query should return:

abc type=img
d type=NULL
e type=NULL
f type=NULL
g type=NULL

EDIT:
I also woudn't mind getting all records in articles that are not in media, such as on this image, 2nd join left hand side ("b.Key IS NULL)

SOLUTION: Ok. I needed to check for another field besiders user_id when joining both tables. This is how it works:

SELECT DISTINCT a.style, a.filename, a.path, m.type
    FROM articles AS a
    LEFT JOIN mnedia AS m
       ON a.iln = m.iln
       AND a.style= m.style
    WHERE
       a.iln = id_12345
       AND ( m.type = "img" OR m.type IS NULL )

THANKS ALL for your suggestions!

Upvotes: 3

Views: 2484

Answers (2)

Omesh
Omesh

Reputation: 29101

you should put m.type = "img" condition on LEFT JOIN instead of putting it in WHERE clause:

SELECT DISTINCT a.style, a.filename, a.path, m.type
FROM articles AS a
     LEFT JOIN mnedia AS m
         ON a.iln = m.iln
            AND m.type = "img"
WHERE a.iln = id_12345;

Upvotes: 2

gabtub
gabtub

Reputation: 1470

i think just adapting your where clause a bit, should be enough:

WHERE
       a.iln = id_12345
       AND (
              m.type = "img"
              OR m.type IS NULL
           )

Since "img" and NULL are the two values you're looking for

Upvotes: 1

Related Questions