Oracle question (may be simple for some of you.)

Question

0.00/5 (No votes)

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

Joe Doe234

Updated 11-Jul-18 23:20pm

v2

Add a Solution

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?

Joe Doe234 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'...

3 solutions

Solution 1

select client,type1 from school
where (type1=1 or type1=2) and (client='a' or client='b');

Posted 11-Jul-18 22:34pm

Member 13908591

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?

Joe Doe234 12-Jul-18 5:33am

yeah good question kornfeld. This solution is wrong.

Add a Solution

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

Joe Doe234 · Accepted Answer · 2018-07-11T23:01:00

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.

Posted 11-Jul-18 23:01pm

Joe Doe234

Updated 11-Jul-18 23:29pm

v2

Comments

Kornfeld Eliyahu Peter 12-Jul-18 5:08am

Obviously wrong! Type is never 'a' or 'b'?

Joe Doe234 12-Jul-18 5:30am

ohh sorry, i've updated the solution.

Kornfeld Eliyahu Peter · Accepted Answer · 2018-07-11T23:21:00

Solution 3

SQL

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

Posted 11-Jul-18 23:21pm

Kornfeld Eliyahu Peter

Updated 11-Jul-18 23:22pm

v2

Comments

Joe Doe234 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...

Joe Doe234 12-Jul-18 5:35am

ohh ok i will try it. thank you :)

Oracle question (may be simple for some of you.)

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Oracle question (may be simple for some of you.)

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us