Join three tables

cbrasfield · #1 (**permalink**) 06-15-09, 10:57

Hello,

I have three tables: directory, child, pet
child and pet have foreign keys associated with directory (mid)

I need a sql statement that will query the tables and return the child and pet associated values in a single column. I can do this with two tables, but when I add in the third, I get duplicates. For instance, if I have one pet, two children, then I get a second returned value of pet.

Here's my sql for two that works just lovely:

Code:

select d.mid, d.lastName, d.firstName1, d.firstName2, d.address, d.phone, d.email, group_concat(c.name) as childName from child c right join directory d on d.mid = c.mid group by d.mid

My sql for three that is not quite right:

Code:

select d.mid, d.lastName, d.firstName1, d.firstName2, d.address, d.phone, d.email, group_concat(c.name) as childName, group_concat(c.age) as childAge, group_concat(p.name) as petName, group_concat(p.description) as description from child c
inner join pet p on p.mid = c.mid
right join directory d on d.mid = c.mid
group by d.mid

Any help would be appreciated, thank you!

r937 · #2 (**permalink**) 06-15-09, 12:10

Code:

SELECT d.mid
     , d.lastName
     , d.firstName1
     , d.firstName2
     , d.address
     , d.phone
     , d.email
     , c.children
     , p.pets
  FROM directory AS d
LEFT OUTER
  JOIN ( SELECT mid
              , GROUP_CONCAT(
                  CONCAT(name,',',age)
                  SEPARATOR ';') AS children
           FROM child 
         GROUP
             BY mid ) AS c
    ON c.mid = d.mid
LEFT OUTER
  JOIN ( SELECT mid
              , GROUP_CONCAT(
                  CONCAT(name,',',description)
                  SEPARATOR ';') AS pets
           FROM pet 
         GROUP
             BY mid ) AS p
    ON p.mid = d.mid

cbrasfield · #3 (**permalink**) 06-15-09, 13:07

You are absolutely wonderful!! Thank you so much. I'm going to buy your book.

Internet Services

Web Development

Digital Marketing

Consumer Tech