Public feed
#380508
I've got two tables:
Table1:
+---------+----------+
| Name | Color |
+---------+----------+
| Fruit | Many |
| Apple | Green |
| Orange | Orange |
| Drink | Varies |
| Beer | Brown |
| Cola | Black |
| Bread | Assorted |
+---------+----------+
Table2:
+---------+-------+
| Name | Type |
+---------+-------+
| Apple | Fruit |
| Orange | Fruit |
| Beer | Drink |
| Cola | Drink |
+---------+-------+
Now I am trying to get all of the same type as a given name. For example if I do a select on Apple, Orange or Fruit I want returned
Return list 1:
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Apple | Fruit | Green |
| Orange | Fruit | Orange |
| Fruit | Fruit | Many |
+---------+-------+--------+
And if I do a select on Dring, Beer or Cola I want returned
Return list 2:
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Drink | Drink | Varies |
| Beer | Drink | Brown |
| Cola | Drink | Black |
+---------+-------+--------+
Note that Table2.Type will always have exactly 1 corresponding entry in Table1.Name
But there might exist entries Table1.Name that does _not_ exist in Table2.Name nor in Table2.Type. These should only return 1 row like
Return list 3:
+---------+-------+----------+
| Name | Type | Color |
+---------+-------+----------+
| Bread | NULL | Assorted |
+---------+-------+----------+
I have tried subselects in where clauses and joins left, right and inner but I can't get it right.
The closest I got was:
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Drink | Drink | Many |
| Drink | Drink | Brown |
| Drink | Drink | Black |
+---------+-------+--------+
Which is close, but no sigar.