CODEWITHSUNDEEP
mysql
bbruman
bbruman

Reputation: 677

MySQL Table Alias Name Only Resulting First Alias in Union

I have multiple tables amazonlistings ebaylistings shopifylistings with the same column SKU.

I'm trying to create a query that searches the SKU column in all these tables. Right now I'm doing this with UNION.

$channeldetectquery = "(SELECT SKU as amazon FROM amazonlistings WHERE SKU = '$channelskuarray[$i]') 
    UNION
(SELECT SKU as ebay FROM ebaylistings WHERE SKU = '$channelskuarray[$i]')
    UNION
(SELECT SKU as shopify FROM shopifylistings WHERE SKU = '$channelskuarray[$i]');"

If I do these individually... 

SELECT SKU as ebay FROM ebaylistings WHERE SKU = 'Product SKU'

it provides the SKUS from ebay as intended... but for some reason when trying to use the above query with UNION it is only providing the first alias (amazon) no matter the result.

My intended result (now, I'm not sure if this is ideal or not, but this is what I was trying to achieve), would be like

[amazon]  [ebay]  [shopify]
 SKU 1              SKU 1

[amazon]  [ebay]  [shopify]
           SKU 2

etc... an empty result set (or no result set), if the SKU is not found, and a result if the SKU is found. This allows me to check and see if the SKU is found in the appropriate table.

Upvotes: 1

Views: 102

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

The select union build a resulting table and a table can have only a column name so you can't assign different alias to the same column name .. and for this reason sql return only the first

You can't obtain different alias from the union this way

instead you could mark each value with a proper value 

 $channeldetectquery = 
"(SELECT  'amazon' as source, SKU FROM amazonlistings WHERE SKU = '$channelskuarray[$i]') 
    UNION
(SELECT 'ebay', SKU  FROM ebaylistings WHERE SKU = '$channelskuarray[$i]')
    UNION
(SELECT 'shopify', SKU  FROM shopifylistings WHERE SKU = '$channelskuarray[$i]');"

In this way you obatain different qualfied rows

and the you could 

select * from 
(SELECT  'amazon' as source, SKU FROM amazonlistings WHERE SKU = '$channelskuarray[$i]') 
    UNION
(SELECT 'ebay', SKU  FROM ebaylistings WHERE SKU = '$channelskuarray[$i]')
    UNION
(SELECT 'shopify', SKU  FROM shopifylistings WHERE SKU = '$channelskuarray[$i]') t
where t.SKU  =  'YOUR_SKU'

obtaining all the YOUR_SKU product for all the source

But if you need all the result for same SKU on the same row (like in the sample you provided ) you should use left join 

$channeldetectquery = 
"(SELECT amazonlistings.SKU as amazon, ebaylistings.SKU as ebay, hopifylistings.SKU as shopify  FROM amazonlistings 
LEFT JOIN ebaylistings  ON amazon.SKU = ebaylistings.SKU
LEFT JOIN hopifylistings ON amazon.SKU = hopifylistings.SKU
WHERE amazon.SKU = '$channelskuarray[$i]'
WHERE SKU = '$channelskuarray[$i]') ";

Upvotes: 2

Related Questions