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:
IDX
------
1a
1b
2a
2c
3a
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 replyBernhard Sturm (sturmnixspam@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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
--|||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|||Bernhard Sturm (sturmnixspam@.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:
DX
------
1a
10b
2a
2c
3a
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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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:
> DX
> ------
> 1a
> 10b
> 2a
> 2c
> 3a
> 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|||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|||> 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
--|||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|||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
--|||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|||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|||> 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
--|||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|||Bernhard Sturm (sturmnixspam@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
No comments:
Post a Comment