*Solved* Bah! Having problems with a simple SQL query

Mucman

Diamond Member
Oct 10, 1999
7,246
1
0
I have no idea why I am having problems with this query. I got no problems with multiple outer joins mixed with inner joins and aliases table queries, but this has got me stumped. Basically I want to return all the rows from one table that do not exist in another. The tables are quite simple... Both tables have 1 column each. Table A is a table of ids, and Table B is actually a view that consolidates a lot of data and simplifies the presentation.

For example:
Table A
1
2
3
4
5
6

Table B
2
4
5
6

I want my qeury to return {1, 3}.

When I do:

SELECT *
FROM A
WHERE A.id NOT IN (SELECT id FROM B)

The results are all elements of Table A.

The RDBMS I am using is MS SQL Server 7.

Resolution

I'm an idiot and forgot to chomp() the variables that are put into table A by a perl script. None of the fields would match because of the newline character. Sorry for being stupid :)
 

MrChad

Lifer
Aug 22, 2001
13,507
3
81
SELECT A.*
FROM A
WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id)

... although your query should work too :confused:
 

Mucman

Diamond Member
Oct 10, 1999
7,246
1
0
Yeah, I tried that too... does the same thing :(

Could this be a PRIMARY KEY problem somehow? A.id is a PK but B.id is not.
 

Mucman

Diamond Member
Oct 10, 1999
7,246
1
0
The results are everything that's in table A.

I just created some test DB's and I can see that both of our queries are correct. The problem lies within the schema somehow. The Query Execution plan looks a bit funny too.

This works:

CREATE TABLE A (
id INT
)
GO
CREATE TABLE B (
id INT
)

INSERT INTO A VALUES ('1')
INSERT INTO A VALUES ('2')
INSERT INTO A VALUES ('3')
INSERT INTO A VALUES ('4')
INSERT INTO A VALUES ('5')
INSERT INTO A VALUES ('6')

INSERT INTO B VALUES ('2')
INSERT INTO B VALUES ('4')
INSERT INTO B VALUES ('5')
INSERT INTO B VALUES ('6')

SELECT *
FROM A
WHERE A.id NOT IN (SELECT id FROM B)

My view looks something like this:

CREATE VIEW B AS (
SELECT SIR.id
FROM S INNER JOIN SIR
ON S.pk = SIR.fk
WHERE S.Status = 'A'
AND SIR.Code = 'ABC'
)
 

KLin

Lifer
Feb 29, 2000
29,988
409
126
DOH didn't attach code anyways, here:


SELECT [TABLE A].* FROM [TABLE A] LEFT JOIN [TABLE B] ON [TABLE A].ID = [TABLE B].ID
WHERE
[TABLE B].ID Is Null
 

Mucman

Diamond Member
Oct 10, 1999
7,246
1
0
Originally posted by: KLin
DOH didn't attach code anyways, here:


SELECT * FROM TABLE A LEFT JOIN TABLE B ON TABLE A.ID = TABLE B.ID
WHERE
TABLE B.ID Is Null

I've tried that too... produces the same results as the first queries. Instead of using a view, I'm going to try storing the results of that query in a temporary table.