CODEWITHSUNDEEP
mysqljoinnullouter-join
Sharpk1ll3r
Sharpk1ll3r

Reputation: 13

Returning Multiple Null Values Using Outer Join

I'm trying to get an SQL query to return NULL values using a LEFT OUTER JOIN between my two tables.

One table contains user information, and the other contains roll call information. Essentially, I'm trying to generate an HTML table to display whether or not someone signed a roll call for each month. If the value is NULL, it means they were not a member at the time of that roll call.

The problem I'm having right now is that the query will only return one NULL value, even if they weren't a member for more than one roll call. Here's the query I'm using where the user was not a member for more than one roll call:

SELECT m.userid, r.status, r.rollcallid
FROM fleetmgr_members AS m
     LEFT OUTER JOIN fleetmgr_rollcall_log AS r 
     ON m.userid = r.userid AND r.rollcallid IN(1, 5)
WHERE m.userid = 2089
ORDER BY m.currentName, r.rollcallid;

This query returns:

userid  status  rollcallid
2089    NULL    NULL

I want it to return:

userid  status  rollcallid
2089    NULL    1
2089    NULL    5

For some odd reason, achieving what I want it to return has totally stumped me - so thanks in advance for any assistance you can provide.

fleetmgr_rollcall_log Table:

 id     rollcallid  userid    date_signed   status  activity        comments    
  2     1           652       1330563886    signed  6-8 hrs/week    -
  29    1           2462      1330565521    signed  9-11 hrs/week   -
  1823  5           731       1333300321    signed  0-2 hrs/week    -
  2293  5           166       1333901983    exempt  0-2 hrs/week    -

fleetmgr_members Table:

 id     userid      currentName
 1          3       SomeUsername
 2          5       DifferentUsername

Upvotes: 1

Views: 1010

Answers (4)

Michael Buen
Michael Buen

Reputation: 39393

Why this doesn't work?

SELECT m.userid, r.status, r.rollcallid
FROM fleetmgr_members AS m
     LEFT OUTER JOIN fleetmgr_rollcall_log AS r 
     ON m.userid = r.userid 
WHERE m.userid = 2089 AND r.rollcallid IN(1, 5)
ORDER BY m.currentName, r.rollcallid;

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6106

How about something like this

SELECT r1.rollcallid, r2.status
FROM (
    SELECT DISTINCT rollcallid
    FROM fleetmgr_rollcall_log
    ) AS r1 
 LEFT JOIN fleetmgr_rollcall_log AS r2 
 ON r1.rollcallid=r2.rollcallid AND r2.userid=2089
ORDER BY r1.rollcallid;

I've not returned any user data as the only user data you've got in your sample results is the user_id which you know already as it's in your query.

Upvotes: 1

Nathan
Nathan

Reputation: 2775

Try this

SELECT m.userid, r.status, r.rollcallid
FROM fleetmgr_members  m, fleetmgr_rollcall_log  r 
WHERE m.userid = 2089  AND r.rollcallid IN(1, 5)
ORDER BY m.currentName, r.rollcallid;

So what about this (using CROSS JOIN):

SELECT m.userid, r.status, r.rollcallid
FROM fleetmgr_members  m cross join fleetmgr_rollcall_log  r 
WHERE m.userid = 2089  AND r.rollcallid IN(1, 5)
ORDER BY m.currentName, r.rollcallid;

Upvotes: 0

scorpdaddy
scorpdaddy

Reputation: 303

Move the r.rollcallid IN(1, 5)to the where clause, not the join clause.

Upvotes: 0

Related Questions