RE: [SQLDownUnder] INNNER JOIN?

[Junying Xue]

Hi Will,

I assume you want to find items that do not exist in the Products table.  If
so, how about the following:
	select ItemID 
	from Items
	where not exists (select Products.FK_ItemID 
				from Products
				where Items.ItemID = Products.FK_ItemID)

Cheers,
June

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of William Luu
Sent: Wednesday, 9 March 2005 11:23 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: Re: [SQLDownUnder] INNNER JOIN?

Jarrad:

well, just doing the 

select * from Items

returns 379 rows.

and executing the tsql query as it was before returned 2 rows.

Now, those two rows were the two rows that I was originally after.

And trying to execute the subquery by itself using SQL Query Analyser
produces the following error, twice.

"The column prefix 'Items' does not match with a table name or alias
name used in the query"

So, I fixed it into a proper query after reading Damian's reply about the
alias.

Thus producing:

select ItemID from Items
where not exists
(select ItemID from Items AS N INNER JOIN Products
ON Items.ItemID = Products.FK_ItemID)

And that above looks correct and works...


Will

On Wed, 9 Mar 2005 11:12:07 +1100, jmplun <jmplun@xxxxxxxxxxxxxx> wrote:
> Maybe because the typo meant no results were returned in the exists
> query, so all rows where returned from Items table?
> 
> Jarrad
> 
> -----Original Message-----
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of William
> Luu
> Sent: Wednesday, 9 March 2005 11:05 AM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [SQLDownUnder] INNNER JOIN?
> 
> Alright, I was looking through one of my stored procedures and see this
> following query:
> 
> select ItemID from Items
> where not exists
> (select ItemID from Items INNNER JOIN Products
> ON Items.ItemID = Products.FK_ItemID)
> 
> I want to know why my typo (adding an extra 'N' in the INNER word),
> actually yielded results, but when I corrected my typo, it didn't yield
> any results at all.
> 
> I expect that it's probably something simple like using the wrong type
> of join in the first place.
> 
> Cheers,
> 
> William
> 
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed
> by http://www.readify.net
> 
> to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net
> 
>


to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net




to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net