Posted by David Portas on 03/17/06 19:03

alex wrote:
> 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


It looks like you could use a self-join:

SELECT T1.ssn, T2.title, T2.location
FROM table_1 AS T1
LEFT JOIN table_1 AS T2
ON T1.ssn = T2.ssn
AND T2.title = 'EX'
AND T2.location = 'DALLAS'
WHERE T1.ssn IN ('322', '536', '258', '478', '861') ;


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]