select values from two tables to one output 1

[MJB3K]

Hi, i have three table setups:

chatroom_rooms
RID | roomName | maxSize

chatroom_reg_users < for registered users
UID | username | room

chatroom_users < guests
uid | username | room

I am trying to get the following output

+ roomName (people / maxsize)
-- username

but the problem i am getting, is i want to combine the output from both tables to one output.

Can this be done??

Here is what i got so far:

SELECT chatroom_reg_users.uid, chatroom_reg_users.username, chatroom_users.username FROM chatroom_rooms, chatroom_reg_users, chatroom_users WHERE chatroom_reg_users.room = '$rid' AND chatroom_users.room = '$rid' AND chatroom_rooms.rid = '$rid'

Regards,

Martin

Computing Design And Services:

http://www.webrevolt.biz

 

[r937]

SELECT all_users.username 
  FROM chatroom_rooms
INNER
  JOIN ( SELECT username, room
           FROM chatroom_reg_users
         UNION
         SELECT username, room
           FROM chatroom_users 
       ) AS all_users
    ON all_users.room = chatroom_rooms.RID
 WHERE chatroom_rooms.RID = '$rid'

r937.com | rudy.ca

 

[MJB3K]

that works great

any chance you can explain the code for me?

another quick question: how would i select the uid from the tables?

Regards,

Martin

Computing Design And Services:

http://www.webrevolt.biz

 

[r937]

it's a UNION inside a subquery

it treats your two users tables as though they were one

which, perhaps, they should be

as for selecting the UID, add it to both of the SELECTs in the subquery, and then you can add it to the SELECT in the outer query (provided you prefix it with the subquery's name)



r937.com | rudy.ca