Get Data from 2 Tables in 1 select statement

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

Transact-SQL (2005)

Get Data from 2 Tables in 1 select statement

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-12-25 : 04:41:30

Hi all,
and merry christmas!

I have 2 tables. They both have a UserID and can have multiple records for each user. Now I want to have a SELECT-Statement which combines the latest data of both of these tables. Here is an example:

TableA:


UserID | Sold | Margin | Date
1      |  17  |    9   | 24.12.2008
2      |  19  |   12   | 24.12.2008
1      |  12  |    7   | 23.12.2008
1      |  22  |   15   | 22.12.2008

TableB:


UserID | Bought | Average | Date
1      |    22  |    14   | 17.12.2008
2      |    17  |    11   | 11.12.2008
1      |    20  |    13   | 09.12.2008
1      |    15  |     9   | 30.11.2008

Doing 1 select for 1 table is not a problem:
SELECT TOP 1 Sold, Margin FROM TableA Where UserID = 1 ORDER BY Date DESC
or
SELECT TOP 1 Bought, Average FROM TAbleB Where UserID = 1 ORDER BY Date DESC

But I want to create a stored procedure which only returns 1 row combining both tables. The result should be like this when I call the stored Procedure for UserID = 1:

Sold | Margin | Bought | Average
17   |   9    |   22   | 14

Is this possible? I've read Bills article regarding UNION at [url]http://www.sqlteam.com/article/union-selecting-from-multiple-tables-in-one-statement[/url] but this would return 2 rows, I'd like to do this in one if possible.

Many thanks!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 04:44:47

select top 1 a.sold,a.margin,b.bought,b.average
from tablea a
inner join
tableb b on a.userid = b.userid

as
stored procedure

CREATE PROC dbo.usp_storedproc
(
@userid int
)
AS
SET NOCOUNT ON
BEGIN

select top 1 a.sold,a.margin,b.bought,b.average
from table1a a
inner join
table1b b on a.userid = b.userid
where a.userid = @userid
END
SET NOCOUNT OFF

exec usp_storedproc 1

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-12-25 : 05:01:28

Hi bklr,

yes, I had something similar already, but in this case the SELECT does not return the LATEST record for the user because there is no ORDER BY statement. I just tried it again, and it just returns any row from Table B (maybe the first row which was created for user 1?). So how could I change the SELECT so that from both tables the LATEST records are retrieved?

Many thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 05:09:52

quote:
Originally posted by Heinz23

Hi bklr,

yes, I had something similar already, but in this case the SELECT does not return the LATEST record for the user because there is no ORDER BY statement. I just tried it again, and it just returns any row from Table B (maybe the first row which was created for user 1?). So how could I change the SELECT so that from both tables the LATEST records are retrieved?

Many thanks

I think given query is giving the latest record from both tables
once check it out
o/p as
17 9 22 14

u can use it by order by also

select top 1 a.sold,a.margin,b.bought,b.average
from table1a a
inner join
table1b b on a.userid = b.userid
where a.userid = 1
order by a.date desc ,b.date desc

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-12-25 : 05:15:06

Hi bklr,

brillant, this is working fine now using the ORDER BY!

Many thanks and merry xmas and a happy new 2009!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 05:22:17

quote:
Originally posted by Heinz23

Hi bklr,

brillant, this is working fine now using the ORDER BY!

Many thanks and merry xmas and a happy new 2009!

ur welcome

wish u the same..........

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-25 : 06:56:51

select z.sold,z.margin,z.bought,z.average from
(select max(tablea.date) as max1,max(tableb.date) as max2
from tablea
inner join tableb on (tablea.userid = tableb.userid)
where tablea.userid = 1) t
cross apply (select t.sold,t.margin,t1.bought,t1.average from tablea t inner join tableb t1 on t.userid = t1.userid and t.userid = 1 and t.date = max1 and t1.date=max2) z

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources