Combine columns from two tables AS one result and THEN use in SELECT query

0
votes

I have a SELECT query from two different tables (Product & Supplier Codes - inner joined) where a specified column is %LIKE% my search string. 

SELECT product.*, supplier_code.PART_NO AS PART_NO
FROM product INNER JOIN supplier_code ON 
product.PRODUCT_ID=supplier_code.PRODUCT_ID  
AND supplier_code.MAINSUPP = 1 
WHERE $column LIKE ? 
LIMIT 0, 25

The problem I'm having is one of the columns (Barcode) has alternatives which are stored in a third table - AltBCode. So if $column = Barcode, that match could be in Product.Barcode OR it could be in AltBCode.Alt_Barcode (never both and both are unique).

I can only asume I need to combine these two columns together first as one AllBarcodes column say, and then use this in my Select query?

The layout of the two barcode storing tables is:

Product Table
PRODUCT_ID | Barcode | Description | Price | Stock | Etc
1          |  12345  |   Apple     | 1.00  |    4  |  
2          |  45678  |   Orange    | 0.50  |    2  |
3          |  91583  |   Banana    | 2.00  |    0  |

AltBCode Table
Id | PRODUCT_ID | Alt_Barcode
1  |    2       |   4321

So if I search for "4321" I want to return Row 2 from Product.

UPDATE

Thanks for the responses guys. I'm not sure I was clear enough though.

Once Product.Barcode and AltBCODE.Alt_code are joined they are the $column I want to be able to use in my WHERE clause. So:

SELECT product.*, supplier_code.PART_NO AS PART_NO
(Magically Select product.barcode and altbcode.altcode AS ALLBARCODES)
FROM product INNER JOIN supplier_code ON 
product.PRODUCT_ID=supplier_code.PRODUCT_ID  
AND supplier_code.MAINSUPP = 1 
WHERE ALLBARCODES LIKE %4321%
LIMIT 0, 25

So to simplify if:

Table Product
PROD_ID | Barcode | Desc
2       |  1234   | Apples

Table Alt Barcodes'
ID | PROD_ID | ALT_CODE
 1 |    2    | 2345'
 2 |    2    | 3456
 3 |    2    | 4567

WHERE PROD_ID = 2 would give me 4 rows: 

PROD_ID -> 2, ALLBARCODE ->1234, DESC- > APPLES  
PROD_ID -> 2, ALLBARCODE ->2345, DESC- > APPLES  
PROD_ID -> 2, ALLBARCODE ->3456, DESC- > APPLES  
PROD_ID -> 2, ALLBARCODE ->4567, DESC- > APPLES

BUT WHERE ALLBARCODE LIKE %2345% would only match 1

4
mysqlsqlinner-join
which barcode do you want to return? the original barcode, or the alternate barcode? – Sean
The Alternatre Barcode along with the matching Product_ID row – Amused
There's no LIKE needed here. This is just a simple join. – Strawberry
@Strawberry its possible that the LIKE is for the value, not the column, ie. Barcode LIKE '%4321%' or Alt_Barcode LIKE '%4321%' – Sean
@Sean you are correct. The LIKE is to find a wildcard match to part of the barcode (so get all products from the same manufacture say) . – Amused

4 Answers

0
votes

First left join Product Table and AltBCode Table so you have both code on same table.

SELECT PT.*, AT.Alt_Barcode
FROM `Product Table` PT
LEFT JOIN `AltBCode Table`  AT
       ON PT.PRODUCT_ID = AT.PRODUCT_ID

Your Final query should be

I use = instead of LIKE

SELECT *,
       $column as MatchBarcode
FROM supplier S
JOIN `Product Table` PT
  ON S.PRODUCT_ID = PT.PRODUCT_ID 
LEFT JOIN `AltBCode Table`  AT
       ON PT.PRODUCT_ID = AT.PRODUCT_ID 
WHERE S.MAINSUPP = 1 
  AND ( 
          $column = AT.`Alt_Barcode`
       OR $column = PT.`Barcode`
      )

First I tought in use 

CASE 
    WHEN $column = AT.`Alt_Barcode` THEN AT.`Alt_Barcode`
    WHEN $column = PT.`Barcode` THEN PT.`Barcode`
END as MatchBarcode

But that is equivalent to $column as MatchBarcode

0
votes

If I'm understanding the issue correctly, you can use a CASE WHEN to do this. Join to the AltBCode table first, then use the CASE statement to filter which column you return based on the criteria (might need to change the conditions). Something like:

SELECT product.*, supplier_code.PART_NO AS PART_NO,
    case 
        when MyColumn = 'Barcode' then Product.Barcode
        when MyColumn = 'NotBarcode' then AltBCode.Alt_Barcode
    end as 'Barcode'
FROM product INNER JOIN supplier_code ON 
product.PRODUCT_ID=supplier_code.PRODUCT_ID  
AND supplier_code.MAINSUPP = 1 
inner join AltBCode on product.PRODUCT_ID = AltBCode.PRODUCT_ID
WHERE $column LIKE ?
LIMIT 0, 25

Hope that helps

0
votes

Do that:

   SELECT
  a.* from product a, altbProduct b
  where a.product_id = b.product_id
and (a.barcode='4321' or b.altbarcode='4321');

Here is a simple SQLFidle without the supplier_code: DEMO

0
votes

you want to left join the alt table to not filter out rows and use coalesce

SELECT product.*, supplier_code.PART_NO AS PART_NO, coalesce(at. Alt_Barcode, supplier_code.barcode) as barcode
FROM product 
LEFT JOIN supplier_code ON product.PRODUCT_ID=supplier_code.PRODUCT_ID AND supplier_code.MAINSUPP = 1 
LEFT JOIN AltBCode ac ON product.PRODUCT_ID = ac.PRODUCT_ID
WHERE $column LIKE ? 
LIMIT 0, 25