CODEWITHSUNDEEP
sqlselect
epch
epch

Reputation: 65

SQL select from two tables, using a distinct value between the two IF it exists, otherwise only using value from the first table?

I have two tables, with structures like this:

roomlist:

-room (unique)
-totalDesks

userlist:

-room (has duplicates)
-deskOwned

I need a SQL statement that will spit out the following:

room
totalDesks
desksUsed (COUNT(DISTINCT userlist.desk))
desksOpen (totalDesks-(COUNT(DISTINCT userlist.desk)))

I have this so far:

SELECT 
    DISTINCT roomList.room, userlist.room, roomList.totalDesks, 
    COUNT(DISTINCT userlist.desk) AS desksUsed, 
    roomlist.totalDesks - COUNT(DISTINCT userlist.desk) AS desksOpen     
FROM 
    roomlist, userlist 
WHERE 
    roomlist.room = userlist.room

The problem is that not all rooms currently have users in them. If I have rooms A, B, C and D, but only records of people in A, B and C, it won't include room D in the result - even though room D has 5 totalDesks, none of which are taken.

How can I get a result that will still give me results on a room from roomlist, even if no records exist for that room in userlist?

Upvotes: 2

Views: 117

Answers (2)

Bruce Burge
Bruce Burge

Reputation: 154

I would suggest mocking this up in http://sqlfiddle.com/ it will be much easier to help you then

Upvotes: 0

D Stanley
D Stanley

Reputation: 152626

Try a LEFT JOIN instead. I assume you need to group by room as well:

SELECT DISTINCT 
     roomList.room, 
     userlist.room, 
     roomList.totalDesks, 
     COUNT(DISTINCT userlist.desk) AS desksUsed, 
     roomlist.totalDesks-COUNT(DISTINCT userlist.desk) AS desksOpen 
     FROM roomlist 
         LEFT JOIN userlist ON roomlist.room=userlist.room
     GROUP BY roomList.room, 
         userlist.room, 
         roomList.totalDesks

Upvotes: 2

Related Questions