March 22, 2007 at 2:44 pm
I have 4 different tables that contain descriptivie information; Orders, Receipts, Adjustments and Transfers
I have one central table that contains a FK reference to these tables based on a TypeID
For example;
dDate, nFK, nTypeID
I can create a large select like this:
SELECT
dDate, Orders.cDescription
FROM MainTable
INNER JOIN Orders
ON MainTable.nFK = Orders.nPK
WHERE nTypeID = 1
UNION ALL
SELECT
dDate, Receipts.cDescription
FROM MainTable
INNER JOIN Receipts
ON MainTable.nFK = Receipts.nPK
WHERE nTypeID = 2
and so on for each type ...
Is there a more ellegant way of doing this?
Regards
March 22, 2007 at 4:58 pm
maybe a query against MainTable that left joins against all the others?
---------------------------------------
elsasoft.org
March 22, 2007 at 5:10 pm
Bad design always leads to ugly queries and maintenance problems.
_____________
Code for TallyGenerator
March 22, 2007 at 7:15 pm
I am interested as why you would say this in this case?
This is a one off query to extract information about some transactions that would be rarely used.
March 26, 2007 at 1:12 am
It isn't how often this particular query will be used. Problem is that you use one column in the base table to join to several tables - based on value in another column (at least I suppose this is what Sergiy meant).
Quote:
"I have one central table that contains a FK reference to these tables based on a TypeID"
Either the "descriptive" tables are so similar that all their data should belong to one table (with some Type column probably), or they are different and then there should be one FK for each table in your central table.
Of course, it can work the way you have it, but I think it isn't a good idea to do it (BTW I work as admin of SQLS-based IS that uses this approach in some places, fortunately only a few - and I can say life would be much easier without it!).
March 26, 2007 at 3:19 pm
Is this what you're after?
create table #test1 (id int, title varchar(20))
create table #test2 (id int, title varchar(20))
create table #fk(tid int, sourceTable varchar(20))
insert into #test1 values (1, 'thingA')
insert into #test1 values (2, 'thingB')
insert into #test2 values (1, 'thingC')
insert into #test2 values (2, 'thingB')
insert into #fk values (1, 'A')
insert into #fk values (1, 'B')
insert into #fk values (2, 'A')
select f.tid,
f.sourceTable,
case f.sourceTable
when 'A' then a.title
when 'B' then b.title
end as title
FROM #fk f
left join #test1 a on f.sourceTable = 'A' and f.tid = a.id
left join #test2 b on f.sourceTable = 'B' and f.tid = b.id
tid sourceTable title
----------- -------------------- --------------------
1 A thingA
1 B thingC
2 A thingB
(3 row(s) affected)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply