correlated subquery - 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

correlated subquery

Author

Topic

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-24 : 17:58:59

Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.

My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-24 : 20:36:57

select t.title, t.title_id, sum(s.quantity) as qty.
from titles t, sales s
where t.title_id = s.title_id
group by t.title, t.title_id
order by 2 desc
This is how i did it any feed back would be helpful.Previously, i had some problem with the aggreate functions.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-25 : 07:55:40

Homework?

Peter Larsson
Helsingborg, Sweden

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-28 : 01:45:40

select *
from titles t
where exists (select * from sales s where s.title_id = t.title_id)

i want to use the correlated subquery, i need some additional help.I have trouble put things together. Since, this is my 5week in sql and first week on forum. Any feed back. I would appreciate.
Thankyou.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 03:01:35

[code]
select *,
qty = (select sum(quantity) from sales x where x.title_id = t.title_id)
from titles t
where exists (select * from sales s where s.title_id = t.title_id)
[/code]

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-28 : 16:23:33

select *,
qty = (select sum(qty) from sales x where x.title_id = t.title_id)
from titles t
where exists (select * from sales s where s.title_id = t.title_id)

Thankyou, is there another way of getting the same solution without using the exists

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 19:09:03

YES.
- LEFT JOIN
- IN

tadin
Yak Posting Veteran

63 Posts

Posted - 2007-03-29 : 15:31:19

can you show me how to use the left join and in at the same time with correlated query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-02 : 03:01:05