Posted by Doug Lawry on 03/17/06 19:28

Please view the solution below in a fixed font.
--
Regards,
Doug Lawry
www.douglawry.webhop.org

_____
___| SQL |________________________________________________

CREATE TABLE table_1
(
[SSN] SMALLINT,
[Title] VARCHAR(4),
[Location] VARCHAR(20)
)

INSERT INTO table_1 VALUES (322, 'EX', 'DALLAS')
INSERT INTO table_1 VALUES (568, 'REP', 'FT_WORTH')
INSERT INTO table_1 VALUES (536, 'EX', 'DALLAS')
INSERT INTO table_1 VALUES (485, 'SLS', 'YOUNGSTOWN')
INSERT INTO table_1 VALUES (854, 'BRO', 'FEEDER')
INSERT INTO table_1 VALUES (258, 'EX', 'EVANSVILLE')
INSERT INTO table_1 VALUES (478, 'TEMP', 'TROY')
INSERT INTO table_1 VALUES (861, 'SLS', 'DALLAS')

SELECT
[SSN],
CASE
WHEN [Title] = 'EX' AND [Location] = 'DALLAS'
THEN [Title]
ELSE NULL
END AS [Title],
CASE
WHEN [Title] = 'EX' AND [Location] = 'DALLAS'
THEN [Location]
ELSE NULL
END AS [Location]
FROM
table_1
WHERE
[SSN] IN ('322', '536', '258', '478', '861')

_________
___| Results |____________________________________________

SSN Title Location
------ ----- --------------------
322 EX DALLAS
536 EX DALLAS
258 NULL NULL
478 NULL NULL
861 NULL NULL

(5 row(s) affected)

__________________________________________________________


"alex" <sql_aid@yahoo.com> wrote in message
news:1142614348.612950.22570@j33g2000cwa.googlegroups.com...
> Thanks in advance for any help.
> I'm trying to attempt the following with one table/query.
>
> I have a table called TABLE_1
> It has the following fields:
>
> SSN TITLE LOCATION
> 322 EX DALLAS
> 568 REP FT_WORTH
> 536 EX DALLAS
> 485 SLS YOUNGSTOWN
> 854 BRO FEEDER
> 258 EX EVANSVILLE
> 478 TEMP TROY
> 861 SLS DALLAS
>
> I want to
> select SSN, TITLE, LOCATION
> from table_1
> where ssn = ('322', '536', '258', '478', '861')
> and title = 'EX'
> and location = 'DALLAS'
>
> The problem, however, it that I want all of the SSN records on my list.
> My table should look like this:
>
> SSN TITLE LOCATION
> 322 EX DALLAS
> 536 EX DALLAS
> 258 null null
> 478 null null
> 861 null null
>
> I can easily do this on two tables with a left outer join. I do not
> know how to accomplish same (theory) with one table.
>
> Any help on how to do this would be appreciated. Thanks

[Back to original message]