Get me records and its subrecords seperately

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

Get me records and its subrecords seperately

Author

Topic

tech
Starting Member

32 Posts

Posted - 2012-05-24 : 11:31:04

Hi.

I have a table which has 3 nullable FK's but only one of them can be used.
I want to write a query where I pass in the "OrderID" and it returns me that record but also any records which have a value in any one of the 3 FK's for that row, and return each of those records in a seperate recordset

any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 12:56:48

[code]CREATE PROCEDURE GetOrder @OrderID int AS
SET NOCOUNT ON;
SELECT * FROM myTable WHERE FK1=@OrderID;
SELECT * FROM myTable WHERE FK2=@OrderID;
SELECT * FROM myTable WHERE FK3=@OrderID;
GO
-- example:
EXEC GetOrder 10;[/code]

tech
Starting Member

32 Posts

Posted - 2012-05-24 : 13:08:17

Thanks. kind of....

the OrderID is for the "primary" table. but the FK tables dont work based upon the OrderID but only the value stored in the FK column.

PrimaryTable:

FK (int)
DateCreated
FKTable1
FKTable2
FKTable3

FKTable1, 2, 3 are seperate tables but the ID values are stored in the above columns

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 13:40:37

I'm confused by the structure you posted. Can you provide sample data and expected results?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-24 : 15:46:02

quote:
Originally posted by tech

Thanks. kind of....

the OrderID is for the "primary" table. but the FK tables dont work based upon the OrderID but only the value stored in the FK column.

PrimaryTable:

FK (int)
DateCreated
FKTable1
FKTable2
FKTable3

FKTable1, 2, 3 are seperate tables but the ID values are stored in the above columns

so which of these columns will have ID values you passed? will it be FK column or will it be in any one of FKtable1,FKtable2 etc?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources