470,848 Members | 1,189 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,848 developers. It's quick & easy.

AND query in single column


Hi group
I have a rather peculiar question, and I really don't know how to solve
this within an SQL statement:

Given a view (v), that results in:

ID X
-----------------
1 a
1 b
2 a
2 c
3 a

I'd like to query the view with something like:
SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

ID
-----------------
1

or in another case:
SELECT ID FROM v WHERE (X='a' OR X='c')
would give:

ID
-----------------
1
2
3

how can this be done?

TIA
bernhard

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #1
14 1458
Bernhard Sturm (st**********@datacomm.ch) writes:
I have a rather peculiar question, and I really don't know how to solve
this within an SQL statement:

Given a view (v), that results in:

ID X
-----------------
1 a
1 b
2 a
2 c
3 a

I'd like to query the view with something like:
SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:
SELECT ID FROM v WHERE X = 'a'
INTERSECT
SELECT ID FROM v WHERE X = 'b'

Except that that syntax only works in SQL 2005. For SQL 2000, you
could do:

SELECT ID FROM v a
WHERE X = 'a'
AND EXISTS (SELECT *
FROM v b
WHERE a.ID = b.ID
AND b.X = 'b')

ID
-----------------
1

or in another case:
SELECT ID FROM v WHERE (X='a' OR X='c')
would give:


SELECT ID FROM v WHERE X = 'a'
UNION
SELECT ID FROM v WHERE X = 'b'

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Assuming the (id, x) is unique. The following gives the first result
you asked for:

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

This operation is called Relational Division and you can find other
examples and discussion about it under that name.

Your second "OR" query:

SELECT id
FROM v
WHERE x IN ('A','C')
GROUP BY id

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3
David Portas wrote:

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

This operation is called Relational Division and you can find other
examples and discussion about it under that name.

Your second "OR" query:

SELECT id
FROM v
WHERE x IN ('A','C')
GROUP BY id


thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

This can easily adopted to the 'or' case:

SELECT id
FROM v
WHERE x='a' OR EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

What do you think?

bernhard

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #4
Bernhard Sturm (st**********@datacomm.ch) writes:
thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id
This does not look good. If you have:

D X
-----------------
1 a
10 b
2 a
2 c
3 a

you will get back 1, 2 and 3. You need to correlate the subquery with
the main query. Because the subquery is not correlated to the main
query, the EXISTS is just binary flip-flop.
This can easily adopted to the 'or' case:

SELECT id
FROM v
WHERE x='a' OR EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id


If there is one row with x = 'b', you will get back all id:s. Again,
this is becaues the subquery is not correlated.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Erland Sommarskog wrote:

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

This does not look good. If you have:

D X
-----------------
1 a
10 b
2 a
2 c
3 a

you will get back 1, 2 and 3. You need to correlate the subquery with
the main query. Because the subquery is not correlated to the main
query, the EXISTS is just binary flip-flop.


true.. I just realised it as well... maybe I give your solution a try as
well. The thing is: this is all part of an other query, and I am pulling
the queries together via an ASP script, so they need to be simple. but
maybe may way of doing it is not simple ;-)

this

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

cheers
bernhard
--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #6
David Portas wrote:
Assuming the (id, x) is unique. The following gives the first result
you asked for:

SELECT id
FROM v
WHERE x IN ('A','B')
can this be formulated using a LIKE '%a%' statement as well?
GROUP BY id
HAVING COUNT(*)=2

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #7
> doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

Are you going to tell us what the error is, or just let us take a wild
guess?

It works for me if I run this code:
CREATE TABLE v (id INTEGER, x CHAR(1), PRIMARY KEY (id,x))

INSERT INTO v SELECT 1,'A' UNION SELECT 1,'B'

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

Result:
(2 row(s) affected)

id
-----------
1

(1 row(s) affected)

So what did you do differently? Please post some code that will
actually reproduce the problem, tell us the error message and the
version, edition and service pack you are using.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #8
David Portas wrote:
doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

Are you going to tell us what the error is, or just let us take a wild
guess?


sorry.. my fault:
I used the expression
x IN (LIKE '%a%', LIKE '%b%') which did result in an error (incorrect
syntax error)

So what did you do differently? Please post some code that will
actually reproduce the problem, tell us the error message and the
version, edition and service pack you are using.


CODE:
SELECT id
FROM v
WHERE x IN (LIKE '%a%', LIKE'%b%')
GROUP BY id
HAVING COUNT(*)=2

Error:
Incorrect Syntax near the keyword 'LIKE'

SQL Server 8.0 MSDE S.Pack 3/S.Pack 3a

cheers
Bernhard

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #9
Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

But are you sure you want to use LIKE here? This is not the same as
what you originally asked for because LIKE will catch any x that
*contains* the character "A" or "B", not just x = "A" or x = "B". If
that's what you want then that will probably invalidate my assumption
about the key of your table and my query won't work as I intended. You
may have to replace COUNT(*) with COUNT(DISTINCT x).

Note that the best way to get help with your problem is to post DDL (a
CREATE TABLE statement, including keys and constraints), sample data (a
few INSERT statements) and your required results. That way we won't
have to keep guessing exactly what you mean.

See: http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--

Jul 23 '05 #10
David Portas wrote:
Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2
no, as my ID is unique I only want records 'sharing' the same ID AND
fullfilling the AND LIKE criteria.

ID X
-----------------
1 auvw
1 buvw
2 axyz
2 cxyz
3 aklm

your solution would result in:

ID
--
1
2
3

but I only want to get (in my AND-problem):

ID
--
1

But are you sure you want to use LIKE here? This is not the same as
what you originally asked for because LIKE will catch any x that
*contains* the character "A" or "B", not just x = "A" or x = "B".


Yes. This is correct. I have formulated the problem in a more
'simplified' way. My problem is far more 'complicated'. But I have
followed your suggestion about the relaional divison, and think I've
found a way to solve the problem (In the 'real world' I need to check
for 3 criteria):

SELECT id
FROM v AS FG1
WHERE x LIKE '%a%' AND EXISTS
(SELECT *
FROM v AS FG2
WHERE FG1.id = FG2.id AND FG2.x LIKE "%b%" AND EXISTS
(SELECT * FROM v AS FG3
WHERE FG1.id = FG2.id AND FG3.id = FG1.id AND FG3.x LIKE "%c%"))
As far as I understand it (and tested it), this seems to cover my 'AND'
problem.
--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #11
David Portas wrote:
Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

sorry.. I was too quick with my reply.. your solution works as well :-)
puhhh.. long way to learn for me.
The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
that?

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #12
> your solution would result in:
ID
--
1
2
3


I don't think you tested that out. If you try it I think you will find
my query gives the result you asked for. Anyway it looks like your
version will work too. It's just rather more typing :-)

--
David Portas
SQL Server MVP
--

Jul 23 '05 #13
David Portas wrote:
your solution would result in:


ID
--
1
2
3

I don't think you tested that out. If you try it I think you will find
my query gives the result you asked for. Anyway it looks like your
version will work too. It's just rather more typing :-)


mine was too complicated, yours is perfect for my needs :-) Thanks a
lot. Now I have to implement this into another query... but thanks: it
works!

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply
Jul 23 '05 #14
Bernhard Sturm (st**********@datacomm.ch) writes:
David Portas wrote:
Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

sorry.. I was too quick with my reply.. your solution works as well :-)
puhhh.. long way to learn for me.
The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
that?


HAVING is like WHERE, but it is applied after GROUP BY. And for this
reason you can use aggregates in HAVING.

If you run

SELECT id, cnt = COUNT(*)
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
-- HAVING COUNT(*)=2

You will get:

id cnt
-- ---
1 2
2 1
3 1

But you want the row with cnt = 2. Normally you do would do this with a
WHERE clause, but you cannot use COUNT(*) in a WHERE clause. Hence,
HAVING to the rescue.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Paradigm | last post: by
1 post views Thread by AP | last post: by
2 posts views Thread by Ray Holtz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.