Help: Query(Solved)
Moderator: General Moderators
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
Help: Query(Solved)
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
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
Last edited by raghavan20 on Wed Aug 17, 2005 6:54 am, edited 3 times in total.
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)- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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
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.
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`)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.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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}'- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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:
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`- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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
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
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
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]
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]
COUNT is an aggregate.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.