Reputation: 13682
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
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
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
If you need help learning JOIN
syntax there is a great article:
A Visual Explanation of SQL Joins
Upvotes: 6