multiple left join problem

[dnagirl]

Hi,

I have 3 tables: sequence, seq_cats and categories.

sequence has 2 fields of interest:
seq_id (int(10) not null auto_increment primary key)
seq_name (varchar(40) not null)

categories has 3 fields of interest:
cat_id (int(10) not null auto_increment primary key)
cat_name (varchar(20) not null)
cat_type_id (int(10) not null foreign key)

The seq_cats table joins the sequence and categories tables:
seq_id (int(10) not null foreign key)
cat_id (int(10) not null foreign key)

So the relationships are:
sequence to seq_cats: 1 to many on seq_id
seq_cats to categories: many to many on cat_id

I need to create a result set with a column for seq_name and a separate column for each cat_type_id.

The results should be like the following:
seq_name, fish, tissue
Bob, halibut, gill
Carol, perch, liver
Sandra, NULL, NULL
Bill, NULL, brain

As you can see, because NULL values are allowed, a left join is appropriate.

I can successfully get a result for one cat_type_id at a time, but not for more than one.

e.g.

mysql> select s2.seq_name, t.cat_name as tissue from sequence as s2 left join seq_cats as tscats using (seq_id) join categories as t on (tscats.cat_id=t.cat_id and t.cat_type_id=3) limit 10;
+------------------------+--------+
| seq_name               | tissue |
+------------------------+--------+
| lk_hhgi_0001a01.pDNRF2 | Gill   |
| lk_hhgi_0001a02.pDNRF2 | NULL   |
| lk_hhgi_0001a03.pDNRF2 | Gill   |
| lk_hhgi_0001a04.pDNRF2 | Gill   |
| lk_hhgi_0001a05.pDNRF2 | Gill   |
| lk_hhgi_0001a06.pDNRF2 | Liver  |
| lk_hhgi_0001a07.pDNRF2 | Brain  |
| lk_hhgi_0001a08.pDNRF2 | Gill   |
| lk_hhgi_0001a09.pDNRF2 | Ovary  |
| lk_hhgi_0001a10.pDNRF2 | Gill   |
+------------------------+--------+


mysql> select s.seq_name, f.cat_name as fish from sequence as s left join seq_cats as fscats using (seq_id) join categories as f on (fscats.cat_id=f.cat_id and f.cat_type_id=1) limit 10;
+------------------------+---------+
| seq_name               | fish    |
+------------------------+---------+
| lk_hhgi_0001a01.pDNRF2 | Halibut |
| lk_hhgi_0001a02.pDNRF2 | Halibut |
| lk_hhgi_0001a03.pDNRF2 | NULL    |
| lk_hhgi_0001a04.pDNRF2 | Halibut |
| lk_hhgi_0001a05.pDNRF2 | Halibut |
| lk_hhgi_0001a06.pDNRF2 | Halibut |
| lk_hhgi_0001a07.pDNRF2 | Sole    |
| lk_hhgi_0001a08.pDNRF2 | Halibut |
| lk_hhgi_0001a09.pDNRF2 | Halibut |
| lk_hhgi_0001a10.pDNRF2 | Halibut |
+------------------------+---------+

When I try to combine the above queries, I lose all my NULL records. I need a result with one record for each seq_id whether the other columns are filled in or not.

Thanks in advance.
Jennifer

 

[dnagirl]

This code was kindly provided me. It solves my problem:

select s.seq_name, 
       (select cat_name
          from categories join seq_cats using (cat_id)
         where cat_type_id = 3
           and seq_cats.seq_id = s.seq_id) as tissue,
       (select cat_name
          from categories join seq_cats using (cat_id)
         where cat_type_id = 1
           and seq_cats.seq_id = s.seq_id) as fish
  from sequence as s 
  order by s.seq_name
  limit 10

mysql v4.1.12 for sun_solaris2.8(sparc)