John,
Union the 2 together.
select r.room_num, count(p.peopid)
from rooms r
left join people p on r.roomid=p.roomid1
group by r.room_num
UNION
select r.room_num, count(p.peopid)
from rooms r
right join people p on r.roomid=p.roomid1
group by r.room_num
or something like that. Should give you 2 rows with the data you are
looking for.
HTH,
=C=
|
| Cal Evans
| http://blog.calevans.com
|
|
John Heim wrote:
I need to write a query that essentially does both a left and right
join. I have a list of people and the rooms they occupy. Some rooms have
no people. Some people have no room assigned.
This gives me a list of rooms and how many people are in each room
including any rooms with nobody in them:
select r.room_num, count(p.peopid)
from rooms r
left join people p on r.roomid=p.roomid1
group by r.room_num
All I need is one more row in the table with a null for the room num and
a count of the number of people with no room assigned. If I change
'left' to 'right' in the query above, I get that row but then I don't
get rooms with no people.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
