Click here to Skip to main content
13,768,135 members
Rate this:
 
Please Sign up or sign in to vote.
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 20:41pm
Updated 12-Jul-18 0:20am
v2
Comments
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'...
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
v2
Comments
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
v2
Comments
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 :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

select client,type1 from school
where (type1=1 or type1=2) and (client='a' or client='b');
  Permalink  
Comments
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.181116.1 | Last Updated 12 Jul 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100