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

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);
Parm singhAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Oracle or MySQL?

Can you also post more sample data and expected results?
0
Parm singhAuthor 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
0
awking00Information Technology SpecialistCommented:
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)
;
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

skullnobrainsCommented:
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.
0
Parm singhAuthor Commented:
I tried all provided solution it doesn't work
0
slightwv (䄆 Netminder) Commented:
Try this:
select org,pid,uid,oid from tab3
where (org,pid,uid) in (
	select org,pid,uid from (
		select org,pid,uid,count(case when oid!=940 then 1 end) cnt_not_940,
		count(case when oid=940 then 1 end) cnt_940
	from tab3
	group by org,pid,uid
	) inner_tab
	where cnt_not_940 > 0 and cnt_940 > 0
); 

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Parm singhAuthor Commented:
Thanks you gave me idea to think about this out of box, Now i Can modify this query according to my requirement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.