RE: [SQLDownUnder] INNNER JOIN?

[Victor . Isakov]

Another option is:

	SELECT ItemID 
	FROM	 Items LEFT JOIN Products
	ON	 Items.ItemID = Products.FK_ItemID
	WHERE  Products.FK_ItemID IS NULL

Don't forget that generally JOINS work quicker than sub-queries in SQL
7/2000.

Cheers,

Victor

-----Original Message-----
From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
[mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Junying
Xue
Sent: Wednesday,9 March 2005 11:34 AM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: RE: [SQLDownUnder] INNNER JOIN?

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






This message and any attachment is confidential and may be privileged or otherwise protected from disclosure.  If you have received it by mistake please let us know by reply and then delete it from your system; you should not copy the message or disclose its contents to anyone.

==============================================================================



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