I've begun to dive into joins/apply/unions, etc and there's one concept I don't think I understand in this, and I was hoping I could be helped out. Let's say I have two tables, tabone and tabtwo.
In tabone:
| product | color |
|---|---|
| Eggs | Tan |
| Milk | White |
In tabtwo:
| product | price |
|---|---|
| Eggs | 1.50 |
| Milk | 2.00 |
I'd like to learn how to write query which will in a single result show me the product, color, and price for any given product.
Example output I'd like:
| product | price | color |
|---|---|---|
| Eggs | 1.50 | Tan |
From what I've gathered so far, I don't see quite how a join or union can achieve this in one result. All of my testing gives me multiple columns with the color for example not merging. I'd appreciate some help on this!
CodePudding user response：
You can join the two tables in at least 3 ways.
You can inner join, use a correlated subquery or apply a correlation. All work as expected provided the product is unique:
select t1.product, t2.price, t1.color
from tabone t1
join tabtwo t2 on t2.product = t1.product;
select product,
(select price from tabtwo t2 where t2.product = t1.product) price,
color
from tabone t1;
select t1.product, t2.price, t1.color
from tabone t1
cross apply (
select price
from tabtwo t2
where t2.product = t1.product
) t2;
CodePudding user response：
SELECT t1.product, price, color
FROM tabone t1
JOIN tabtwo t2
ON t1.product = t2.product
Disclaimer: I'm fairly new to SQL too lol