CODEWITHSUNDEEP
sqlteradata
dj waffles
dj waffles

Reputation: 3

inner and left outer join combined - with two columns

Just learning - thanks for the help.

Table1: Breakfast

Animal Food
Cow Grass
Monkey Banana
Monkey Apples
Human Cereal
Croc Meat

Table 2: Lunch

Animal Food
Cow Grass
Monkey Apples
Human Sandwich
Croc Meat

How would you write a query to return All the animals but return NULL where the lunch is different to breakfast?

Expected output:

Animal Breakfast Lunch
Cow Grass Grass
Monkey Banana NULL
Monkey Apples Apples
Human Cereal NULL
Croc Meat Meat

Upvotes: 0

Views: 66

Answers (4)

Alonso G.
Alonso G.

Reputation: 11

it is simply a left join statement, remember that left join returns all indices in left table, the ones that are not paired are filled with 'null' value, the querty looks like

 select b.Animal, 
    b.Food as Breakfast, 
    l.Food as Lunch
from Breakfast as b
    left join Lunch as l
using(Food);

we are using the column Food from both table as index. Take the index 'meat' then you have that croc eat meat as breakfast and for lunch so your output row is

| croc | meat | meat |

Now take the index 'cereal', we have that humans eat cereal for breakfast but humans don't have it for lunch then (as you choose left join) the value 'Null' is placed in the lunch column so we have

| human | cereal | null |

as output row.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

It's a plain Outer Join:

select
   b.animal,
  ,b.food as breakfast
  ,l.food as lunch
from breakfast b 
left join lunch l
  on b.animal = l.animal
 and a.food = b.food

Upvotes: 1

dglz
dglz

Reputation: 69

Depending on the kind of sql that you are using:

If SQLite:

SELECT 
breakfast.animal AS animal, 
breakfast.food AS breakfast, 
CASE WHEN lunch.food=breakfast.food THEN lunch.food ELSE NULL END AS lunch 
FROM breakfast JOIN lunch ON breakfast.animal = lunch.animal;

else if MySQL:

SELECT 
breakfast.animal AS animal, 
breakfast.food AS breakfast, 
IF lunch.food=breakfast.food THEN lunch.food ELSE NULL END IF AS lunch 
FROM breakfast JOIN lunch ON breakfast.animal = lunch.animal;

There are other kinds of SQL, such as SQL Server, etc, so I'm just guessing that it might be either of those two.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You would use full join:

select coalesce(b.animal, l.animal) as animal,
       b.food as breakfast, l.food as lunch
from breakfast b full join
     lunch l
     on b.animal = l.animal

Upvotes: 0

Related Questions