pillmill
asked on
sql query select on two criteria, same field?
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?
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'
select * from cam where cust='1' or cust='2'
you never can 1 = 2
You can also use IN()
where cust in('1', '2')
where cust in('1', '2')
ASKER
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'
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'
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
The intersection will be a NULL set. "cust" can be 1, or it can be 2, but it can't be both.
Kent
ASKER
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.
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.
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
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 )
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.
ASKER
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
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
ASKER
Record "a" is the common element.
Change "where exists" to "and exists".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select * from cam where
cama in (select cama from cam where cust='1' )
and
cama in (select cama from cam where cust='2' )
cama in (select cama from cam where cust='1' )
and
cama in (select cama from cam where cust='2' )
ASKER
Thanks!
Hi Pill,
For the record, there was a typo in my SQL, but it looks like you figured that out. :)
Kent
For the record, there was a typo in my SQL, but it looks like you figured that out. :)
Kent