CODEWITHSUNDEEP
mysqlsql
Luke
Luke

Reputation: 21

SQL - Bring back sales transactions with 2 specific items in it AND/OR

I want to write a piece of SQL code that will bring back all sales transaction numbers with 2 specific items in it which I input. I just can't get the items part correct.

SELECT *
FROM [mastertable] TSE
WHERE TSE.Date BETWEEN '20160101' AND '20160501'
AND TSE.[Store No_] = '####'
AND TSE.[Item No_] = '133033'
AND TSE.[Item No_] = '144086`

Any suggestions?

Upvotes: 2

Views: 76

Answers (2)

Rich Benner
Rich Benner

Reputation: 8113

Give this a go;

SELECT *
FROM [mastertable] TSE
WHERE TSE.Date BETWEEN '20160101' AND '20160501'
AND TSE.[Store No_] = '####'
AND (TSE.[Item No_] = '133033'
OR TSE.[Item No_] = '144086`)

The reason your code wasn't working is that the item no needed to be both 113033 and 144086, you want it to be either of these. You could also update this to something like below;

AND TSE.[Item No_] IN ('133033','144086')

Although this will be optimised the same way.

Reading your responses that mention your item number should contain either of these numbers, is this what you're after?

SELECT *
FROM [mastertable] TSE
WHERE TSE.Date BETWEEN '20160101' AND '20160501'
AND TSE.[Store No_] = '####'
AND TSE.[Item No_] LIKE '%133033%'
and TSE.[Item No_] LIKE '%144086%')

Upvotes: 3

Praveen ND
Praveen ND

Reputation: 560

Please try this. It will work perfectly.

SELECT 
    *
FROM 
    [mastertable] TSE
WHERE 
    TSE.Date BETWEEN '20160101' AND '20160501'
    AND TSE.[Store No_] = '####'
    AND TSE.[Item No_] IN ('133033','144086')

Upvotes: 1

Related Questions