CODEWITHSUNDEEP
mysqloracle-database
Sahal
Sahal

Reputation: 4146

Multiple ID check in MYSQL WHERE condition

I have a query, I need to get a row only if all the ID match in WHERE clause

Here is the query

SELECT CompanyId
FROM vendors
WHERE VendorId
IN (
'306145244', '1011073437'
)

I want all the CompanyId which has both vendorId '306145244' and '1011073437'

If one of the VendorId match, I don't want that companyId. Both VendorId should be matched

While grouping I need 2 rows like below

company Id   Vendor Id
1             306145244
1             1011073437
2             306145244
2             306145244

If possible I want this query in ORACLE.

Upvotes: 3

Views: 1427

Answers (5)

JDGuide
JDGuide

Reputation: 6525

Try this :

SELECT CompanyId,VendorId
FROM vendors
WHERE VendorId
IN (
select VendorId from vendors
)
order by CompanyId asc;

Hope it will help you.

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

In SQL Server what we can do is:

SELECT CompanyId
FROM vendors
WHERE VendorId
IN ('306145244')
intersect
SELECT CompanyId
FROM vendors
WHERE VendorId
IN ('1011073437')

But MYSQL doesn't support intersect. Hence we have to approach alternative to intersect in MYSQL

select distinct CompanyId
FROM vendors
where vendorId IN ('306145244')
and CompanyId IN
(SELECT CompanyId
FROM vendors
WHERE VendorId
IN ('1011073437'))

Edit

select  CompanyId,VendorId
FROM vendors
where vendorId IN ('306145244')
and CompanyId IN
(SELECT CompanyId
FROM vendors
WHERE VendorId
IN ('1011073437'))
union
select  CompanyId,VendorId
FROM vendors
where vendorId IN ('1011073437')
and CompanyId IN
(SELECT CompanyId
FROM vendors
WHERE VendorId
IN ('306145244'))

SQL Fiddle

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15593

SELECT `CompanyId` , count( `CompanyId` )
FROM `vendors`
WHERE VendorId
IN (
'306145244', '1011073437'
)
GROUP BY `CompanyId`
HAVING count( `CompanyId` ) = 2;

Use this, this is working for me and tested.

Upvotes: 0

John Woo
John Woo

Reputation: 263813

SELECT  CompanyId
FROM    vendors
WHERE   VendorId IN ('306145244', '1011073437')
GROUP   BY CompanyId
HAVING  COUNT(*) = 2

OTHER LINK:

if vendorID is not unique for every CompanyId, a DISTINCT keyword is need to count only unique values,

SELECT  CompanyId
FROM    vendors
WHERE   VendorId IN ('306145244', '1011073437')
GROUP   BY CompanyId
HAVING  COUNT(DISTINCT VendorId) = 2

Upvotes: 2

Dipesh Parmar
Dipesh Parmar

Reputation: 27364

You might need to take a look at ALL.

SELECT CompanyId
FROM vendors
WHERE VendorId = ALL (
    query to get numbers.
)

http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

Upvotes: 0

Related Questions