CODEWITHSUNDEEP
sqlsql-server
Nathan11205
Nathan11205

Reputation: 5

sql where have all multiple conditions in entry table

I've been trying to wrap my brain around this using joins, subquery joins, not exists clauses and I keep failing to come up with a query that produces the correct results.

I have 2 table's Trans and TransEntry

Trans Table (PRIMARY TransID)

TransID | Date 
-----------
1        1/1/18           
2        1/2/18          
3        1/3/18

TransEntry Table (PRIMARY TransEntryID) 

TransEntryID | TransID |Item
-----------
1              1        A
2              1        B
3              1        C
4              2        A
5              2        D
6              2        F
7              3        A
8              3        B
9              3        G
10             3        C

I need to have all TransID from TransEntry where the have item A and item C in the Entry, in our sample it will be only TransID (1,3)

Upvotes: 0

Views: 66

Answers (6)

JNevill
JNevill

Reputation: 50119

I think something like this should work:

SELECT TransID
FROM TransEntry
WHERE Item in ('A','C')
GROUP BY TransID
HAVING Count(DISTINCT Item) = 2;

Here's a sqlfiddle showing this

CREATE TABLE TransEntry(
   TransEntryID   INTEGER  NOT NULL PRIMARY KEY 
  ,TransID        INTEGER  NOT NULL
  ,Item           VARCHAR(1) NOT NULL
);
INSERT INTO TransEntry(TransEntryID,TransID,Item) VALUES
 (1,1,'A')
,(2,1,'B')
,(3,1,'C')
,(4,2,'A')
,(5,2,'D')
,(6,2,'F')
,(7,3,'A')
,(8,3,'B')
,(9,3,'G')
,(10,3,'C');

SELECT TransID
FROM TransEntry
WHERE Item in ('A','C')
GROUP BY TransID
HAVING Count(DISTINCT Item) = 2

+---------+
| TransID |
+---------+
|       1 |
|       3 |
+---------+

Upvotes: 0

shoaib
shoaib

Reputation: 29

SELECT     TransEntry.TransID, TransEntry.Item, GROUP_CONCAT(TransEntry.Item) AS items
FROM       TransEntry
group by   TransEntry.TransID Having Find_In_Set('A',items)>0 and Find_In_Set('C',items)>0

see the jsfiddle

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use GROUP BY :

SELECT TransID
FROM TransEntry
GROUP BY TransID
HAVING SUM(CASE WHEN item = 'A' THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN item = 'C' THEN 1 ELSE 0 END) > 0;

Upvotes: 0

Y.S
Y.S

Reputation: 1862

you can go for something like this:

select transId 
from
(
    select 
        TransID , 
        case when item = 'A' then 1 else 0 end as HasA,
        case when item = 'C' then 1 else 0 end as HasC
    from TransEntry
) a
group by transId
having sum(hasA) > 0 and sum(hasC) > 0

Upvotes: 0

Paurian
Paurian

Reputation: 1402

My answer wasn't as succinct as Aaron's, but in case it's helpful:

SELECT
  TransID
FROM
  Trans
WHERE
  TransID IN ( SELECT TransID FROM TransEntry WHERE Item = 'A' ) AND
  TransID IN ( SELECT TransID FROM TransEntry WHERE Item = 'C' )

Upvotes: 0

Aaron Reese
Aaron Reese

Reputation: 131

or

SELECT TransID FROM TransEntry WHERE Item = 'A'
INTERSECT
SELECT TransID FROM TransEntry WHERE Item = 'C'

Upvotes: 1

Related Questions