Return one-to-many in one query

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)

Return one-to-many in one query

Author

Topic

pcdj
Starting Member

5 Posts

Posted - 2009-09-16 : 16:39:26

I currently have a one to many relationship between table a and table b.

In my Perl script I query for all the rows of table a that I require, and for each row returned I select all columns from table b that have the same foreign key as the primary key of table a. If the first query against table a returns three rows, I perform three queries against table b. (There are other tables relating to both tables a and b for other relational data but these have been omitted for simplicity).

Is there any way to do this in one query?

Please excuse me if the question is a bit too generic, I can oviously provide a schema on request but I'm thinking this might be simpler than I think!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-09-16 : 17:32:47

Yes, it can be done easily enough using a simple inner join:

SELECT {list of columns from tablea and tableb}
FROM tablea a
INNER JOIN tableb b ON b.keycolumn = a.keycolumn
WHERE {where criteria};

That should be it.

pcdj
Starting Member

5 Posts

Posted - 2009-09-18 : 02:04:13

Thank you for your response.

How about something a bit more complex - for each row returned by the query you outlined above (so tablea.id will appear multiple times, but tableb.id will appear once; and it is tableb.id I wish to run the SP against), run a stored procedure against the primary key of table b, ideally returning the results in their own column at the end of each of the existing rows.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-18 : 02:19:51

It doesnt matter whether you use table a.id or table b.id as the parameter to your sp, You will get the same result as you are using an eq.join. Basicall you will get the same data on both the queries

SELECT {list of columns from tablea and tableb}
FROM tablea a
INNER JOIN tableb b ON b.keycolumn = a.keycolumn
WHERE a.id ={criteria}

SELECT {list of columns from tablea and tableb}
FROM tablea a
INNER JOIN tableb b ON b.keycolumn = a.keycolumn
WHERE b.id ={criteria}

pcdj
Starting Member

5 Posts

Posted - 2009-09-18 : 03:13:18

Well I won't get the same result as the set of data comprising the a.id primary keys will be entirely different from the set of data comprising the b.id primary keys. The stored procedure also won't work as the columns in table a are entirely different from the columns in table b, so I don't understand where you're coming from?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-18 : 03:44:04

To make it more clear you can give example tables with example Columns and example data with wanted output.
And your first post was nothing about SP.
So what you're trying to do?

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

pcdj
Starting Member

5 Posts

Posted - 2009-09-18 : 03:48:25

Yes I realise my first reponse didn't mention an SP; my second query was more of a follow up and I didn't want to clutter the board by starting a new thread.

I have table a:
ID
Date
Event
Description

and table b:
ID
Action
Action Due Date
EventID (foreign key)

The table a to table b relationship is one to many.

Keeping in mind the reply to my first post, which will return a set of rows from table a along with all corresponding data from table b I would now like to execute an SP against every row for the values in column tableb.id which will all be unique.

As an example of the output:

a.ID	a.date	a.event	a.description	b.id	b.action	b.action_due_date	b.eventID	SP_result
1	12/2/09	A	Ab		1	Abc		12/2/09			1		?
1	12/2/09	A	Ab		2	Bcd		13/2/09			1		?
2	14/2/09	B	Bc		3	Cde		15/2/09			2		?
3	15/2/09	C	Cd		4	Def		16/2/09			3		?
3	15/2/09	C	Cd		5	Efg		16/2/09			3		?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources