Avatar of Parm singh
Parm singh
 asked on

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);
MySQL Server

Avatar of undefined
Last Comment
Parm singh

8/22/2022 - Mon
slightwv (䄆 Netminder)

Oracle or MySQL?

Can you also post more sample data and expected results?
Parm singh

ASKER
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
awking00

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)
;
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
skullnobrains

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.
Parm singh

ASKER
I tried all provided solution it doesn't work
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Parm singh

ASKER
Thanks you gave me idea to think about this out of box, Now i Can modify this query according to my requirement.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.