data selection

Question

Post reply

data selection

znkin

Old Hand

Points: 382
More actions
October 4, 2008 at 6:00 am

#127829

I have 5 sql tables with data as
1)InternalSource
SrcId SrcName
100IntSrc1
101IntSrc2
2)ExternalSource
SrcId SrcName
100ExtSrc1
101ExtSrc2
3)Sourcecabinet
SrcType SrcId Category CabinetId
1 100 441111
1 100 452222
1 101 444543
2 100 443232
2 100 45 1113
2 101 443212
2 1 01 45 3112
4)TransferMaster
PK_Id SrcType SrcId
1 1 100
2 1 101
5)TransferDetails
FK_Id SrcType SrcId Category
1 2 100 44
1 2 100 45
2 2 100 45
Let me explain the above tables. I have 2 types of sources and so 2 source master tables
1)InternalSource
2)ExternalSource
Now each source has been mapped to one or many cabinets for each category as can be seen in the Sourcecabinet table.
The SrcType column in Sourcecabinet table is 1(for InternalSource) and 2(for ExternalSource) since I have combined the mapping details for them in this one table only.(This 1 and 2 for sourcetype is fixed since I have only 2 types of sources.)
Now whenever a transfer happens from 1 to 2 (means from InternalSource to ExternalSource) the source of transfer is stored in table TransferMaster where SrcType column is 1(means Internal Source) and SrcId is the source name(Id saved from InternalSource table)
The destination of trasnfer is stored in TransferDetails table where SrcType column is 2(means external Source) and SrcId is the source name(Id saved from ExternalSource table) and the transfer was for a category given in the category column.
I have written a sql query that will fetch list of all transfers that have happpened for each category. so for this I have the query as below
select A.SrcId as 'Source',B.SrcId as 'Destination', B.Category from TransferMaster A inner join TransferDetails B on A.PK_Id = B.FK_Id
Now I also want for the source and destination the cabinet ids, that is what was the cabinet Id of the source and the cabinetId of the destination(CabinetId is gievn in table Sourcecabinet). How do I get these 2 more columns in my sql query.
Thanks...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Want a better answer faster? Take a look at the link in my signature below and also post the query you already have. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

znkin Old Hand Points: 382 More actions · Answer 2

Sorry I should have done it earlier.....

The structure and data of the tables is as follows:

create table InternalSource

(

SrcId int IDENTITY(100,1) PRIMARY KEY CLUSTERED

SrcName varchar(50)

)

create table ExternalSource

(

SrcId int IDENTITY(100,1) PRIMARY KEY CLUSTERED

SrcName varchar(50)

)

create table Sourcecabinet

(

SrcType int,

SrcId int,

Category int,

CabinetId int

)

create table TransferMaster

(

PK_Id int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

SrcType int,

SrcId int

)

create table TransferDetails

(

FK_Id int,

SrcType int,

SrcId int,

Category int

)

insert into InternalSource values('IntSrc1')

insert into InternalSource values('IntSrc2')

insert into ExternalSource values('ExtSrc1')

insert into ExternalSource values('ExtSrc2')

insert into Sourcecabinet values(1,100,44,1111)

insert into Sourcecabinet values(1,100,45,2222)

insert into Sourcecabinet values(1,101,44,4543)

insert into Sourcecabinet values(2,100,44,3232)

insert into Sourcecabinet values(2,100,45,1113)

insert into Sourcecabinet values(2,101,44,3212)

insert into Sourcecabinet values(2,101,45,3112)

insert into TransferMaster values(1,100)

insert into TransferMaster values(1,101)

insert into TransferDetails values(1,2,100,44)

insert into TransferDetails values(1,2,100,45)

insert into TransferDetails values(2,2,100,45)

Let me explain the above tables. I have 2 types of sources and so 2 source master tables

1)InternalSource

2)ExternalSource

Now each source has been mapped to one or many cabinets for each category as can be seen in the Sourcecabinet table.

The SrcType column in Sourcecabinet table is 1(for InternalSource) and 2(for ExternalSource) since I have combined the mapping details for them in this one table only.(This 1 and 2 for sourcetype is fixed since I have only 2 types of sources.)

Now whenever a transfer happens from 1 to 2 (means from InternalSource to ExternalSource) the source of transfer is stored in table TransferMaster where SrcType column is 1(means Internal Source) and SrcId is the source name(Id saved from InternalSource table)

The destination of trasnfer is stored in TransferDetails table where SrcType column is 2(means external Source) and SrcId is the source name(Id saved from ExternalSource table) and the transfer was for a category given in the category column.

I have written a sql query that will fetch list of all transfers that have happpened for each category. so for this I have the query as below

select A.SrcId as 'Source',B.SrcId as 'Destination', B.Category from TransferMaster A inner join TransferDetails B on A.PK_Id = B.FK_Id

Now I also want for the source and destination the cabinet ids, that is what was the cabinet Id of the source and the cabinetId of the destination(CabinetId is gievn in table Sourcecabinet). How do I get these 2 more columns in my sql query.

Thanks again

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 3

Here's how to get the destination CabinetID. But I'm not sure about the source.

Why doesn't TransferMaster have a Category?

Can you please post a sample result set?

SELECT TM.SrcId AS Source

,TD.SrcId AS Destination

,TD.Category

,SC.CabinetID AS DestinationCabinetID

FROM TransferMaster TM

JOIN TransferDetails TD ON TM.PK_Id = TD.FK_Id

JOIN SourceCabinet SC ON TD.SrcID = SC.SrcID

AND TD.Category = SC.Category

AND TD.SrcType = SC.SrcType

znkin Old Hand Points: 382 More actions · Answer 4

Thanks for your reply.

The Transfermaster does not have a category because while doing a transfer that transfer can happen from 1 source to 1 or multiple destinations under the same or differenet categories.

So while printing the all the transfers that happened from which source to which destination I also want to print side by side their cabinet ids (both for the source as well as the destination)

Its like example say a transfer happened for Source1 whose cabinet id for category 44 is 1111 and for category 45 it is 4543

So if Source1 does a transfer to a destination source 'Source2' under category 44 then the transfer should go to cabinetid 5555 as this is the cabinetid for destination for category id 44 and query should be shown as

SourceName DestinationName Category SourceCabinetId DestinationCabinetId

Source1 Source2 44 1111 5555

similarly if the transfer happened under category 45 also for the same source and destination the query should output have 2 records for 2 transfers(assuming that for category 45 the source cabinet id is 4543 and the destination cabinet id is 6522)

SourceName DestinationName Category SourceCabinetId DestinationCabinetId

Source1 Source2 44 1111 5555

Source1 Source2 45 4543 6522

The Transfermaster will always have one record only which would be the source details and the Transferdetails table would have one or multiple records if the transfer was done to multiple destination sources or even to the same destination source but under different categories.

Please note that for each category the cabinetid would be different for a particular source.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 5

Does this work for you?

SELECT TM.SrcId AS Source

,TD.SrcId AS Destination

,TD.Category

,SC1.CabinetID AS DestinationCabinetID

,SC2.CabinetID AS SourceCabinetID

FROM TransferMaster TM

JOIN TransferDetails TD ON TM.PK_Id = TD.FK_Id

JOIN SourceCabinet SC1 ON TD.SrcID = SC1.SrcID

AND TD.SrcType = SC1.SrcType

JOIN SourceCabinet SC2 ON TM.SrcID = SC2.SrcID

AND TM.SrcType = SC2.SrcType

ORDER BY Source

, Destination

, Category