473,324 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 1514
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
2
by: Stan | last post by:
Hello all, I'm looking for some hints as to how to use the results of a query in another query. I assume I'm 'thinking' wrong in how to solve this, so I'm hoping someone can clobber me and send...
3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
5
by: Rated R1 | last post by:
I wrote this before in the NGs, so I am going to paste the responses that I got and see if someone can please help me. Email me and we can set something up as Id even be willing to pay for your...
1
by: AP | last post by:
We commonly use this method to "pivot/crosstab" table results. For example lets say there are two offices, and we want the total sales for each ofice in its own column(Next to each other). We do...
2
by: Ray Holtz | last post by:
I have a form that shows a single record based on a query criteria. When I click a button it is set to use an append query to copy that record to a separate table, then deletes the record from the...
5
by: teddysnips | last post by:
I have to write an application to do some data cleansing. It's a Contact database, but over a number of years there are multiple companies which are all essentially the same entity. For each...
0
by: RCapps | last post by:
When running the below SQL Query I keep getting the following error: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table...
1
by: pmouseca | last post by:
Hello all, I have two tables, one call Assignments, the other call Grades. In Assignment, the columns are: Asn_ID, Asn_Name, Asn_MaxGrade In Grades, the columns are: Grade_ID,...
25
by: Darsin | last post by:
Hi all I need to perform a summation on a column of a table based on a criteria given in another column in the same table. The catch is i need to perform different sums according to the number of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.