subquery i suppose - 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

subquery i suppose

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-20 : 23:13:00

hey guys,
trying to program something and i am using sql to run a query in my code.
I have done the query where it returns a top(1) line i want. I now want to extract just the id out of that top line and run a query on that id

for example

my query looks like this


select top(1)
p.personid, p.personfirstname, p.persondob
from people p

join school as s
on p.schoolid = s.schoolid

where personethnicity like 'greek'
and
s.schoolname like 'thessalon%'
and

this returns one line

i now want to see how much that person has paid for his school fees from the fee table. But can not join it in the same sql query.

is there a simple way to say
where the id in the top line = the sql query above?

i cant hardcode the values

cheers guys!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-21 : 01:43:39

I don't see a reason why you can't join another table in the same query.

And your top(1) makes no sense if you want to see specific information.

If you need more help then please provide
table structure,
sample data,
wanted output in relation to sample data.

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

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-21 : 03:03:25

i have used a count in the intial query and want to use a sum in the later query.

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-21 : 03:06:57

[code]
select top(1)
p.personid, p.personfirstname, p.persondob, COUNT(p.personschool) as 'counted'
from people p

join school as s
on p.schoolid = s.schoolid

group by p.personid, p.persondob

where personethnicity like 'greek'
and
s.schoolname like 'thessalon%'

order by 'counted' desc
[/code]

now i want to use the above result which returns the top counted schools a student has been to

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-21 : 03:07:29

quote:
If you need more help then please provide
table structure,
sample data,
wanted output in relation to sample data.

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

pavlos
Yak Posting Veteran

59 Posts

Posted - 2010-05-21 : 04:39:48

best for me to start a new topic when i get home and have all info
thanks freind!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2010-05-21 : 04:50:15

Pavlos,

Just a guess, are you trying to see this:
select max_id.personid, sum(f.fees_paid)
from
(
Select max(p.personid) as personid
from people p
join school s on p.schoolid=s.schoolid
where personethnicity like 'greek' and s.schoolname like 'thessalon%') max_id
join fee f on f.personid=max_id.personid

Group by max_id.personid

I don't know your table structure, so I'm guessing at your column names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources