We help IT Professionals succeed at work.

How to select record with In and condition together based on more then one coulmn

Parm singh
Parm singh asked
on
164 Views
Last Modified: 2017-03-20
I trying to get some thing like this if same uid have pid 36 and oid = 940 as passed in query then need to select other record with same pid and uid with other oid as shown following, But if this uid 1138 and pid - 36 does not have oid 940 then it should not show other two record also

org pid uid  oid
938 36  1138 938
938 36  1138 940
938 36  1138 941

I am doing some thing like this But doesn't get logic how it will work

SELECT     DISTINCT user_org.oid org,
           pid,
           user_users.uid,
           acl_user_perms.oid
FROM       `acl_user_perms`
INNER JOIN user_users USING(uid)
INNER JOIN user_org
ON         (user_users.oid = user_org.oid)
INNER JOIN user_org_admin
ON         (user_org.oid = user_org_admin.oid)
WHERE      pid = 36
and        value = 1
and        acl_user_perms.oid = 940
and        acl_user_perms.oid IN (938,945,941);
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Oracle or MySQL?

Can you also post more sample data and expected results?

Author

Commented:
Thanx for look at my question,  Its MYSQL, MORE sample data is just same . In this case It need to show only record have oid - 940 with at least one another oid, as last record of sample data  1205 36  1405 940 that also should not be shown, because it has only with 940 not any other,
Sample data :
org pid uid  oid
938 36  1138 938
938 36  1138 940
938 36  1138 941
961 36  1167 938
1180 36 1380 938
1180 36 1380 940
1182 36 1382 945
1183 36      1383 945
1184 36      1384 945
1191 36      1391 940
1191 36      1391 945
1205 36  1405 940

Expected Result

org pid uid  oid
938 36  1138 938
938 36  1138 940
938 36  1138 941
1180 36 1380 938
1180 36 1380 940
1191 36      1391 940
1191 36      1391 945
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
select org, pid, uid, oid
from yourtbl t1
where exists
(select org, pid, uid, count(*) cnt
 from yourtbl t2
 where t2.org = t1.org
 and t2.pid = t1.pid
 and t.uid = t1.uid
 and pid = 36
 group by org, pid, uid
 having count(*) > 1)
;
CERTIFIED EXPERT

Commented:
select t1.*
from yourtbl t1
inner join yourtbl t2
on t1.uid = t2.uid and t1.pid = t2.pid
where
t2.pid = 36
and t2.oid = 640

or given the complexity of the query, revert to this one

select t1.*
from tbl
where
pid = 36
and uid = ( select uid from tbl where pid=36 and oid = 640 )

both of the above would work on a single table

if you have a working query that pulls the provided info, you should be able to integrate this logic. post a working query that barely pulls the list of all uid,pid,oid if you need help doing the merge.

Author

Commented:
I tried all provided solution it doesn't work
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks you gave me idea to think about this out of box, Now i Can modify this query according to my requirement.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions