We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

sql query select on two criteria, same field?

Medium Priority
1,222 Views
Last Modified: 2013-12-07
I want an sql SELECT that will select on the same field
and will use two, separate criteria for that field.

The result should be the subset that satisfies BOTH criteria.

The example below does not work.

How do I do this?


select * from cam where cust='1' and cust='2'

Open in new window

Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
select * from cam where cust='1' or  cust='2'
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
you never can 1 = 2
Top Expert 2009

Commented:
You can also use IN()

where cust in('1', '2')

Author

Commented:
Thanks. Both where cust in('1', '2')  and  select * from cam where cust='1' or  cust='2'  are producing the WRONG result.

I want the intersection of the sets of results produced by these two queries:

1. select * from cam where cust='1'

2. select * from cam where cust='2'
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi pillmill,

The intersection will be a NULL set.  "cust" can be 1, or it can be 2, but it can't be both.


Kent

Author

Commented:
Thanks. Here are the records:

table cam:

cust     cama
1          a
1           b
2          a
2          c


I want the query to return the value "a" .  The value "a" is the one
which is the same between cust 1 and 2.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Hmm....  There are a lot of variables here.  Can 'a occur more than once per customer number?  Will it occur only twice?  Are more values than 1 and 2 possible for cust, etc.

Here's one solution:

SELECT cama, count() as c FROM mytable
GROUP BY cama
HAVING c > 1;


Kent

Commented:
Try:
SELECT cama FROM cam WHERE cust = 1
INTERSECT
SELECT cama FROM cam WHERE cust = 2;
 
or
 
SELECT cama c1
FROM cam WHERE cust = 1
WHERE EXISTS ( SELECT NULL
               FROM cam c2
               WHERE c1.cama = c2.cama
                 AND c1.cust <> c2.cust )
  

Open in new window

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I suspect your problem may be little more complex than that indicated by your example. Are you trying to find values that exist for more than one customer, that exist for every customer, or do you want entire records (or perhaps more than one field) where those instances occur? Perhaps you could post some more relevant sample data and what your expected output should be.

Author

Commented:
SELECT cama c1
FROM cam WHERE cust = 1
WHERE EXISTS ( SELECT NULL
               FROM cam c2
               WHERE c1.cama = c2.cama
                 AND c1.cust <> c2.cust )

produces a MySQL error:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE exists ( SELECT NULL

Author

Commented:
Record "a" is the common element.  

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Change "where exists" to "and exists".
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
select * from cam where
cama in (select cama from cam where cust='1' )
and
cama in (select cama from cam where cust='2' )

Author

Commented:
Thanks!
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Pill,

For the record, there was a typo in my SQL, but it looks like you figured that out.   :)

Kent
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.