13,700,125 members
See more:
hi guys i got this table :

```client     type     school
a           1          123
a           2          123
b           1          123
b           2          123
c           1          123
d           2          123
e           3          123```

please note that this table is created for this example.

i need to get thos client that their type is 1 and 2 so in this case i want clients a & b

How can i make the query to get those 2 only.

What I have tried:

i tried the query but it is not good :
select distinct client from table
where
(type = 1) or ( type = 2);
Posted 11-Jul-18 19:41pm
Updated 11-Jul-18 23:20pm
v2
Kornfeld Eliyahu Peter 12-Jul-18 4:35am

Do you wrote: "type is 1 and 2" - that should include c and d too...
Or do you mean those that have type 1 and 2 too?
Sigmond Gatt 12-Jul-18 5:03am

the client needs to have both types to be selected. i have found the solution you can look at in the solution section.
Kornfeld Eliyahu Peter 12-Jul-18 5:07am

Your solution has nothing to do with your question and sample as type never 'a' or 'b'...

## Solution 2

guys i found the solution for what i meant.

```select distinct client from table a
join table b on
b.client = a.client
where a.type= '1'
and b.type = '2'```

this will return all the client that have both types.
v2
Kornfeld Eliyahu Peter 12-Jul-18 5:08am

Obviously wrong! Type is never 'a' or 'b'?
Sigmond Gatt 12-Jul-18 5:30am

ohh sorry, i've updated the solution.

## Solution 3

```SELECT T1.CLIENT
FROM [YOUR_TBL] T1
INNER JOIN [YOUR_TBL] T2 ON T2.CLIENT = T1.CLIENT AND T2.TYPE <> 1
WHERE
T1.TYPE = 1 AND T2.TYPE = 2```
v2
Sigmond Gatt 12-Jul-18 5:31am

why T2.TYPE <> 1? .. will make any difference?
Kornfeld Eliyahu Peter 12-Jul-18 5:34am

Less (half?) rows in the JOIN...
Sigmond Gatt 12-Jul-18 5:35am

ohh ok i will try it. thank you :)

## Solution 1

```select client,type1 from school
where (type1=1 or type1=2) and (client='a' or client='b');```
Kornfeld Eliyahu Peter 12-Jul-18 5:08am

And what if in the real data has other client both with type 1 and 2?
Sigmond Gatt 12-Jul-18 5:33am

yeah good question kornfeld. This solution is wrong.

Top Experts
Last 24hrsThis month
 OriginalGriff 332 Richard MacCutchan 118 Pete O'Hanlon 90 Rick York 78 Maciej Los 75
 OriginalGriff 4,502 Patrice T 1,551 Richard MacCutchan 1,468 Maciej Los 1,109 Vincent Maverick Durano 575