Page 1 of 2

Help: Query(Solved)

Posted: Sun Aug 14, 2005 3:35 pm
by raghavan20
I have got two tables:
ChatUsers_tbl : Id, UserName
ChatMessages_tbl: Id, FromId, RecipientId, Message

Is it possible in a single query to take out:
1. UserNames of both fromId, RecipientId
2. Message

Posted: Sun Aug 14, 2005 4:14 pm
by nielsene
Sure

Code: Select all

SELECT f.UserName, t.Username, Message FROM ChatMessages_tbl JOIN ChatUsers_tbl AS f ON (FromID=f.Id) JOIN ChatUsers_tbl AS t ON (RecipientId=t.Id)

Posted: Sun Aug 14, 2005 6:49 pm
by raghavan20
thanks for your query.
but it returns four rows instead of one

srini srini hi all
chat name srini hi all
srini chat name hi all
chat name chat name hi all


current table values:
ChatUsers_tbl:
41 srini 20050814182401
40 chat name 20050814182401

ChatMessages_tbl:
290 41 40 20050814182401 hi all

Posted: Sun Aug 14, 2005 8:11 pm
by nielsene
That seems extremely odd. What DBMS are you using? Did you use the query exactly as shown? Sounds like you ended up with a CROSS JOIN instead of a normal INNER/THETA JOIN.

Posted: Mon Aug 15, 2005 6:30 am
by raghavan20
It works now.

Honestly, I dont know why it did not happen the last time.

Thanks for your help mate

Posted: Tue Aug 16, 2005 7:56 am
by raghavan20
I was adding chat rooms to the chat application.
Now, I am wondering how to take out results for a chat room id from the existing join

Code: Select all

SELECT f.`UserName`, t.`UserName`, `Message`
 FROM `ChatMessages_tbl`
 JOIN `ChatUsers_tbl` AS f ON (`FromId`= f.`Id`)
 JOIN `ChatUsers_tbl` AS t ON (`RecipientId`= t.`Id`)
tables:
ChatRooms_tbl: Id, Name, Description, MaxUsersAllowed
ChatMessages_tbl: Id, ChatRoomId, FromId, RecipientId, Message, Time
ChatUsers_tbl: Id, ChatRoomId, UserName, LastUpdate

Help me to take messages only from a particular ChatRoomId.

Posted: Tue Aug 16, 2005 8:06 am
by feyd

Code: Select all

SELECT f.`UserName`, t.`UserName`, `Message`
 FROM `ChatMessages_tbl`
 INNER JOIN `ChatUsers_tbl` AS f ON (`FromId`= f.`Id`)
 INNER JOIN `ChatUsers_tbl` AS t ON (`RecipientId`= t.`Id`)
 WHERE `ChatMessages_tbl`.`ChatRoomId` = '{$room}'
this isn't rocket science... :?

Posted: Tue Aug 16, 2005 8:08 am
by raghavan20
let me try it,,,cos I tried the same earlier but I am having some other problems which controls the result

Posted: Tue Aug 16, 2005 8:14 am
by raghavan20
works fine feyd, it worked but with no results when I tried mine earlier. but sometimes the recipientid times out and i get no results if i still try to send messages to the same recipient.

so this is the query i use:

Code: Select all

SELECT f.`UserName`, t.`UserName`, `Message` FROM `ChatMessages_tbl`
JOIN `ChatUsers_tbl` AS f ON (`FromId`=f.`Id`) 
JOIN `ChatUsers_tbl` AS t ON (`RecipientId`=t.`Id`) 
WHERE `ChatMessages_tbl`.`ChatRoomId` = '{$_SESSION["ChatRoomId"]}' 
ORDER BY `ChatMessages_tbl`.`Time`

Posted: Tue Aug 16, 2005 10:57 am
by raghavan20
tables:
ChatRooms_tbl: Id, Name, Description, MaxUsersAllowed
ChatMessages_tbl: Id, ChatRoomId, FromId, RecipientId, Message, Time
ChatUsers_tbl: Id, ChatRoomId, UserName, LastUpdate

I want to find the total number of users logged into a chat room and the maximum users allowed for the room

sth like:
count(*) from ChatUsers_tbl where ChatRoomId = 'room_id'
MaxUsersAllowed from ChatRooms_tbl where ChatRoomId = 'room_id'
is it possible to return true or false by comparing (MaxUsersAllowed <= count(*))?

I want all of them done in a single query.
thanks for your help guys

Posted: Tue Aug 16, 2005 11:26 am
by feyd
what have you tried? I'm not going to just give an answer..

Posted: Tue Aug 16, 2005 11:42 am
by nielsene
You'll want to look into the HAVING clause to use with the GROUP BY to do the filtering of an aggregate (COUNT()).

Posted: Tue Aug 16, 2005 2:10 pm
by raghavan20
does not look like using a having or group by clause.

I do not need 'group by' because i do not need results based on grouping of any column rather i am looking for the count(*) of only one column with a where condition

I do not need 'having' because I do not have any aggregate function to be compared in the where class.

I do not need a 'join' I think because I do not use a value of one table with another instead I have a value that has to be compared with two tables.

my query would be like:
a. select count(*) from ChatUsers_tbl where ChatRoomId = '{$_SESSION["ChatUserId"]}'
b. select MaxUsersAllowed from ChatRooms_tbl where ChatRoomId = '{$_SESSION["ChatUserId"]}'
inference:
same value "$_SESSION["ChatUserId"]" has to be compared with two tables to extract two results

now if we get count(*), MaxUsersAllowed near the select keyword
thats like
select count(*), MaxUsersAllowed then we could easily
do
select (MaxUsersAllowed < count(*)) as Allowed

should find a way to combine (a) and (b)[/b]

Posted: Tue Aug 16, 2005 2:23 pm
by feyd
you do need a join. You're wanting to know how many users (1) and what the maximum size of a room is (2). Both pieces of information are stored in two seperate tables.

Posted: Tue Aug 16, 2005 2:27 pm
by nielsene
raghavan20 wrote:does not look like using a having or group by clause.

I do not need 'group by' because i do not need results based on grouping of any column rather i am looking for the count(*) of only one column with a where condition

I do not need 'having' because I do not have any aggregate function to be compared in the where class.
COUNT is an aggregate.