I fell like an idiot...

Thread: I fell like an idiot...

1. Senior Member
Join Date
Dec 1969
Posts
655

I fell like an idiot...

...because this is probally staring me right in the face but could someone please enlighten me to why these two queries both return 0 ressults?<BR><BR>SELECT * FROM tbl1 WHERE id = 1 AND id IN (SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039;)<BR><BR>SELECT * FROM tbl1 WHERE id = 1 AND id NOT IN (SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039;)

2. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: I fell like an idiot...

one of a few things!<BR><BR>1) id #1 does not exist<BR>2) id #1 in table2 does not have obolete with a value of "no"<BR><BR>what does the SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039; return ?<BR><BR>Does it have id 1?<BR> the following makes more sense<BR><BR>SELECT * FROM tbl1 WHERE id IN (SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039;)<BR><BR>SELECT * FROM tbl1 WHERE id NOT IN (SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039;)

3. Senior Member
Join Date
Dec 1969
Posts
11,247

And you Fell too!

You never mention your fall?<BR><BR>What happened? Chair to high?

4. Senior Member
Join Date
Dec 1969
Posts
655

RE: I fell like an idiot...

The query:<BR>SELECT * FROM tbl1 WHERE id = 1<BR>will return one result<BR>The query:<BR>SELECT id FROM tbl2 WHERE id = 1 <BR>will return 0 results

5. Senior Member
Join Date
Dec 1969
Posts
655

:-p

I never said I knew how to spell

6. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: :-p

I Kant Spill eva

7. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: I fell like an idiot...

How&#039;s about explaining in words what you want from both the queries?

8. Senior Member
Join Date
Dec 1969
Posts
655

RE: I fell(sp) like an idiot...

I actually only want the first query to work. What I need it to get the list of IDs from the first table which do not have records in the second table with an obsolete = &#039;no&#039; <BR><BR>Both those queries are returning the same result even though I know that there are definelty ids from the first table which do not exist in the second.

9. Senior Member
Join Date
Dec 1969
Posts
11,247

RE: I fell(sp) like an idiot...

SELECT * FROM tbl1 WHERE id NOT IN (SELECT DISTINCT id FROM tbl2 WHERE obsolete = &#039;no&#039;) <BR><BR>That is the one you want! Copy it exactly as is and tell me I&#039;m the saviour....<BR><BR><BR>OK you don&#039;t HAVE to do the last bit (you can if you want)<BR><BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
655

Ya so...

... that&#039;s what I thought the query should be right. But I have an id FROM table one that I can verify that it is in table one and when I just the IN query by is self with and ORDER BY clause on it I can visually verify its not in the results.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•