[RESOLVED] MySQL query problem with 3 tables

[safra]

Hi,

I have 3 tables. I would like to do a search on table 1 (mail). The sender can come from table 2 (users) or table 3 (members). This is what I tried:

SELECT mail.mailfrom,mail.subject,mail.submitdate,mail.status,mail.id,users.screenname,members.screenname 
FROM mail,users,members 
WHERE mail.mailto='$user' AND mail.foldername='$folder' AND (mail.mailfrom=users.id OR mail.mailfrom=members.id)
 ORDER BY `mail.submitdate` DESC ";

Doesn't seemt to work whereas it does work fine when you only search on 2 tables, for example mail and user:

SELECT mail.mailfrom,mail.subject,mail.submitdate,mail.status,mail.id,users.screenname 
FROM mail,users 
WHERE mail.mailto='$user' AND mail.foldername='$folder' AND mail.mailfrom=users.id 
ORDER BY mail.submitdate DESC 

Anyone knows how to do this?

Thanks,
Raoul

[Piranha]

First thing is that your database is not built as it should be if it may come from 2 tables. As an example you could do like this:

table person
id
name
mail
other stuff that every person have

table member
person_id
other stuff that have to do with members only

table user
person_id
other stuff that have to do with users only

Then when you need something about members you join members and person. Same with users.

To solve your current problem without changing the table structure you can do one query for each and then use UNION to join them. The order by should then be for all of it. Something like this:
[sql]((SELECT mail.from, user.name
FROM mail, user
WHERE...)
UNION
(SELECT mail.from, member.name
FROM mail, member
WHERE...))
ORDER BY date[/code]

[safra]

Thanks Piranha! And sorry for the late reply, I was away for a couple of days.

Yes, sorry my table setup was not right, I found this out when doing some more tests. I solved it.