How to use Select within select

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 2005 Forums

Express Edition and Compact Edition (2005)

How to use Select within select

Author

Topic

visweswaran28
Starting Member

4 Posts

Posted - 2010-05-12 : 03:22:14

Hi,
I have two tables. In which, I am going to select records from first table based on some condition and that resultant may have more than one record. Based on that resultant value I want to select from second table's value. How Can I use. Normally I worked with second inside select.(when inside select produce single value).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 03:25:47

Use derived table and JOIN the second table on that derived table.

If you need a better answer then please be more specific in your question (tables, sample data, wanted result).

No, you're never too old to Yak'n'Roll if you're too young to die.

visweswaran28
Starting Member

4 Posts

Posted - 2010-05-12 : 03:55:38

My First Query is "select user_id, visit_no from pj_trn_visits a, mst_po b
where a.revenuehead='pune' and
a.client_slno='CUMMINS INDIA LTD.,' and
a.service_slno='ISO 14001:2004 & OHSAS 18001:2007' and
a.client_slno=b.client_name and
b.client_location='pune' and
b.enquiry_type='P'"

and result will be:

user_id visit_no
-------------------------------------------------- -----------
SANGAMESH LINGABASAPPA KALASANNAVAR 36
SANGAMESH LINGABASAPPA KALASANNAVAR 37
SANGAMESH LINGABASAPPA KALASANNAVAR 38
SANGAMESH LINGABASAPPA KALASANNAVAR 39
GANAPATHY C 7
KAVITA KULKARNI 29
KAVITA KULKARNI 30
KAVITA KULKARNI 59

Second query is : "select cons_name, count( distinct visit_dates) from pj_trn_visits_cons
where user_id='GANAPATHY C' and visit_dt=21
group by cons_name"

In above query, I directly mentioned that user_id and visti_dt. Instead of this, values should be taken from that first query.

This is whole concepts.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-12 : 04:02:43

[code]
select cons_name, count( distinct visit_dates) as count_visit_dates
from pj_trn_visits_cons as ptv

join
(select user_id, visit_no
from pj_trn_visits a, mst_po b
where a.revenuehead='pune' and
a.client_slno='CUMMINS INDIA LTD.,' and
a.service_slno='ISO 14001:2004 & OHSAS 18001:2007' and
a.client_slno=b.client_name and
b.client_location='pune' and
b.enquiry_type='P') as dt
on dt.user_id=ptv.user_id and dt.visit_no=ptv.visit_dt

group by cons_name
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources