Menu
I have the following, working MySQL query:
SELECT
a.id id,
a.price price,
a.stock stock,
a.max_per_user max_per_user,
a.purchased purchased,
b.quantity owned
FROM
shop_items a
JOIN shop_inventory b
ON b.iid=a.id
AND b.cid=a.cid
WHERE
a.cid=1
AND a.szbid=0
AND a.id IN(3,4)
The JOIN joins the table shop_inventory b to return b.quantity owned. However, if there is no record in the shop_inventory b table where b.iid=a.id I want it to return b.quantity = 0. How would I do this?
752
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
You join two tables by creating a relationship in the WHERE clause between at least one column from one table and at least one column from another. The join creates a temporary composite table where each pair of rows (one from each table) that satisfies the join condition is linked to form a single row.
To combine result set of two or more queries using the UNION operator, these are the basic rules that you must follow: First, the number and the orders of columns that appear in all SELECT statements must be the same. Second, the data types of columns must be the same or compatible.
Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).
Use LEFT JOIN instead. And COALESCE since some of the records where null (I guess). Try,
SELECT a.id id,a.price price,a.stock stock,
a.max_per_user max_per_user,a.purchased purchased,
COALESCE(b.quantity, 0) owned
FROM shop_items a
LEFT JOIN shop_inventory b
ON b.iid=a.id AND b.cid=a.cid
WHERE a.cid=1 AND
a.szbid=0 AND
a.id IN(3,4)
Something like this should do the trick.
SELECT a.id id,a.price price,a.stock stock,
a.max_per_user max_per_user,a.purchased purchased,
COUNT(b.quantity) AS owned
FROM shop_items a
LEFT JOIN shop_inventory b
ON b.iid=a.id AND b.cid=a.cid
WHERE a.cid=1 AND a.szbid=0 AND a.id IN(3,4)
GROUP BY id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
