MySQL left join and union - mysql

Having a database structure like this:
car
id, name, color
1, mercedes, red
2, volvo, blue
3, bmw, green
car_alt
id, car_id, color
1, 1, green
2, 1, blue
3, 2, red
4, 2, blue
I want to get such result:
id, name, color
1, mercedes, red
1, mercedes, green
1, mercedes, blue
2, volvo, blue
2, volvo, red
2, volvo, blue
3, bmw, green
Currently i use this query:
SELECT id, name, color
FROM car
UNION
SELECT car.id, car.name, car_alt.color
FROM car
INNER JOIN car_alt ON car.id = car_alt.id_car
Is there any way to do it without using UNION? I wonder if it's possible to just use LEFT JOIN adding some empty row like:
SELECT car.id, car.name, COALESCE(car_alt.color, car.color)
FROM car
LEFT JOIN car_alt ON car.id = car_alt.id_car OR 'GET EMPTY ROW'

I don't believe there is a reasonable way of doing this without union/union all.
Here is an unreasonable approach, using cross join and filtering:
select c.id, c.name, co.color
from (select distinct id, name from car) c cross join
(select distinct color from car) co
where exists (select 1 from car c2 where c2.id = c.id and c2.color = co.color) or
exists (select 1 from car_alt ca where ca.id = c.id and ca.color = co.color);
This has basically restructured the logic so the or functionally does what the union all does in your version.

If you don't want to use UNION, how about a temporary table?
SELECT *
into #Temp
FROM car
INSERT #Temp
SELECT a.car_id, c.name, a.color
FROM car_alt a INNER JOIN
car c ON c.id = a.car_id
SELECT *
FROM #Temp
ORDER BY id, name
Please, don't forget to DROP TABLE #Temp when done - it's good to clean up.

Related

MySQL query get product have multiple color and order by number of color product have

I have one table product(id, name) have n-n relationship with table color(id, name), and 1 table product_color(product_id, color_id). How can i query all product can have multiple color (example i want query product can have color red, green, blue) and order them by number of color coincident( product have 3 color red, green, blue will be first, then product with 2 color and 1 color last)
you could use a select form the joined table and count the number of distinct color
select prod_name, count(distinct color_name)
from (
select
a.id prod_id
, a.name prod_name
, b.id color_id
, b.name color_name
from product a
inner join product_color c on a.id = c.product_id
inner join color b on b.id = c.color_id
where b.name in ('red', 'green', 'blue')
) t
group by prod_name
order by count(distinct color_name) desc

Filter on second left join - SQL

I have three tables. One consists of customers, one consists of products they have purchased and the last one of the returns they have done:
Table customer
CustID, Name
1, Tom
2, Lisa
3, Fred
Table product
CustID, Item
1, Toaster
1, Breadbox
2, Toaster
3, Toaster
Table Returns
CustID, Date, Reason
1, 2014, Guarantee
2, 2013, Guarantee
2, 2014, Guarantee
3, 2015, Guarantee
I would like to get all the customers that bought a Toaster, unless they also bought a breadbox, but not if they have returned a product more than once.
So I have tried the following:
SELECT * FROM Customer
LEFT JOIN Product ON Customer.CustID=Product.CustID
LEFT JOIN Returns ON Customer.CustID=Returns.CustID
WHERE Item = 'Toaster'
AND Customer.CustID NOT IN (
Select CustID FROM Product Where Item = 'Breadbox'
)
That gives me the ones that have bought a Toaster but not a breadbox. Hence, Lisa and Fred.
But I suspect Lisa to break the products on purpose, so I do not want to include the ones that have returned a product more than once. Hence, what do I add to the statement to only get Freds information?
How about
SELECT * FROM Customer
LEFT JOIN Product ON Customer.CustID=Product.CustID
WHERE Item = 'Toaster'
AND Customer.CustID NOT IN (
Select CustID FROM Product Where Item = 'Breadbox'
)
AND (SELECT COUNT(*) FROM Returns WHERE Customer.CustId = Returns.CustID) <= 1
The filter condition goes in the ON clause for all but the first table (in a series of LEFT JOIN:
SELECT *
FROM Customer c LEFT JOIN
Product p
ON c.CustID = p.CustID AND p.Item = 'Toaster' LEFT JOIN
Returns r
ON c.CustID = r.CustID
WHERE c.CustID NOT IN (Select p.CustID FROM Product p Where p.Item = 'Breadbox');
Conditions on the first table remain in the WHERE clause.
As a note: A table called Product that contains a CustId seems awkward. The table behaves more likes its name should CustomerProducts.
You use conditional COUNT
SELECT C.CustID, C.Name
FROM Customer C
JOIN ( SELECT CustID
FROM Products
GROUP BY CustID
HAVING COUNT(CASE WHEN Item = 'Toaster' THEN 1 END) > 1
AND COUNT(CASE WHEN item = 'Breadbox' THEN 1 END) = 0
) P -- Get customer with at least one Toaster and not Breadbox
ON C.CustID = P.CustID
JOIN ( SELECT CustID
FROM Returns
HAVING COUNT(*) < 2
) R -- Get only customers with less than 2 returns
ON C.CustID = R.CustID

Multiple conditions on the joined table when using group by

I have two tables:
products
id name
1 Product 1
2 Product 2
3 Product 3
products_sizes
id size_id product_id
1 1 1
2 2 1
3 1 2
4 3 2
5 3 3
So product 1 has two sizes: 1, 2. Product 2 has two sizes: 1, 3. Product 3 has one size: 3.
What I want to do is build a query that pulls back the products that have both size 1 and size 3 (i.e. Product 2). I can easily create a query that pulls back the products that have both sizes 1 AND 3:
select `products`.id, `products_sizes`.`size_id`
from `products` inner join `products_sizes` on `products`.`id` = `products_sizes`.`product_id`
where products_sizes.size_id IN (1, 3)
group by products.id
When I run this query, I get back Product 1, Product 2, and Product 3.
Just to reiterate, I'd like to only get back Product 2. I've tried using the HAVING clause, messing around with $id IN GROUP_CONCAT(...) but I haven't been able to get anything to work. Thanks in advance, guys.
Since you want both 1 and 3, you need to COUNT the size_ids that are IN (1, 3) and require the result to be 2:
SELECT p.id AS id, p.name AS name
FROM products p, products_sizes s
WHERE p.id = s.product_id AND s.size_id IN (1, 3)
GROUP BY s.product_id
HAVING COUNT(DISTINCT s.size_id) = 2;
Check out the demo here. Let me know if it works for you.
This one might work for you in MySQL, if group_concat works the same way it does in PostgreSQL:
select
product_id
from products
left join ( select group_concat(cast(id as char(5)), ',') as agg1 from sz where id in (1, 3) group by size_id ) as qagg1 on 1=1
left join ( select products_sizes.product_id product_id, group_concat(cast(products_sizes.size_id as char(5)), ',') agg2 from sz where products_sizes.size_id IN (1, 3) group by products_sizes.product_id ) as qagg2 on 1=1
where qagg1.agg1 = qagg2.agg2
group by product_id
This is the original query, tested in PostgreSQL:
select
product_id
from pr
left join ( select string_agg(cast(id as char(5)), ',') as agg1 from sz where id in (1, 3) group by size_id ) as qagg1 on 1=1
left join ( select sz.product_id product_id, string_agg(cast(sz.size_id as char(5)), ',') agg2 from sz where sz.size_id IN (1, 3) group by sz.product_id ) as qagg2 on 1=1
where qagg1.agg1 = qagg2.agg2
group by product_id
Talking about performance, it would be a nice deal to filter to a certain size_id first:
SELECT STRAIGHT_JOIN
x.id, y.size_id AS size_1, z.size_id AS size_2
FROM (
SELECT * FROM products_sizes
WHERE size_id = 1
) AS y
INNER JOIN products AS x
ON (x.id = y.product_id)
INNER JOIN (
SELECT * FROM products_sizes
WHERE size_id = 3
) AS z
ON (x.id = z.product_id);
Humble suggestion, make some tests with a higher amount of data (:
select `products`.id, `products_sizes`.`size_id`
from `products` inner join `products_sizes` on `products`.`id` = `products_sizes`.`product_id`
where products_sizes.size_id IN (1, 3)
group by products.id
having count(distinct `products_sizes`.size_id) = 2;

SQL conditional relations

I have three tables:
Product(columns: Id, ProductType, ProductId, ...)
Apple(columns: Id, ProductType(will be always 1), ...)
Orange(columns: Id, ProductType(will be always 2), ...)
If Product a has ProductType 1, I want to relate it with Apple Table, If 2, then To Orange Table. Is it possible in SQL?
In MySQL, you could do:
SELECT *
FROM Product p
JOIN Apple a ON (p.ProductType = 1 AND p.ProductId = a.Id)
JOIN Orange o ON (p.ProductType = 2 AND p.ProductId = o.Id)
you could do a union of all the subtypes then an outer join.

Sort MySQL Query based on SUM

I struggle to get the right query:
There are two tables: articles and orders
I want to get a list from all the articles, but sorted by the times they were ordered. (Not only how many times the item was ordered, also including the amount)
orders: id, article_id, amount
articles: id, description
Example:
articles
1, apple
2, orange
3, lime
orders
1, 1, 5
2, 3, 1
3, 3, 2
4, 2, 1
Output should be:
1, apple, 5
3, lime, 3
2, orange, 1
This is a basic join and group by query, two fundamentals of the SQL language. You should learn the language if you want to use databases effectively.
select a.`key`, count(o.id) as cnt, sum(amount)
from articles a left join
orders o
on o.article_key = a.`key`
group by a.`key`
order by cnt desc;
select a.key, a.description, sum(amount) as sum_amount
from articles a
left join orders o on o.article_key = a.key
group by a.key, a.description
order by sum_amount desc

Resources