SELECT from multiple tables 1

[mimics]

Hi!

I have 2 tables. Users and Messages. Messages contains MessageID and Username for this MessageID. Users contains Username and Data. MessageID si unique and Messages.Username is not (there can be many same Username fields in Messages but with different MessageIDs).

I would like to have a SELECT sentence which would produce a Username, Data and number of Messages (this is number of MussageIDs) for a Username which exists in Users and a Username is a Username of a MessageID. I have made this:

SELECT @Username:=Users.Username FROM Users, Messages WHERE Messages.MessageID='given_id' AND Messages.Username=Users.Username;
SELECT Users.Username, Users.Data, COUNT(Messages.MessageID) FROM Users, Messages WHERE Messages.Username=@Username AND Messages.Username=Users.Username GROUP BY Users.Username;

I think that this is an "ugly" solution. Is there a better one?


Mike

 

[swampBoogie]

select u.username,udata,count(m.username)
 from users u left join messages m
   on u.username = m.username
 group by u.username,u.data

 

[mimics]

What about "WHERE Messages.MessageID='given_id'"?

This returns me data for all Usernames. I only need data for a Username which has given MessageID for it in Messages table.

I would like to specify a MessageID and than SQL would:
- find out Username in Messages for this MessageID (MessageIDs are unique)
- find out if this Username exists in Users table
- return data for this Username (Username, Data and number of MessageIDs for this Username in Messages table)

 

[swampBoogie]

Misread.

With the current (lacking) support for SQL in Mysql it is not possible to do better than you do.

 

[mimics]

What should otherwise be a proper SQL sentence? And which SQL server supports it? What is that what is missing in MySQL (for what should I wait that they implement)?

 

[swampBoogie]

Subselect, which is supported by almost any DBMS.

select u.username,udata,count(m.username)
 from users u join messages m
   on u.username = m.username
where u.username in
(select username from messages where messageid = 4711)
 group by u.username,u.data

 

[mimics]

I have found this:

Subqueries are supported in MySQL version 4.1. See section 1.6.1 Features Available From MySQL 4.1.

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

I think that it will be soon in a stable release (4.1 is alpha at the moment).

 

[swampBoogie]

Actually, I just realised that you don't need a subquery. It can be done with multiple joins instead.

select u.username,udata,count(m1.username)
 from messages m1 join users u
   on m1.username = u.username
   join messages m2 on
   u.username = m2.username
 where m2.messageid = 4711
 group by u.username,u.data

 

[mimics]

That did it! Thanks!

Is there maybe any optimization for it (maybe to specify LEFT or something)? Should I replace it with sub-select when it will be avaiable?

 

[swampBoogie]

A left join will never be more efficinet than an inner join and it is not relevant in this circumstances. A subquery will probably not be better optimized either but you can compare when they are available.

If you are having performance problem you should look over your indexes. You can use the explain statement to see the query plan.

 

[mimics]

Thanks again. You really helped me a lot.