CODEWITHSUNDEEP
phpmysqlsqlselectsubquery
yelooo
yelooo

Reputation: 73

MySQL Subquery, Help Needed Constructing Select Statement

I'm having problems constructing a select query.

I have a table which I have shown in a simplified version below. There are many more columns than those I have shown, but I have ommitied those not relevent to this query.

ID   ReceiptNo  TransactionType 
--------------------------------
 1 | 2SJ1532   |  SALE
 2 | 8UG7825   |  SALE
 3 | 0619373   |  SALE
 4 | 8UG7825   |  RFND
 5 |           |  TEST

I want to select only those rows that have a transaction type "SALE" where the ReceiptNo for the selected row also appears in a row with transaction type RFND.

So in the above example I would want to select Row 2. Because it has Transaction Type SALE and its ReceiptNo also appears in a row with transaction type RFND.

I have the following query to select all the SALE type transaction types, but I think I need a subquery to make the select work as descriped above. If anyone could help that would be great.

SELECT * FROM $table_name WHERE RecieptNo IS NOT NULL AND TRIM(RecieptNo) <>  '' AND   TransactionType = 'SALE'

Upvotes: 1

Views: 678

Answers (3)

Aboelseoud
Aboelseoud

Reputation: 555

SELECT *
FROM $table_name X1
WHERE X1.TransactionType = 'SALE'
AND EXISTS (
SELECT X2.ReceiptNo
FROM $table_name X2
WHERE X1.ReceiptNo=X2.ReceiptNo
AND X2.TransactionType = 'RFND'
)

Upvotes: 1

Vatev
Vatev

Reputation: 7590

SELECT t.*
FROM your_table t
JOIN your_table t2 ON t2.TransactionType = 'RFND' AND t2.ReceiptNo=t.ReceiptNo
GROUP BY t.ID

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Try this:

SELECT * 
FROM $table_name
WHERE RecieptNo IN(SELECT RecieptNo 
                   FROM tablename
                   WHERE TransactionType IN('Sale', 'RFND')
                   GROUP BY RecieptNo 
                   HAVING COUNT( DISTINCT TransactionType) = 2);

This will ensure that the selected RecieptNo have both transaction types Sale, RFND.

Upvotes: 1

Related Questions