Help: Query(Solved)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Help: Query(Solved)

Post 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
Last edited by raghavan20 on Wed Aug 17, 2005 6:54 am, edited 3 times in total.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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)
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

It works now.

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

Thanks for your help mate
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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... :?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

let me try it,,,cos I tried the same earlier but I am having some other problems which controls the result
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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`
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what have you tried? I'm not going to just give an answer..
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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()).
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Post Reply