MYSQL Query to SELECT row based on a value (if exists) otherwise return data from another table

Question

I have two tables:

CATALOG:
------------------------------------
| itemID    | itemCode  | itemName |
------------------------------------

ORDER:
----------------------------------------------
| orderID   | itemID_FK | itemPrice | etc... |
----------------------------------------------

I want to select itemName, itemPrice, itemCode etc by using a join on both tables.

So far so good; however, the row corresponding to a given itemID in CATALOG may not exist in the ORDER table. In which case, I want to fall back on using itemName, itemCode from the first table CATALOG.

Is this possible using MYSQL in a single (compound) statement?

EDIT: Here's a sample table

CATALOG:
------------------------------------
| itemID    | itemCode  | itemName |
------------------------------------
|  1        |  89873232 | Oats     |
|  2        |  32849392 | Beer     |
------------------------------------

ORDER:
----------------------------------------------
| orderID   | itemID_FK | itemPrice | etc... |
----------------------------------------------
| 213232    |  2        |  3.99     |  ...   |
----------------------------------------------

I use the following query to retrieve complete data about an item:

SELECT itemID, itemName, itemPrice FROM CATALOG
INNER JOIN ORDER
ON itemID = itemID_FK
WHERE itemID = %d

I need this join because CATALOG will not contain price which is obtained from ORDER. If I use itemID=1 there would not be a corresponding entry in ORDER table and the query would fail. I want it to retrieve at least itemName if there is no entry for the item in ORDER

What do you currently join on? Can you give your current query? If you do ...FROM CATALOG LEFT JOIN ORDER ON CATALOG.itemID=ORDER.itemID_FK then you'll always get rows from ORDER for each row in CATALOG, an if it can't find it you'll have NULL in that column. — mathematical.coffee
– mathematical.coffee, Commented Jan 18, 2012 at 5:12
Can you show some sample data and an example of the resultset you're after? — Phil
– Phil, Commented Jan 18, 2012 at 5:13

Web User · Accepted Answer · 2012-01-18 05:11:46Z

1

Just use a LEFT OUTER JOIN between the two tables, so you will always have data corresponding to the table on the LEFT side of the JOIN:

SELECT C.itemName, C.itemCode, O.itemPrice, ...
FROM CATALOG as C
     LEFT OUTER JOIN ORDER AS O ON C.itemID = O.itemID_FK

All the O.s in the above selection will turn up as NULL if the item from the CATALOG does not exist in ORDERS.

answered Jan 18, 2012 at 5:11

Web User

7,83615 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Over a year ago

The OUTER keyword is redundant. A LEFT JOIN is implicitly outer

Collectives™ on Stack Overflow

MYSQL Query to SELECT row based on a value (if exists) otherwise return data from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related