No nested queries allowed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

No nested queries allowed

Author

Topic

Smulzie
Starting Member

3 Posts

Posted - 2007-01-29 : 17:38:29

Hi all,

I need to rewrite the following query without a nested query (stupid mysql 4.0.25. Can anyone lend a hand?

SELECT name from `list` where name not in (
SELECT DISTINCT b.name from ` armor ` a,` list` b where b.name = a.name
);

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-29 : 17:43:25

Try this:

SELECT l.name
FROM list l
LEFT OUTER JOIN armor a
ON l.name = a.name
WHERE a.name IS NULL

This is a SQL Server site, so answers will be valid T-SQL queries.

Tara Kizer

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-29 : 17:46:43

This is a SQL Server forum, you should rather go to http://forums.mysql.com/ which are MySql forums

For your query however, this should do it

SELECT DISTINCT l.name
FROM `list` l
LEFT OUTER JOIN ` armor ` a ON a.name = l.name
WHERE a.name IS NULL

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-29 : 17:47:04

Smulzie
Starting Member

3 Posts

Posted - 2007-01-29 : 17:47:09

That did the trick! Thank you, I didn't even know about the existence of left outer join.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources