CODEWITHSUNDEEP
mysql
andy
andy

Reputation: 2399

Where distinct ID has two values

I apologize for the weird title, I really can't think of a way to describe this. I'm just having a complete mental block. Basically, the table layout looks like so:

id, customer_id, company, date, (... other irrelevant fields)
1   1            CompanyA    02/08/1111
2   1            CompanyB    02/08/1111
3   1            CompanyC    02/08/1111

I basically want to select all the customer ID's that have records in that table for both CompanyA and CompanyB

How would I go about this?

Upvotes: 0

Views: 76

Answers (2)

Taryn
Taryn

Reputation: 247730

To return all company_ids that have both CompanyA and CompanyB then you should be able to use a GROUP BY with a HAVING clause to get the result:

select customer_id
from yourtable
where company in ('CompanyA', 'CompanyB')
group by customer_id
having count(distinct company) >= 2;

See SQL Fiddle with Demo.

If you just want to return any company_ids with either CompanyA or CompanyB then you can use an IN:

select customer_id
from yourtable
where company in ('CompanyA', 'CompanyB')

Upvotes: 5

andy
andy

Reputation: 2399

I figured it out just as I posted this. I've been writing complicated queries all day and I just couldn't get it. It literally is just an OR query.

Upvotes: 0

Related Questions