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:
SSNTITLELOCATION
322EXDALLAS
568REPFT_WORTH
536EXDALLAS
485SLSYOUNGSTOWN
854BROFEEDER
258EXEVANSVILLE
478TEMPTROY
861SLSDALLAS
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:
SSNTITLELOCATION
322EXDALLAS
536EXDALLAS
258nullnull
478nullnull
861nullnull
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. Thanksalex 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:
> SSNTITLELOCATION
> 322EXDALLAS
> 568REPFT_WORTH
> 536EXDALLAS
> 485SLSYOUNGSTOWN
> 854BROFEEDER
> 258EXEVANSVILLE
> 478TEMPTROY
> 861SLSDALLAS
> 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:
> SSNTITLELOCATION
> 322EXDALLAS
> 536EXDALLAS
> 258nullnull
> 478nullnull
> 861nullnull
> 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/...US,SQL.90).aspx
--|||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.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|||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_1COLUMN_2COLUMN_3
FordBlue4door
ChevyBlk2door
HondaBlue4door
NissanRed2door
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_MakeColumn_2Column_3
FordBlue4door
Chevynullnull
HondaBlue4door
Nissannullnull
Toyotanullnull
Jeepnullnull|||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_1COLUMN_2COLUMN_3
> FordBlue4door
> ChevyBlk2door
> HondaBlue4door
> NissanRed2door
> 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_MakeColumn_2Column_3
> FordBlue4door
> Chevynullnull
> HondaBlue4door
> Nissannullnull
> Toyotanullnull
> Jeepnullnull
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/...US,SQL.90).aspx
--|||I'm a fan of temp tables:
create table #results
(ssnvarchar(10)
titlevarchar(10),
locationvarchar(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
setr.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|||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'
')|||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')|||(Michael.Suarez@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
JayH wrote: