two JOINS

Anon

hello,

i am using LEFT JOIN between two tables [domains, domains_pictures]:

domains LEFT JOIN domains_pics ON domains.id=domains_pics.id_domain

however i need to use 2 joins at the same time, both from the DOMAINS table, something like this:

domains LEFT JOIN domains_pics ON domains.id=domains_pics.id_domain
+
domains LEFT JOIN domains_guestbook ON domains.id=domains_guestbook.id_domain

but it isnt working... working weird. is this supported by mysql?

[deleted]

How did you put it in the query?

Anon

I, too, am very interested. I posted a similar question yesterday about LEFT JOINs. Sounds like we have the same problem.

Trying to LEFT JOIN table A to table B
AND
LEFT JOIN table A to table C.

I have tried several different queries, but none gave me what I was looking for. My first post is at:
http://www.phpbuilder.com/forum/read.php3?num=5&id=24081&loc=0&thread=24081

Anon

there are 3 tables

DOMAINS
--- domains_pics
--- domains_guestbook

1st query [works well]

let me explain first - its pretty simple. i want to list domains and count number of pictures for each domain [watch COUNT... and GROUP BY - i am grouping by id_domain in pictures table].

SELECT <something>, COUNT(domains_pics.id) FROM domains LEFT JOIN domains_pics ON domains.id=domains_pics.id_domain WHERE <something> GROUP BY domains.id, domains_pics.id_domain

2nd query [cant figure it out]

now i want to use same thing as above but i want to add counter for guestbook entries - logic is same as with pictures. every domain can have X pictures and Y guestbook entries. so i thought i will just add another LEFT JOIN somehow with parameter

domains LEFT JOIN domains_guestbook ON domains.id=domains_guestbook.id_domain

and have all in one result - domainname, number of pics and number of guestbook entries. i also added GROUP BY for id_domain in guestbook table

domains.id, domains_#guestbook#.id_domain

but i think the problem is in that GROUP BY! i am lost now :-X

[deleted]

A JOIN connectes tables by common data.
Thus the resulting records all have one (or more) values in common.

Consquently, if you join the domain table to the pictures table, you will only get all records where domain and picture have the same value for the id.

If then add the guestbook table, you will only get the records where domain, picutures AND guestbook all have the same id.

There are ways of getting what you want, but it is much simpler to just run two queries and match them up through creating associative arrays.

Anon

i know... but i always want to do it with less queries. with 2 queries its easy, everyone knows that ;-)

thanks anyway!

[deleted]

... sometimes it's just not practical to do it all in SQL :-)

smarlowe

But this guy is doing left joins which should present every row in domains to the second left join.

The problem may be that he is trying to left join AFTER the group by.

What he needs is:

select * from table a left join table b on (a.id=b.aid) left join table c on (a.id=c.aid) group by...

Anon

Scott, that exactly what i did [look at my example]. oh yeah, thats working, but i need to count entries in other tables [b & c] rather to get them... so i am using COUNT(b.id_domain), COUNT (c.id_domain)...

Anon

hehe vincent, yeah, i'll do it your way this time :-)