CODEWITHSUNDEEP
mysql
L Vermeulen
L Vermeulen

Reputation: 69

MySQL: Compare two tables and find only matching data/filter first table by matched columns in second

I have sales data that contains Cust_ID, Invoice_Date, Invoice, Item_Number (and plenty other fields). This table has multiple records per Customer's Invoice, because each record holds different Item_Number info. Let's call this table sales_main.

I have a second table that contains only three of the fields mentioned above, namely Cust_ID, Invoice_Date, and Invoice. This table only contains one row per customer. Let's call this table sales_filter.

What I want to do is essentially use the second table as a filter on the first, so as to keep only rows from table sales_main (and all its columns) where the Cust_ID, Invoice_Date and Invoice columns in sales_filter and sales_main are an exact match.

So if sales_filter has a row that contains:

"1024", "24/02/2016", 533

and sales_main has four rows that match all three criteria, all four rows are returned in the result set. Same for any other rows in sales_main that match all three columns in any row from sales_filter exactly.

But I just cannot figure out what code to use to do this!

Upvotes: 0

Views: 524

Answers (1)

Rams
Rams

Reputation: 2169

You need to join those two tables based on those 3 columns then it will return all the records with matched condition

select * from sales_main sm join sales_filter sf on (sm.Cust_ID=sf.Cust_ID and sm.Invoice_Date=sf.Invoice_Date and  sm.Invoice=sf.Invoice)

You can select the fields whichever you want in select

Upvotes: 1

Related Questions