Executing Different mysql selects based on a field

Question

i have a series of tables for an asset checkout system. there are 2 types of assets.. Stocks and Assets. they all have barcodess stored in a separate table. this is how my tables look

Checkouts table: (sipe_check_out)

+----+--------+----------+----------+-----+---------------------+----------+------+----------+
| id | job_id | stock_id | asset_id | qty | out_time            | user_out | note | depot_id |
+----+--------+----------+----------+-----+---------------------+----------+------+----------+
| 1  | 1625   | 0        | 1026     | 1   | 0000-00-00 00:00:00 | 1288     |      | 1        |
+----+--------+----------+----------+-----+---------------------+----------+------+----------+
| 2  | 1625   | 8        | 0        | 10  | 0000-00-00 00:00:00 | 1288     |      | 0        |
+----+--------+----------+----------+-----+---------------------+----------+------+----------+

barcodes table: (sipe_barcodes)

+----+----------+----------+---------+
| id | stock_id | asset_id | barcode |
+----+----------+----------+---------+
| 1  | 0        | 1026     | AR2221  |
+----+----------+----------+---------+
| 2  | 8        | 0        | MAR0001 |
+----+----------+----------+---------+

Stock Table: (sipe_stock)

+-----+------------+-----------+--------+----------+---------------------+------+--------------+------+-------------+
| id  | title      | alt_title | status | category | last_updated        | memo | replace_cost | flag | description |
+-----+------------+-----------+--------+----------+---------------------+------+--------------+------+-------------+
| 8   | Cable XLR  | Cable XLR | 2      | 10       | 2019-01-03 20:45:40 |      | 750          | 0    |             |
+-----+------------+-----------+--------+----------+---------------------+------+--------------+------+-------------+
| 237 | Fresnel 1K |           | 0      | 43       | 2019-01-02 19:08:45 |      | 12000        | 0    |             |
+-----+------------+-----------+--------+----------+---------------------+------+--------------+------+-------------+

Asset Table: (sipe_stock_asset)

+------+----------+-----+--------+--------+---------------------+------+---------------+-------------+-------+
| id   | stock_id | qty | serial | status | status_change       | memo | purchase_date | retire_date | depot |
+------+----------+-----+--------+--------+---------------------+------+---------------+-------------+-------+
| 1026 | 237      | 1   |        | 0      | 2019-01-02 19:09:00 | NULL | NULL          | NULL        | 1     |
+------+----------+-----+--------+--------+---------------------+------+---------------+-------------+-------+

i need a list of all items checked out to a specific job_id. these items are sotred in the chekout table. but for display purpose i need to get the items barcode (stored in the barcodes table) , items title (stored in the stock table), and qty (stored in the checkout table)

this is my problem. the select query is different for a stock item and for an asset item. if it is a stock item one needs to get the stock id from the checkout table then get the title from the stock table and the qty from the checkout table.

this is my working query to get stock items

SELECT 
sipe_barcodes.barcode,
sipe_stock.title,
sipe_check_out.qty,
sipe_check_out.out_time
FROM
sipe_barcodes
INNER JOIN sipe_stock ON (sipe_barcodes.stock_id = sipe_stock.id)
INNER JOIN sipe_check_out ON (sipe_stock.id = sipe_check_out.stock_id)
WHERE
sipe_check_out.job_id = 1625

this returns the following

+---------+-----------+-----+---------------------+
| barcode | title     | qty | out_time            |
+---------+-----------+-----+---------------------+
| MAR0001 | Cable XLR | 10  | 0000-00-00 00:00:00 |
+---------+-----------+-----+---------------------+

If the item is a a stock. we get the asset_id from the checkout. but then we need to get the stock_id from the checkout table so we can get the stock title from the stock table. the qty from the checkout table and the barcode from the barcode table

this is my working query to get asset items

SELECT 
sipe_barcodes.barcode, 
sipe_stock.title, 
sipe_check_out.qty, 
sipe_check_out.out_time
FROM sipe_check_out 
INNER JOIN sipe_stock_asset ON (sipe_check_out.asset_id = sipe_stock_asset.id) 
INNER JOIN sipe_stock ON (sipe_stock_asset.stock_id = sipe_stock.id) 
LEFT JOIN sipe_barcodes ON (sipe_check_out.asset_id = sipe_barcodes.asset_id) 
WHERE sipe_check_out.job_id = 1625

this retuns this

+---------+------------+-----+---------------------+
| barcode | title      | qty | out_time            |
+---------+------------+-----+---------------------+
| AR2221  | Fresnel 1K | 1   | 0000-00-00 00:00:00 |
+---------+------------+-----+---------------------+

The Problem

how do i structure a single mysql query that can select all items checked out no matter if they are assets or stocks producing the following output

+---------+------------+-----+---------------------+
| barcode | title      | qty | out_time            |
+---------+------------+-----+---------------------+
| AR2221  | Fresnel 1K | 1   | 0000-00-00 00:00:00 |
+---------+------------+-----+---------------------+
| MAR0001 | Cable XLR  | 10  | 0000-00-00 00:00:00 |
+---------+------------+-----+---------------------+

your help is much Appreciated

Thanks

You may use [this](https://stackoverflow.com/a/16364510/1958051) approach or use [union](https://www.w3schools.com/sql/sql_union.asp) — user1987, May 10 '20 at 22:30
it took me more time to compose the question that to adapt my query according to your comment! thanks. the Union ALL did the trick for me — Ariel Ramos, May 10 '20 at 22:42
add this as an answer instead of a comment so i can mark it as the accepted answer for future refrence. thanks! — Ariel Ramos, May 10 '20 at 22:43

score 0 · Accepted Answer · answered May 10 '20 at 22:55

You may use UNION with your provided select statements

SELECT 
    sipe_barcodes.barcode,
    sipe_stock.title,
    sipe_check_out.qty,
    sipe_check_out.out_time
FROM
    sipe_barcodes
        INNER JOIN
    sipe_stock ON (sipe_barcodes.stock_id = sipe_stock.id)
        INNER JOIN
    sipe_check_out ON (sipe_stock.id = sipe_check_out.stock_id)
WHERE
    sipe_check_out.job_id = 1625 

UNION ALL 

SELECT 
    sipe_barcodes.barcode,
    sipe_stock.title,
    sipe_check_out.qty,
    sipe_check_out.out_time
FROM
    sipe_check_out
        INNER JOIN
    sipe_stock_asset ON (sipe_check_out.asset_id = sipe_stock_asset.id)
        INNER JOIN
    sipe_stock ON (sipe_stock_asset.stock_id = sipe_stock.id)
        LEFT JOIN
    sipe_barcodes ON (sipe_check_out.asset_id = sipe_barcodes.asset_id)
WHERE
    sipe_check_out.job_id = 1625

Executing Different mysql selects based on a field

1 Answers1