[solved] Trying for a faster SQL query

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

[solved] Trying for a faster SQL query

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-12-15 : 20:42:58

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-15 : 22:02:39

change inner join to left join.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 03:53:05

try this


SELECT top 7 a.id,a.link,a.town,a.fullmember,b.visitor 
FROM sites a 
left join watchlist b ON a.id = b.location where a.new <> 'o' and b.visitor= 'oap' 
order by id desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-17 : 07:31:52

[code]
SELECT top 7 a.id,a.link,a.town,a.fullmember,b.visitor
FROM sites a inner join watchlist b ON a.id = b.location
left join (select location,count(*) as cnt from watchlist where visitor = 'oap' group by location) b1
on b1.location = b.location
where a.new <> 'o'
and coalesce(b1.cnt,0)=0
order by id desc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-17 : 19:20:01

hi, can you try this?


SELECT	*
FROM	(
		SELECT TOP 7 
			a.id,
			a.link,
			a.town,
			a.fullmember
		FROM	sites a 
		WHERE	a.new <> 'o' 
		ORDER BY id DESC
		)top7
		LEFT JOIN (
		SELECT	visitor, 
				location
		FROM	watchlist
		WHERE	visitor = 'oap'
		)watchlist
		ON top7.id = watchlist.location

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-18 : 06:43:13

how would you get record for 10021 ? it has new columns value as o which is what you're tryingto exclude in your query using condition a.new <> 'o'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-12-18 : 08:29:33

You are correct, 10021 would not show.

Now if only we someone can get the sort order to work :)

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-12-18 : 20:36:09

Got it!

SELECT *
FROM (
SELECT TOP 7
a.id,
a.link,
a.town,
a.fullmember
FROM sites a
WHERE a.new <> 'o'
ORDER BY id desc
)top7
LEFT JOIN (
SELECT visitor,
location
FROM watchlist
WHERE visitor = 'xxx'
)watchlist
ON top7.id = watchlist.location order by id desc

Thanks to all :)

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 04:08:30

Why have you removed your original post? makes the whole thread pointless for anyone else looking at it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources