joins and subqueries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

joins and subqueries

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2005-07-23 : 08:43:58

Hi friends,

Referring to the pubs database in sql server
this query refers to the titles that received an advance larger than the minimum advance amount paid by algodata Infosystems.How to change the query to a join???

select title
from titles
where advance > any
(select advance from publishers inner join titles
on titles.pub_id = publishers.pub_id
and pub_name = 'Algodata Systems')

Vicky

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-24 : 07:06:48

Here are some alternatives...

-- pure base table JOIN
select 	distinct t.title
from 	titles t
	join titles t2
		join publishers p
		on t2.pub_id = p.pub_id
		and p.pub_name = 'Algodata Infosystems'
	on t.advance > t2.advance

-- EXISTS clause
select 	t.title
from 	titles t
where 	exists( select * from titles t2 join publishers p
		on t2.pub_id = p.pub_id
		and p.pub_name = 'Algodata Infosystems'
		where t.advance > t2.advance )

-- derived table JOIN
select 	t.title
from 	titles t
	join(	select min(t.advance) as minadvance
		from publishers p inner join titles t
		on t.pub_id = p.pub_id
		and p.pub_name = 'Algodata Infosystems' ) tmin
	on t.advance > tmin.minadvance -- or tmin.minadvance is null /* use OR, in case there is no pub with that name */


-- subquery
select 	title
from 	titles
where	advance > (	select min(t.advance) as minadvance
			from publishers p inner join titles t
			on t.pub_id = p.pub_id
			and p.pub_name = 'Algodata Infosystems' )

-- NOT EXISTS clause
select 	t.title
from 	titles t
where 	not exists( 	select * from titles t2 join publishers p
			on t2.pub_id = p.pub_id
			and p.pub_name = 'Algodata Infosystems'
			having t.advance <= min(t2.advance) )
and 	advance is not null

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources