By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

outer join column

P: n/a
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

Mar 17 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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/m...S,SQL.90).aspx
--

Mar 17 '06 #2

P: n/a
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" <sq*****@yahoo.com> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.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

Mar 17 '06 #3

P: n/a
Thanks for the response. Let me pose the example this way:

I'm attempting to write a query against a table (Table_A). Let's
say the table has three columns with various records. Column_1 lists
the names of vehicle manufacturers. I have a preexisting list that
I'm attempting to bounce of my table. I want, however, all of the
names in my preexisting list to appear in my results. If the criterion
doesn't exit, it should say 'NULL'. Thanks for any help.

For example:

Table_A

COLUMN_1 COLUMN_2 COLUMN_3
Ford Blue 4door
Chevy Blk 2door
Honda Blue 4door
Nissan Red 2door

My preexisting list looks like this:

Ford
Chevy
Honda
Nissan
Toyota
Jeep

I want to select every record from COLUMN_2 and _3 that = Blue and
4door.
I want my results to look like this:

Vehicle_Make Column_2 Column_3
Ford Blue 4door
Chevy null null
Honda Blue 4door
Nissan null null
Toyota null null
Jeep null null

Mar 17 '06 #4

P: n/a
alex wrote:
Thanks for the response. Let me pose the example this way:

I'm attempting to write a query against a table (Table_A). Let's
say the table has three columns with various records. Column_1 lists
the names of vehicle manufacturers. I have a preexisting list that
I'm attempting to bounce of my table. I want, however, all of the
names in my preexisting list to appear in my results. If the criterion
doesn't exit, it should say 'NULL'. Thanks for any help.

For example:

Table_A

COLUMN_1 COLUMN_2 COLUMN_3
Ford Blue 4door
Chevy Blk 2door
Honda Blue 4door
Nissan Red 2door

My preexisting list looks like this:

Ford
Chevy
Honda
Nissan
Toyota
Jeep

I want to select every record from COLUMN_2 and _3 that = Blue and
4door.
I want my results to look like this:

Vehicle_Make Column_2 Column_3
Ford Blue 4door
Chevy null null
Honda Blue 4door
Nissan null null
Toyota null null
Jeep null null


Please post DDL in future otherwise you are forcing us to guess what
your datatypes, keys, constraints and RI look like. The best way to
post sample data is as INSERT statements.

What is the "preexisting list" in your example? There is no list or
array data structure in SQL. The most sensible way to supply that set
of values is in a table. Use a table variable if you don't have a
permanent table. Example:

CREATE TABLE table_a (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY,
column_2 VARCHAR(10) NOT NULL, column_3 VARCHAR(10) NOT NULL);
INSERT INTO table_a VALUES ('Ford', 'Blue', '4door');
INSERT INTO table_a VALUES ('Chevy', 'Blk', '2door');
INSERT INTO table_a VALUES ('Honda', 'Blue', '4door');
INSERT INTO table_a VALUES ('Nissan', 'Red', '2door');

CREATE TABLE table_b (vehicle_make VARCHAR(10) NOT NULL PRIMARY KEY);
INSERT INTO table_b VALUES ('Ford');
INSERT INTO table_b VALUES ('Chevy');
INSERT INTO table_b VALUES ('Honda');
INSERT INTO table_b VALUES ('Nissan');
INSERT INTO table_b VALUES ('Toyota');
INSERT INTO table_b VALUES ('Jeep');

SELECT B.vehicle_make, A.column_2, A.column_3
FROM table_b AS B
LEFT JOIN table_a AS A
ON B.vehicle_make = A.vehicle_make
AND column_2 = 'Blue'
AND column_3 = '4door' ;

Result:

vehicle_make column_2 column_3
------------ ---------- ----------
Chevy NULL NULL
Ford Blue 4door
Honda Blue 4door
Jeep NULL NULL
Nissan NULL NULL
Toyota NULL NULL

(6 row(s) affected)
Hope this helps.

--
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/m...S,SQL.90).aspx
--

Mar 17 '06 #5

P: n/a
I'm a fan of temp tables:

create table #results
(ssn varchar(10)
title varchar(10),
location varchar(32))

insert #results (ssn)
values ('322')
insert #results (ssn)
values ('536')
insert #results (ssn)
values ('258')
insert #results (ssn)
values ('478')
insert #results (ssn)
values ('861')

update r
set r.title = t.title,
r.location = t.location
from #results r,
table_1 t
where r.ssn = t.ssn
and t.title = 'EX'
and t.location = 'DALLAS'
select * from #results

Mar 17 '06 #6

P: n/a
actually, this:

insert #results (ssn)
values ('322')
insert #results (ssn)
values ('536')
insert #results (ssn)
values ('258')
insert #results (ssn)
values ('478')
insert #results (ssn)
values ('861')

can also be done like this:

insert #results (ssn)
exec ('
select '322'
select '536'
select '258'
select '478'
select '861'
')

Mar 17 '06 #7

P: n/a
Try this:

select a.* from table_1 a left outer join
(
select SSN, TITLE, LOCATION
from table_1
where ssn = ('322', '536', '258', '478', '861')
and title = 'EX'
and location = 'DALLAS' ) b on a.ssn=b.ssn -- assuming that ssn is a
unique key
where a.ssn = ('322', '536', '258', '478', '861')

Mar 17 '06 #8

P: n/a
(Mi************@gmail.com) writes:
insert #results (ssn)
values ('322')
insert #results (ssn)
values ('536')
insert #results (ssn)
values ('258')
insert #results (ssn)
values ('478')
insert #results (ssn)
values ('861')

can also be done like this:

insert #results (ssn)
exec ('
select '322'
select '536'
select '258'
select '478'
select '861'
')


Or

insert #results (ssn)
select '322' union all
select '536' union all
select '258' union all
select '478' union all
select '861'

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 17 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.