473,390 Members | 1,348 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

outer join column

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
8 1563
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
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
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
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
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
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
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
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Surly | last post by:
Both the Microsoft and the Oracle ODBC driver will take an outer join with the "{oj....}" ODBC syntax and Put the (+) on one column as determined by order of the columns in the "Left Outer Join"...
3
by: sks | last post by:
I have a Product table, a Categories table and a join table that contains product to category mappings (each product can be in many categories) CREATE TABLE categories ( id bigint(20) unsigned...
0
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...
3
by: dumbledad | last post by:
Hi All, I'm confused by how to replace a SELECT statement in a SQL statement with a specific value. The table I'm working on is a list of words (a column called "word") with an index int...
1
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
1
by: Rory Campbell-Lange | last post by:
I'm having troube doing a left outer self join on a table. The sent column shows the number of items sent to each recipient from each source. The received column (generated by the outer join) is...
4
by: Anthony Robinson | last post by:
I was actually just wondering if someone could possibly take a look and tell me what I may be doing wrong in this query? I keep getting ambiguous column errors and have no idea why...? Thanks in...
1
by: Andreas Bauer | last post by:
Hi, when I try a left outer join on one table everything works fine: select * from (tourist.users u left outer join tourist.user_extended_info ue on (u.id = ue.id)) But now I need to do...
6
by: PW | last post by:
I've created an ASP application which uses an Access database. I've created an outer join query, but for some reason the "Property_Def" column is not aligning with the "ESPN" column. They should...
5
by: gimme_this_gimme_that | last post by:
I have an employee table with two columns, one named login, the other named otherdata. I have a list of login values, some of which do not exist in the employee table. I want to fetch the...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.