getting source records table name?

Hello,
I need some help on getting the name of a table, or even creating an id
of some kind, that relates to where a record was pulled from. Ive
included a *simplified* example so you can see what Im talking about
say I have 3 tables, and a query that returns 2 rows, each row made up
of 1 column from table1 and 1 column from either table2 or table3.
There is no unique identifier I can use in table2 or table3.
Ive played with using UNION but it doesnt suit a complex query like
the actual query Im using. Maybe theres some stored procedure, or
another hack to solve this?
Any help is appreciated,
Craig.
table1
--
table1_id
1
2
table2
--
table2_id, table2.table1_id
1,1
2,NULL
table3
--
table3_id, table3.table1_id
1,NULL
2,1
query
--
select table1.table1_id, X
from table1
left join table2
on table1.table1_id = table2.table1_id
left join table3
on table1.table1_id = table3.table3_id
result
--
table1_id
1, X
1, X
(where X displays the source table name)
"The only way to get rid of temptation is to yield to it...""Craig H." <spam@.thehurley.com> wrote in message
news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
> query
> --
> select table1.table1_id, X
> from table1
> left join table2
> on table1.table1_id = table2.table1_id
> left join table3
> on table1.table1_id = table3.table3_id
> result
> --
> table1_id
> 1, X
> 1, X
Craig,
I'm -- the value in column 1, in your query, will ALWAYS come
from table1. Is your real query different in some way? Please post the
actual code you're trying to work with.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 14:47, Adam Machanic wrote:
"...how we thwart the natural love of learning by leaving the natural
method of teaching what each wishes to learn, and insisting that you
shall learn what you have no taste or capacity for."

> "Craig H." <spam@.thehurley.com> wrote in message
> news:OAnj6GEIFHA.2656@.TK2MSFTNGP09.phx.gbl...
>
>
> Craig,
> I'm -- the value in column 1, in your query, will ALWAYS come
> from table1. Is your real query different in some way? Please post the
> actual code you're trying to work with.
>
That's correct Adam, but it's the source of column 2 in the result that
I need to obtain (bear in mind that there is nothing to identify what
the table represents in the data contained within table2 & table3). The
actual query looks like:
select le.Name as Client, lec.Name as CompanyType, div.name as Division,
p.Name as PayeeName
from lentity le
inner join contract c
on ((c.contracting_lentity_id = le.lentity_id) and (c.enddate IS NULL or
c.enddate > getdate()))
inner join lentity div
on div.lentity_ID = c.providing_lentity_id
inner join lentityCode lec
on lec.lentityCode = le.lentityCode
inner join payee p
on ((p.contract_id = c.contract_id) and (p.active = 'true'))
inner join paymentrouting pr
on pr.payee_id = p.payee_id
left join aPaymentChannel apc
on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
'true'))
left join bPaymentChannel bpc
on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
'true'))
left join cPaymentChannel cpc
on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
'true'))
left join dPaymentChannel dpc
on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
'true'))
left join ePaymentChannel gpc
on ((epc.paymentrouting_id = pr.paymentrouting_id) and (epc.active =
'true'))
left join fPaymentChannel fpc
on ((fpc.paymentrouting_id = pr.paymentrouting_id) and (fpc.active =
'true'))
order by Client|||"Craig H." <spam@.thehurley.com> wrote in message
news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
> select le.Name as Client, lec.Name as CompanyType, div.name as Division,
> p.Name as PayeeName
Which column are you concerned with?
The first will always come from le, the second from lec, the third from
div, and the fourth from p...
Can you show me exactly what the issue is?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||On 03/03/2005 16:46, Adam Machanic wrote:
> "Craig H." <spam@.thehurley.com> wrote in message
> news:O%23DbBdEIFHA.2564@.tk2msftngp13.phx.gbl...
>
>
> Which column are you concerned with?
> The first will always come from le, the second from lec, the third fro
m
> div, and the fourth from p...
> Can you show me exactly what the issue is?
>
O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
3rd and 4th columns are returned in a result, I would also like to
return a column indicating which left join statement caused a match (or
which XPaymentChannel a match was was found in):
left join XPaymentChannel X
on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
The reason I'm finding this difficult is because the XPaymentChannel
tables have no column that differentiate it from the other
XPaymentChannel tables.
Is that clearer? Thanks for your time so far Adam.
Craig.
"You can't build a reputation on what you are going to do."|||>> ..each row made up of 1 column from table1 and 1 column from either
table2 or table3.
There is no unique identifier I can use in table2 or table3. <<
This sounds like the design is screwed up and you have either put the
same data in two places, or the table you return is made of mixed
tuples. And two tables without a key is just plain wrong!
Next, you will be using a lot of NULLs and setting flags all over the
place, or using dynamic SQL :)|||Okay... I think you want to add:
CASE
WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
..
ELSE NULL
END AS PaymentRoutingTbl
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Craig H." <spam@.thehurley.com> wrote in message
news:ui66XaFIFHA.2704@.tk2msftngp13.phx.gbl...
> On 03/03/2005 16:46, Adam Machanic wrote:
from
> O.K. I didn't explain myself clearly enough. Every time the 1st, 2nd,
> 3rd and 4th columns are returned in a result, I would also like to
> return a column indicating which left join statement caused a match (or
> which XPaymentChannel a match was was found in):
> left join XPaymentChannel X
> on ((X.paymentrouting_id = pr.paymentrouting_id) and (X.active = 'true'))
> The reason I'm finding this difficult is because the XPaymentChannel
> tables have no column that differentiate it from the other
> XPaymentChannel tables.
> Is that clearer? Thanks for your time so far Adam.
> Craig.
>
> --
> "You can't build a reputation on what you are going to do."|||On 03/03/2005 17:40, Adam Machanic wrote:
> Okay... I think you want to add:
> CASE
> WHEN apc.paymentrouting_id IS NOT NULL THEN 'apc'
> WHEN bpc.paymentrouting_id IS NOT NULL THEN 'bpc'
> WHEN cpc.paymentrouting_id IS NOT NULL THEN 'cpc'
> ...
> ELSE NULL
> END AS PaymentRoutingTbl
>
Perfect, thank you Adam!
"The only way to get rid of temptation is to yield to it..."