selecting all records from one column

[alex]

Experts,

I'm trying to write a query (actually in Oracle, but I'm trying to
get Access to write the SQL for me) based on one table. Although this
query has specific parameters, I want the query to return all the
records from a specific column-if the parameters in the where clause
are not true, a null value will be returned. To simplify: Think of it
as trying to do an outer join with one table. I hope I didn't
confuse anyone.

 

[Phillip Windell]

I would think it would just be a simple Select query

Select
<column I want>
From
<table in question>
Where
<some other column>
Not
<some value>

 

[alex]

Phillip,

I'm not sure what you mean by "Not." I've never used that before. The
problem is that my WHERE clause will limit the amount of records
returned from the "COLUMN I WANT." I want every record from "COLUMN I
WANT," despite the where clause. I need the WHERE clause, however, to
return specific data. I just want to see all the records from "COLUMN
I WANT," even though some rows will only contain that data and nothing
else.

 

[Phillip Windell]

I don't know then. It seems to me that you just wouldn't use the Where
clause at all and you would get all the records.

 

[Joseph Meehan]

Phillip,

I'm not sure what you mean by "Not." I've never used that before.
The problem is that my WHERE clause will limit the amount of records
returned from the "COLUMN I WANT." I want every record from "COLUMN I
WANT," despite the where clause. I need the WHERE clause, however, to
return specific data. I just want to see all the records from "COLUMN
I WANT," even though some rows will only contain that data and nothing
else.

Do you mean you want to hide some of the available data, but show each
record?

It is not at all clear to me what you want. Maybe a sample would help.

 

[alex]

For example:

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

I want to select all records where TITLE = EX
The problem, however, it that I want all of the SSN records.
My table should look like this:

SSN TITLE LOCATION
322 EX DALLAS
568 null null
536 EX DALLAS
485 null null
854 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

 

[Pieter Wijnen]

Well How's This?:

SELECT SSN, TITLE, LOCATION
FROM TABLE_1
WHERE TITLE='SSN'
UNION ALL
SELECT SSN, NULL,NULL
FROM TABLE_1
WHERE TITLE <> 'SSN'

HTH

Pieter

 

[alex]

Pieter - Thanks for the response

Let me try a better example:
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, is 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

 

[Guest]

I can do it with two queries.

qryAllSSN:

SELECT SSN
FROM TABLE_1;

qryYourMainQuery:

SELECT SSN, TITLE, LOCATION
FROM TABLE_1
LEFT JOIN qryAllSSN ON qryAllSSN.SSN = TABLE_1.SSN
WHERE TABLE_1.TITLE = 'EX' and TABLE_1.LOCATION = 'DALLAS'

Or something to that affect. I haven't tested it.

Dave

 

[alex]

I've done it with two queries too. I was hoping to do it with one.
I've been told it's possible (maybe with a nested sub query); I'm not
sure.

 

[Guest]

qryAllSSN:

SELECT SSN, TITLE, LOCATION
FROM TABLE_1
WHERE TITLE = 'EX' and LOCATION = 'DALLAS';

qryYourMainQuery:

SELECT TABLE_1.SSN, qryAllSSN.TITLE, qryAllSSN.LOCATION
FROM TABLE_1 LEFT JOIN qryAllSSN ON qryAllSSN.SSN = TABLE_1.SSN;


Someone clever will be able to tie them into one query.

Dave

 

[alex]

We're still at two tables/queries, though.

 

[alex]

We're still at two tables/queries, though.

Experts: here's your chance to shine>

 

[Phillip Windell]

Experts: here's your chance to shine>

Perhaps they already have, and the answer is that you can't do it that way.

 

[James A. Fortune]

I've done it with two queries too. I was hoping to do it with one.
I've been told it's possible (maybe with a nested sub query); I'm not
sure.

Perhaps this comes close:

SELECT SSN, IIF([TITLE] <> 'EX' OR [LOCATION] <> 'DALLAS', Null,
[TITLE]) AS TheTitle, IIF([TITLE] <> 'EX' OR [LOCATION] <> 'DALLAS',
Null, [LOCATION]) AS TheLocation FROM TABLE_1 where SSN IN ('322',
'536', '258', '478', '861');

or even:

SELECT TABLE_1.SSN, [TITLE]='EX' And [LOCATION]='DALLAS' AS PickMe,
IIf([PickMe],[TITLE],Null) AS TheTitle, IIf([PickMe],[LOCATION],Null) AS
TheLocation FROM TABLE_1 WHERE TABLE_1.SSN In
('322','536','258','478','861');

James A. Fortune
(e-mail address removed)