Meyka Jograt Meyka Jograt - 4 months ago 8
MySQL Question

Combine Multiple Tables using LEFT JOIN and UNION

I got 5 different Tables to get the data. But first I use 3 tables for testing. Using LEFT JOIN and UNION. So far this is the code iv'e been using.

SELECT sts.TransferDate as 'Transaction Date', sts.TransNumber as 'Document Number', orderset_details.Quantity as 'Quantity', '' as 'Po Number', '' as 'Sales Invoice', orderset_details.Cost as 'Cost', orderset_details.SerialNumber as 'Serial Number', orderset_details.LotNumber as 'Lot Number', location.Location as 'Location'
FROM orderset_details
LEFT JOIN sts ON sts.OrdersetId = orderset_details.OrdersetId
LEFT JOIN location ON location.LocationId = sts.LocationIdTo
WHERE orderset_details.ItemId = '3071'
UNION
SELECT drs.OrderDate as 'Transaction Date', drs.TransNumber as 'Document Number', orderset_details.Quantity, drs.PONumber as 'Po Number', '' as 'Sales Invoice', orderset_details.Cost as 'Cost', orderset_details.SerialNumber as 'Serial Number', orderset_details.LotNumber as 'Lot Number', suppliers.Supplier as 'Location'
FROM orderset_details
LEFT JOIN drs ON drs.OrdersetId = orderset_details.OrdersetId
LEFT JOIN suppliers ON suppliers.SupplierId = drs.SupplierId
WHERE orderset_details.ItemId = '3071'
UNION
SELECT stockadjustment.TransactionDate as 'Transaction Date', stockadjustment.TransactionId as 'Document Number', orderset_details.Quantity as 'Quantity', '' as 'Po Number', '' as 'Sales Invoice', orderset_details.Cost as 'Cost', orderset_details.SerialNumber as 'Serial Number', orderset_details.LotNumber as 'Lot Number', location.Location as 'Location'
FROM orderset_details
LEFT JOIN stockadjustment ON stockadjustment.OrdersetId = orderset_details.OrdersetId
LEFT JOIN location ON location.LocationId = stockadjustment.LocationId
WHERE orderset_details.ItemId = '3071'
Group By orderset_details.ItemId


And here is the output.
enter image description here

The output should be only 1 and that is the last row. My problem is when my 1 or 2 queries is nothing its duplicating some columns which is not right.

How can I only show the data that has been equals to specific ItemId? What is wrong with my query?

Answer

In your query you should use alias only in the first select and you don't have aggregation function so you don't need group by .. because unio return only distinct result

SELECT 
    sts.TransferDate as 'Transaction Date'
  , sts.TransNumber as 'Document Number'
  , orderset_details.Quantity as 'Quantity'
  , '' as 'Po Number'
  , '' as 'Sales Invoice'
  , orderset_details.Cost as 'Cost'
  , orderset_details.SerialNumber as 'Serial Number'
  , orderset_details.LotNumber as 'Lot Number'
  , location.Location as 'Location' 
FROM orderset_details 
LEFT JOIN sts ON sts.OrdersetId = orderset_details.OrdersetId
LEFT JOIN location ON location.LocationId = sts.LocationIdTo  
WHERE orderset_details.ItemId = '3071' 
UNION 
SELECT 
      drs.OrderDate 
    , drs.TransNumber 
    , orderset_details.Quantity
    , drs.PONumber 
    , ''
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber 
    , suppliers.Supplier 
FROM orderset_details 
LEFT JOIN drs ON drs.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN suppliers ON suppliers.SupplierId = drs.SupplierId 
WHERE orderset_details.ItemId = '3071'
UNION 
SELECT 
      stockadjustment.TransactionDate 
    , stockadjustment.TransactionId 
    , orderset_details.Quantity 
    , '' 
    , '' 
    , orderset_details.Cost 
    , orderset_details.SerialNumber 
    , orderset_details.LotNumber 
    , location.Location     
FROM orderset_details 
LEFT JOIN stockadjustment ON stockadjustment.OrdersetId = orderset_details.OrdersetId 
LEFT JOIN location ON location.LocationId = stockadjustment.LocationId 
WHERE orderset_details.ItemId = '3071'