eatonphil
eatonphil

Reputation: 13682

SQL joining tables only on specific rows

I am looking for a general response as to the mindset of how to do this... I have a table that has a column full of possible parameters and a column full of possible values. I want to join all parameters of a certain kind to another table to give further description of those specific rows, but not have that table joined to all other rows that don't contain the specific value. It would look like this:

Parameters  Values   Mammal
   a          1         
   b          3
   d         cat      Yes
   c          4
   d         dog      Yes
   e          3
   d         fish      No
   f          2

I've tried a number of ways using Case, however the table just gets very weird and there was repetition of the table being joined depending on its length. Any suggestions?

The second table has two columns, it is being joined on its own animal column to the values column where parameter = "d". It does not show up at all when parameter equals anything else. Any suggestions would be greatly appreciated! (I'm using Cache SQL if you need to know. I'd much rather have a general explanation of technique though, it helps more.)

EDIT: Sorry, here would be the two separate tables:

      Table 1:                                   Table 2:

Parameters  Values                           Animal      Mammal
    a        1                                 cat         yes
    b        3                                 dog         yes
    d        cat                              snake        no
    c        4                                fish         no
    d        dog                               rat         yes
    e        3                               hamster       yes
    d        fish
    f        2

Upvotes: 3

Views: 11616

Answers (2)

tomfanning
tomfanning

Reputation: 9660

Two options.

First uses a subquery:

select [parameters], [values], 
    (select mammal from t2 where t2.animal = t1.[values]) as mammal
from t1

Second uses a left join.

select [parameters], [values], t2.mammal
from t1
left join t2 on t1.[values] = t2.animal

The other answer uses a left join but skips the null replacement provided by the other answer.

Note this was tested on MS SQL Server (T-SQL) only.

Upvotes: 2

Taryn
Taryn

Reputation: 247680

It sounds like your current query is using an INNER JOIN which will only include the records that match in both tables. You need to use a LEFT JOIN which will produce all records from table1 and the matching records in table2. If there is not a match from table1 then the missing side will equal null:

select t1.parameters,
  t1.value,
  case when t2.mammal is null then '' else t2.mammal end Mammal
from table1 t1
left join table2 t2
  on t1.value = t2.animal

See SQL Fiddle with Demo

If you need help learning JOIN syntax there is a great article:

A Visual Explanation of SQL Joins

Upvotes: 6

Related Questions