CODEWITHSUNDEEP
mysql
Saqib
Saqib

Reputation: 1129

show two colum from two tables in mysql with a UNION

I have an SQL query in which I had to get value from two different tables (Say a1, a2). For simple reason it works but as I have a UNION in the same query for an important reason (to get a value from table a2 to place in between a column value of a1) due to this my query result comes out to be odd. Here is my query and image which shows the duplicate entries of same TICKET_ID, I want to remove that, how?

QUERY 

SELECT 
 RECEIPTS.DATENEW AS DATE,
 TICKETS.TICKETID AS TICKETID,
 PAYMENTS.PAYMENT AS PAYMENT,
 PAYMENTS.TOTAL AS TOTAL,
 CUSTOMERS.NAME AS NAME,
 ADJUSTMENTS.ADJUSTMENT_REASON AS REASON
FROM RECEIPTS
 INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
 INNER JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
 INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
 RIGHT JOIN ADJUSTMENTS ON ADJUSTMENTS.CUSTOMER_ID = CUSTOMERS.ID
WHERE
 (PAYMENTS.PAYMENT = 'debt'
 OR PAYMENTS.PAYMENT = 'debtpaid')
UNION SELECT 
 ADJUSTMENTS.DATE AS DATE,
 ADJUSTMENTS.TICKET_NO AS TICKETID,
 ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT,
 ADJUSTMENTS.ADJUSTMENT_AMOUNT * -1 AS TOTAL,
 ADJUSTMENTS.CUSTOMER_NAME AS NAME,
 ADJUSTMENTS.ADJUSTMENT_REASON AS REASON
FROM ADJUSTMENTS
 ORDER BY NAME ASC, DATE DESC

and here is the output image of it

Output

enter image description here

Upvotes: 0

Views: 98

Answers (2)

Saqib
Saqib

Reputation: 1129

Finally I figured it out, now the query becomes as below

SELECT
 RECEIPTS.DATENEW AS DATE,
 TICKETS.TICKETID AS TICKETID,
 PAYMENTS.PAYMENT AS PAYMENT,
 PAYMENTS.TOTAL AS TOTAL,
 CUSTOMERS.NAME AS NAME,
 (SELECT ADJUSTMENTS.ADJUSTMENT_REASON FROM ADJUSTMENTS WHERE ADJUSTMENTS.ADJUSTMENT_TYPE != 'adjustment' AND ADJUSTMENTS.ADJUSTMENT_REASON != '') AS REASON
FROM RECEIPTS
 INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
 INNER JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
 INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
WHERE
 (PAYMENTS.PAYMENT = 'debt'
 OR PAYMENTS.PAYMENT = 'debtpaid')
UNION SELECT 
 ADJUSTMENTS.DATE AS DATE,
 ADJUSTMENTS.TICKET_NO AS TICKETID,
 ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT,
 ADJUSTMENTS.ADJUSTMENT_AMOUNT * -1 AS TOTAL,
 ADJUSTMENTS.CUSTOMER_NAME AS NAME,
 ADJUSTMENTS.ADJUSTMENT_REASON AS REASON
FROM ADJUSTMENTS
 ORDER BY NAME ASC, DATE DESC

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use GROUP BY or just check the reason column not null

SELECT q.* FROM (

SELECT 
 RECEIPTS.DATENEW AS DATE,
 TICKETS.TICKETID AS TICKETID,
 PAYMENTS.PAYMENT AS PAYMENT,
 PAYMENTS.TOTAL AS TOTAL,
 CUSTOMERS.NAME AS NAME,
 ADJUSTMENTS.ADJUSTMENT_REASON AS REASON
FROM RECEIPTS
 INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID
 INNER JOIN PAYMENTS ON RECEIPTS.ID = PAYMENTS.RECEIPT
 INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID
 RIGHT JOIN ADJUSTMENTS ON ADJUSTMENTS.CUSTOMER_ID = CUSTOMERS.ID
WHERE
 (PAYMENTS.PAYMENT = 'debt'
 OR PAYMENTS.PAYMENT = 'debtpaid')
UNION SELECT 
 ADJUSTMENTS.DATE AS DATE,
 ADJUSTMENTS.TICKET_NO AS TICKETID,
 ADJUSTMENTS.ADJUSTMENT_TYPE AS PAYMENT,
 ADJUSTMENTS.ADJUSTMENT_AMOUNT * -1 AS TOTAL,
 ADJUSTMENTS.CUSTOMER_NAME AS NAME,
 ADJUSTMENTS.ADJUSTMENT_REASON AS REASON
FROM ADJUSTMENTS
 ORDER BY NAME ASC, DATE DESC ) q GROUP BY q.TICKETID

OR 

 ORDER BY NAME ASC, DATE DESC ) q GROUP BY q.REASON !='' // q.REASON IS NOT NULL

Upvotes: 1

Related Questions