Need help with complex query
-
Can somebody help me with the following query, I'm trying to combine the two rows so that only one row shows per "PayTransID" valueThanks
-
Can you post your query.. Thant might help.
-
Sorry,
the picture in the message above was meant to include a screen shot of the query builder. I won't have access to the pic again until tonight. I will repost the screenshot then.
Thanks
-
Generally speaking you will need to read the same file back in for a second time. For example:
Select name, title, sex, hair, t1.sexdesc, t2.hairdesc
From dbo.personel as P
INNER JOIN dbo.DESCTBLE as T1 on T1.sex = P.sex
INNER JOIN dbo.DESCTBLE as T2 on t2.hair = p.hair
The description table is read in twice and the numeric codes are matched. This will result in having multiple descriptions on each row.
-
Hop you were trying to do this:
--Sample Data
Create table Ppl(PPLname varchar(100), title varchar(100), sex char(1), hair char(50))
Create table DESCTBLE (Code nvarchar(50), CodeDesc char(255))
Insert into Ppl
select 'AAA','Mr A','M','Black'
union
select 'BBB','Mr B','M','Brown'
union
select 'CCC','Ms C','F','Brown'
union
select 'DDD','Ms D','F','Black'
Insert DESCTBLE
select 'M','Male'
union
select 'F','Female'
union
select 'Black','Black Color'
union
select 'Brown','Brown Color'
union
select 'Blue','Blue Color'
Here is your query
-----------------
Select PPLname, title, sex, hair, t1.CodeDesc, t2.CodeDesc
From dbo.Ppl as P
INNER JOIN dbo.DESCTBLE as T1 on P.sex = T1.Code
INNER JOIN dbo.DESCTBLE as T2 on P.hair = T2.Code
-
Here is the screen shot:
-
Any ideas folks? I refuse to be beaten on this one, and I'm 99.9% certain its "do-able!"
-
Could it have something to do with the joins?
-
have you tried it with inner joins instead of outer joins?
-
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply