Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do I have to use repetative joins to get values Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 8:05 AM
Points: 43, Visits: 191
Hello,

I have a query that returns several key values:

SELECT * FROM TableX

Which returns:
Value1 = 1001
Value2 = 9671
.....etc

Now Value1 & Value2 are stored in the same ValueTable. To get one translated to a textual representation I would join like so:

SELECT MRX.Col1 VAT.TextName, MRX.Col3, Col4....
FROM TableX MRX

INNER JOIN ValueTable VAT ON
MRX.Col2 = VAT.Value1


This is fine and dandy and gets one key translated to user friendly text but what about the other (Col4)?
Do I have to join again..add an OR clause or...?

SELECT MRX.Col1 VAT.TextName, MRX.Col3, VAT2.TextName....
FROM TableX MRX

INNER JOIN ValueTable VAT ON
MRX.Col2 = VAT.Value1

INNER JOIN ValueTable VAT2 ON
MRX.Col4 = VAT2.Value2

TIA'JB
Post #1466172
Posted Friday, June 21, 2013 9:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
To be sure you get a coded and tested solution, it would help if you could post up some sample data and an expected result set. The people who answer questions on these forums are unpaid. Setting up the problem for them so that they can just cut and paste is a simple courtesy which will pay you big dividends in timeliness and quality of the responses you get. There is a good read here that will explain what we need and why we need it. Thanks.

Edited to take a blind stab at answering.

Reading what you've posted for the third time, it believe you're saying that you have want to retrieve text values from different rows, and present them side by side as columns. Perhaps you have a descriptions table associated with codes in the original table. If that assumption is correct you only have to join once, but you need to either PIVOT or cross-tab to present the values as different columns.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1466238
Posted Friday, June 21, 2013 9:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Here is an illustration of what I think you're talking about. If my assumptions are wrong, please change the code up to illustrate generally what your tables and data look like, and what you would expect the results to look like given the sample data you supply.


declare @descriptions table (code int identity primary key, code_description varchar (50))
insert into @descriptions
select 'small' union all
select 'medium' union all
select 'large' union all
select 'red' union all
select 'green' union all
select 'blue' union all
select 'stock' union all
select 'custom'

select * from @descriptions


declare @items table (itemNumber int , code int )
insert into @items
select 1,3 union all
select 1,6 union all
select 1,7

-- one join gets the descriptions
select itemnumber, i.code, d1.code_description, row_number() over(order by (select Null)) as rowID
from @items i
join @descriptions d1 on d1.code = i.code
where itemNumber = 1

-- this code cross-tabs the rows into columns
;with cte as (
select itemnumber, i.code, d1.code_description, row_number() over(order by (select Null)) as rowID
from @items i
join @descriptions d1 on d1.code = i.code
where itemNumber = 1
)

select itemnumber
, max(case when rowID = 1 then code_description else null end) as Col1
, max(case when rowID = 2 then code_description else null end) as Col2
, max(case when rowID = 3 then code_description else null end) as Col3
from cte
group by itemnumber





__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1466268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse