Getting data from multiple tables

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

Getting data from multiple tables

Author

Topic

Tototo1
Starting Member

22 Posts

Posted - 2012-02-22 : 14:36:45

Hello,

I am trying to comeplete a table that will pull in people based on a timekeeper number and get back how many hours, cost and fees they are billing. I can get two queries to get me my information but I cannot figure out how to get it all in one place. My first query is:

select ctk, sum(camount) CSUM
from cost
where cmatter = '0998457-0001'
group by ctk
order by ctk

This will return ctk(person) and camount(amount of cost) that worked on a specific client(cmatter)

I also want to grab what they billed for fees:

select ttk, sum(tworkrt * tworkhrs) TSUM
from timecard
where tmatter = '0998457-0001'
group by ttk
order by ttk

ttk and ctk are essentially the same, just on the two different tables timecard and cost. Is there an easy way to get this information to come up to show each person, the amount of fees and the amount of costs they worked for on the specific tmatter/cmatter? Whenever I try to incorporate both tables it breaks. I have not touch the hours portion of this but I feel if I get some quidance on how to get these two queries to work together I should be able to figure it out.

Thanks!

Tototo1
Starting Member

22 Posts

Posted - 2012-02-22 : 16:14:27

I can get this data to kinda work with a temp table

select ctk, sum(camount) CSUM
into #eee
from cost
where cmatter = '0998457-0001'
group by ctk
order by ctk

select ttk, sum(tworkrt * tworkhrs) TSUM
into #fff
from timecard
where tmatter = '0998457-0001'
group by ttk
order by ttk

select ttk,tsum, ctk, csum
from #eee
join #fff
on ctk = ttk

I do not want to use a temp table. Any suggestions?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 17:25:00

See if this will work for you. Just be aware that I am making some assumptions - for example, that there will not be any ctk/ttk that exists in one table but not the other (if that is the case, you will need to use a full join).

SELECT
	c.ctk,
	sum(c.camount) CSUM,
	sum(t.tworkrt * t.tworkhrs) TSUM
FROM
	cost c
	INNER JOIN timecard t ON t.ttk = c.ctk
WHERE
	c.cmatter = '0998457-0001'
	AND tmatter = '0998457-0001'
ORDER BY
	c.ctk;

Tototo1
Starting Member

22 Posts

Posted - 2012-02-23 : 08:44:43

Thank you for that query, although the numbers that I get are far higher than they should be. Not sure how to fix that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 08:57:00

quote:
Originally posted by Tototo1

Thank you for that query, although the numbers that I get are far higher than they should be. Not sure how to fix that.

That means the relationship between tables might be 1 to n . Probably you can provide some sample data to give us more idea on relationship so that we can provide accurate solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources