CODEWITHSUNDEEP
mysql
udexter
udexter

Reputation: 2347

Doing a MySQL query with JOINs and get default values if not exists some of them

I don't know how exactly name this question, so please read the example and the expected result to clarify.

I need a query that return a list of projects with some data that needs to be joined. Some of the data data that I join, can be NULL. Let me show my actual query:

SELECT p.projectid, p.projectname, p.date, u.name, u.surnames
FROM projects p
JOIN rel_user_proj rup ON rup.projectid=p.projectid
JOIN users u ON u.userid=rup.userid
WHERE rup.codrole='PM'

The table rel_user_proj have relations between projects and users. I can have more than one relation but I only need one so I use the where rup.codrole='PM' that works for me.

The problem is that this table can have NO result for some project but if there is NO result I need to obtain u.name and u.surnames as NULL.

How I can do that?

Thank you in advance!

Upvotes: 0

Views: 768

Answers (2)

Robbie Tapping
Robbie Tapping

Reputation: 2556

Here we go... I think this is your Answer :)

SELECT DISTINCT 
                p.projectid
                , p.projectname
                , p.date
                , tab.name
                , tab.surnames
FROM 
                projects p
LEFT JOIN (
            SELECT DISTINCT 
                        rup.projectid
                        , u.name
                        , u.surnames
            FROM users u
            INNER JOIN 
                        rel_user_proj rup 
                                   ON u.userid = rup.userid
         ) tab 
            ON p.projectid = tab.projectid

OH woops... and here is the PM clause in it. Also Works.

SELECT DISTINCT 
                p.projectid
                , p.projectname
                , p.date
                , tab.name
                , tab.surnames
FROM 
                projects p
LEFT JOIN (
            SELECT DISTINCT 
                        rup.projectid
                        , u.name
                        , u.surnames
            FROM users u
            INNER JOIN 
                        rel_user_proj rup 
                                   ON u.userid = rup.userid
            AND rup.codrole='PM'
         ) tab 
            ON p.projectid = tab.projectid

Upvotes: 3

Robbie Tapping
Robbie Tapping

Reputation: 2556

LEFT JOIN would be your best option http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

SELECT p.projectid, p.projectname, p.date, u.name, u.surnames FROM projects p LEFT JOIN rel_user_proj rup ON rup.projectid=p.projectid JOIN users u ON u.userid=rup.userid WHERE rup.codrole='PM'

Hope this helps

Upvotes: 1

Related Questions