SOLVED: MS SQL Advanced Query HELP badly needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SOLVED: MS SQL Advanced Query HELP badly needed

Author

Topic

MRdNk
Starting Member

6 Posts

Posted - 2010-05-18 : 15:44:58

I have an issue, that I don't know how to solve.

I have a Records Table with a Record_ID (unique) this JOINs to another table which uses the same Record_ID but has multiple records, and I only want one based on the value of the Type column.

i.e.

SELECT <Records fields>, Number FROM Records
LEFT JOIN Numbers
ON Records.Record_ID = Numbers.Record_ID
(IF Numbers.Type = 'A' THEN Numbers.Number
ELSE IF Numbers.Type = 'B' THEN Numbers.Number
ELSE IF Numbers.Type = 'C' THEN Numbers.Number)

Please help, I didn't make the tables, can't change them, and yes I hate it.

MRdNk
Starting Member

6 Posts

Posted - 2010-05-18 : 19:32:44

Currently...

SELECT <Records fields>, Number FROM Records
LEFT JOIN Numbers
ON Records.Record_ID = Numbers.Record_ID

returns 3 records
ROW 1 (Type=A): <Records Fields>, 123
ROW 2 (Type=B): <Records Fields>, 234
ROW 3 (Type=C): <Records Fields>, 345

Whereas I only want 1 of these, if there is a Type = A row then I want the Number field from that table, in the instance above this would be 123
ROW 1: <Records Fields>, 123

However if there isn't a record with Type A, I want to return either row Type=B or Type=C, based on a value in the Records Table.
So...
ROW 1 (Type=B): <Records Fields>, 234
ROW 2 (Type=C): <Records Fields>, 345

Return Row 1 if Records table - Records.Record_ID = B890 THEN Return Type B
OR ROW 2 if Records table - Records.Record_ID = C789 THEN Return Type C.

I hope this makes more sense.

MRdNk
Starting Member

6 Posts

Posted - 2010-05-20 : 15:11:29

I've solved the issue, this is the solution:

SELECT Records.*,
CASE
WHEN A.Number IS NOT NULL THEN A.Number
WHEN B.Number IS NOT NULL THEN B.Number
END Number
FROM tblRecords R
LEFT JOIN tblNumbers A ON R.Record_ID = A.Record_ID AND A.NumType = 'N'
LEFT JOIN tblNumbers B ON R.Record_ID = B.Record_ID AND B.NumType = LEFT(R.Record_ID,1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources