Multiple ID check in MYSQL WHERE condition

Question

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.

VendorId = '306145244' and VendorId = '1011073437'? Table structure please! — AllTooSir, Apr 25 '13 at 05:44
I assume there are 2 rows with the same companyID and different vendorID? — Osiris, Apr 25 '13 at 05:44

score 2 · Answer 1 · answered Apr 25 '13 at 05:46

2

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

SQLFiddle Demo

OTHER LINK:

SQL of Relational Division

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

answered Apr 25 '13 at 05:46

John Woo

258,903
69
498
492

I have edited the question. Please check it. Your query will give only one row for vendors. I need 2 rows for ('306145244', '1011073437') – Sahal Apr 25 '13 at 06:40

score 0 · Answer 2 · answered Apr 25 '13 at 05:46

0

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

answered Apr 25 '13 at 05:46

Dipesh Parmar

27,090
8
61
90

score 0 · Answer 3 · answered Apr 25 '13 at 05:52

0

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.

answered Apr 25 '13 at 05:52

Code Lღver

15,573
16
56
75

I have edited the question. Please check it. Your query will give only one row for vendors. I need 2 rows for ('306145244', '1011073437') – Sahal Apr 25 '13 at 06:41
In this case I will get only one record for company, I need 2 record see the question – Sahal Apr 25 '13 at 06:51

score 0 · Answer 4 · edited May 23 '17 at 12:04

0

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

edited May 23 '17 at 12:04

Community

1
1

answered Apr 25 '13 at 06:08

Prahalad Gaggar

11,389
16
53
71

I have edited the question. Please check it. Your query will give only one row for vendors. I need 2 rows for ('306145244', '1011073437') – Sahal Apr 25 '13 at 06:41
@Sahal Answer Edited. **:)**. It will worked in **Oracle** too – Prahalad Gaggar Apr 25 '13 at 06:49

score 0 · Answer 5 · answered Apr 25 '13 at 06:49

0

Try this :

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

Hope it will help you.

answered Apr 25 '13 at 06:49

JDGuide

6,239
12
46
64

Multiple ID check in MYSQL WHERE condition

5 Answers5