query multiple tables - newbie

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)

query multiple tables - newbie

Author

Topic

havey
Starting Member

16 Posts

Posted - 2007-11-30 : 00:04:14

Hi i would like to query multiple tables for the top1 record, this is what i have created so far, but its not working, any suggestions:

CREATE PROCEDURE dbo.sp_getMCpage1
(
@MemberID int
) 
AS
SET NOCOUNT ON

Select TOP 1 * from MCPage1,  MCPage1a,  MCPage1b,  MCPage1c,  MCPage1d,  MCPage1e,  MCPage1f

where MCPage1.MemberID=@MemberID

RETURN
GO

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-30 : 00:07:59

quote:
from MCPage1, MCPage1a, MCPage1b, MCPage1c, MCPage1d, MCPage1e, MCPage1f

where is the JOIN condition for these tables ?

What do you want to do here ?

KH
[spoiler]Time is always against us[/spoiler]

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-30 : 00:08:38

select top 1 * from MCPage1
union all
select top 1 * from MCPage1a
...
etc

Top 1 does not mean anyting without an order by on something unique so you'd want something like

select * from (select top 1 * from MCPage1 order by x)
union all
select * from (select top 1 * from MCPage1a order by x)

Sounds like a very dodgy db design to me though.

havey
Starting Member

16 Posts

Posted - 2007-11-30 : 00:14:33

ok i'm a little lost so excuse me, there are the aformentioned tables, they all have info for a particular MemberID, i would like sql to grab all the info in the tables in regards to that member id.

I thought using Top 1 ment that when sql found the record that it would stop scanning the table looking for another record with the same id, am i wrong? just thought it was more effecient, and also thought that didnt need to order by with top 1, am i wrong again?

Thanks

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-30 : 02:29:37

Also, it is not recommended to call procedures sp_ .

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-30 : 03:29:41

quote:
Originally posted by havey

ok i'm a little lost so excuse me, there are the aformentioned tables, they all have info for a particular MemberID, i would like sql to grab all the info in the tables in regards to that member id.

I thought using Top 1 ment that when sql found the record that it would stop scanning the table looking for another record with the same id, am i wrong? just thought it was more effecient, and also thought that didnt need to order by with top 1, am i wrong again?

Thanks

Do you want one row for each id for each table?

select t.columns from
(
Select columns,row_number() over(partition by id order by id) as rownum from table
) as t
where rownum=1

You can apply the same in all tables

Madhivanan

Failing to plan is Planning to fail

havey
Starting Member

16 Posts

Posted - 2007-11-30 : 11:52:10

quote:
Do you want one row for each id for each table?

madhivanan thanks, yes that correct, i would like 1 record from each of the tables where MemberID=89.

There is a form on a webpage and the tables for the long form is seperated by required fields and nonRequired fields, questions used for 'heavy' statistical mining, and questions/answers used by another application, etc... So now i just need to bring ALL the data from the tables back into the form based on MemberID=X

I'm a little lost by this method, can someone please break it bown for me so i can learn, thanks

error: 'row_number' is not a recognized function name.
Maybe this a sql2005 function, i'm on 2k, sry for not mentioning that

select t.columns from
(
Select columns,row_number() over(partition by id order by id) as rownum from table
) as t
where rownum=1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-01 : 02:03:22

You posted this questionin 2005 forum
Try point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources