consolidating sql queries

UNION:

SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
FROM cells ce,
cell_names cn,
extent_size es,
extent_type et
WHERE ce.cell_id = cn.cell_id and
cn.cell_name_current = 'Y' and
ce.cell_size = upper(es.extent_size_name) and
ce.cell_type = upper(et.extent_type_descr)
UNION
SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
FROM cells ce,
cell_names cn,
extent_size es,
extent_type et
WHERE ce.cell_id = cn.cell_id and
cn.cell_name_current = 'Y' and
ce.cell_size = upper(es.extent_size_name) and
ce.cell_type IS NULL

OR

SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
FROM cells ce,
cell_names cn,
extent_size es,
extent_type et
WHERE ce.cell_id = cn.cell_id and
cn.cell_name_current = 'Y' and
ce.cell_size = upper(es.extent_size_name) and
IsNull( ce.cell_type, upper(et.extent_type_descr) ) =
upper(et.extent_type_descr)

P.S. you should convert your SQL to ANSI SQL using JOINs
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
--------------------
"M Hamilton" <mhamilton@usgs.gov> wrote in message
news:3d359b7e$1@10.1.10.29...
>
> How would I write the query to pull the records from the following 2
select
> statements as 1 select statement? Notice that the last condition in each
> WHERE clause is different.
>
> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
> FROM cells ce,
> cell_names cn,
> extent_size es,
> extent_type et
> WHERE ce.cell_id = cn.cell_id and
> cn.cell_name_current = 'Y' and
> ce.cell_size = upper(es.extent_size_name) and
> ce.cell_type = upper(et.extent_type_descr);
>
> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
> FROM cells ce,
> cell_names cn,
> extent_size es,
> extent_type et
> WHERE ce.cell_id = cn.cell_id and
> cn.cell_name_current = 'Y' and
> ce.cell_size = upper(es.extent_size_name) and
> ce.cell_type IS NULL;
>
> Thanks in advance. MH

Yes, thanks, the UNION does produce the correct result set. The second set
of code you recommended, I did not understand.

Another set of code that I have found to work is:

SELECT distinct (ce.cell_id), cn.cell_name, ce.cell_size, ce.cell_type
FROM cells ce,
cell_names cn,
extent_type et,
extent_size es
WHERE (ce.cell_id = cn.cell_id and
cn.cell_name_current = 'Y' and
ce.cell_size = upper(es.extent_size_name) )
and
(ce.cell_type = upper(et.extent_type_descr)
or
ce.cell_type is null);

If I do not put the 'distinct' on the SELECT ce.cell_id portion, I get back
numerous duplicate rows of the 1 row that has the NULL value in it for cell_type,
while all the rest of the non-null valued rows would be returned correctly.
Strange!?

Thanks. MH

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>UNION:
>
>SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>FROM cells ce,
> cell_names cn,
> extent_size es,
> extent_type et
>WHERE ce.cell_id = cn.cell_id and
> cn.cell_name_current = 'Y' and
> ce.cell_size = upper(es.extent_size_name) and
> ce.cell_type = upper(et.extent_type_descr)
>UNION
>SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>FROM cells ce,
> cell_names cn,
> extent_size es,
> extent_type et
>WHERE ce.cell_id = cn.cell_id and
> cn.cell_name_current = 'Y' and
> ce.cell_size = upper(es.extent_size_name) and
> ce.cell_type IS NULL
>
>OR
>
>SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>FROM cells ce,
> cell_names cn,
> extent_size es,
> extent_type et
>WHERE ce.cell_id = cn.cell_id and
> cn.cell_name_current = 'Y' and
> ce.cell_size = upper(es.extent_size_name) and
> IsNull( ce.cell_type, upper(et.extent_type_descr) ) =
>upper(et.extent_type_descr)
>
>
>P.S. you should convert your SQL to ANSI SQL using JOINs
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>--------------------
>"M Hamilton" <mhamilton@usgs.gov> wrote in message
>news:3d359b7e$1@10.1.10.29...
>>
>> How would I write the query to pull the records from the following 2
>select
>> statements as 1 select statement? Notice that the last condition in each
>> WHERE clause is different.
>>
>> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> FROM cells ce,
>> cell_names cn,
>> extent_size es,
>> extent_type et
>> WHERE ce.cell_id = cn.cell_id and
>> cn.cell_name_current = 'Y' and
>> ce.cell_size = upper(es.extent_size_name) and
>> ce.cell_type = upper(et.extent_type_descr);
>>
>> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> FROM cells ce,
>> cell_names cn,
>> extent_size es,
>> extent_type et
>> WHERE ce.cell_id = cn.cell_id and
>> cn.cell_name_current = 'Y' and
>> ce.cell_size = upper(es.extent_size_name) and
>> ce.cell_type IS NULL;
>>
>> Thanks in advance. MH
>
>

MH,

David's advice is correct, but I though that it would be appropriate to explain
it a bit more. The two original suggestions give slightly different results.
Using the UNION causes the server to execute each query and then deliver
the results of both in the same result set. This I why you had to use the
DISTINCT in order to eliminate the duplicates. Because you do not want the
duplicates, the second example is much better because it creates the desired
result without the use of the DISTINCT keyword and only executes a single
query. Actually the example, using the OR in the where clause is the most
standard way of doing this because it does not use any server specific features.
The ISNULL() function is SQL Server specific.

It should be noted that the use of the UPPER function in the WHERE clause
will prevent the server from being able to use an index to search the 'extent_type'
and 'extent_size' tables. This may not be an issue if those table are small
enough, but it is important if the tables are large.
The UPPER() function is not needed if the server is installed as 'Non Case-Sensitive'
(default installation).

Jim

>> If I do not put the 'distinct' on the SELECT ce.cell_id portion, I get
>back
>> numerous duplicate rows of the 1 row that has the NULL value in it for
>cell_type,
>> while all the rest of the non-null valued rows would be returned
>correctly.
>> Strange!?

"David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>the 2nd set just used the IsNull() function to assign ce.cell_type to
>upper(et.extent_type_descr) if it is null
>
>Dave
>"M Hamilton" <mhamilton@usgs.gov> wrote in message
>news:3d36dd4d$1@10.1.10.29...
>>
>> Yes, thanks, the UNION does produce the correct result set. The second
>set
>> of code you recommended, I did not understand.
>>
>> Another set of code that I have found to work is:
>>
>> SELECT distinct (ce.cell_id), cn.cell_name, ce.cell_size, ce.cell_type
>> FROM cells ce,
>> cell_names cn,
>> extent_type et,
>> extent_size es
>> WHERE (ce.cell_id = cn.cell_id and
>> cn.cell_name_current = 'Y' and
>> ce.cell_size = upper(es.extent_size_name) )
>> and
>> (ce.cell_type = upper(et.extent_type_descr)
>> or
>> ce.cell_type is null);
>>
>> If I do not put the 'distinct' on the SELECT ce.cell_id portion, I get
>back
>> numerous duplicate rows of the 1 row that has the NULL value in it for
>cell_type,
>> while all the rest of the non-null valued rows would be returned
>correctly.
>> Strange!?
>>
>> Thanks. MH
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.NOSPAM.com> wrote:
>> >UNION:
>> >
>> >SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> >FROM cells ce,
>> > cell_names cn,
>> > extent_size es,
>> > extent_type et
>> >WHERE ce.cell_id = cn.cell_id and
>> > cn.cell_name_current = 'Y' and
>> > ce.cell_size = upper(es.extent_size_name) and
>> > ce.cell_type = upper(et.extent_type_descr)
>> >UNION
>> >SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> >FROM cells ce,
>> > cell_names cn,
>> > extent_size es,
>> > extent_type et
>> >WHERE ce.cell_id = cn.cell_id and
>> > cn.cell_name_current = 'Y' and
>> > ce.cell_size = upper(es.extent_size_name) and
>> > ce.cell_type IS NULL
>> >
>> >OR
>> >
>> >SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> >FROM cells ce,
>> > cell_names cn,
>> > extent_size es,
>> > extent_type et
>> >WHERE ce.cell_id = cn.cell_id and
>> > cn.cell_name_current = 'Y' and
>> > ce.cell_size = upper(es.extent_size_name) and
>> > IsNull( ce.cell_type, upper(et.extent_type_descr) ) =
>> >upper(et.extent_type_descr)
>> >
>> >
>> >P.S. you should convert your SQL to ANSI SQL using JOINs
>> >--
>> >HTH,
>> >David Satz
>> >Principal Web Engineer
>> >Hyperion Solutions
>> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >(Please reply to group only - emails answered rarely)
>> >--------------------
>> >"M Hamilton" <mhamilton@usgs.gov> wrote in message
>> >news:3d359b7e$1@10.1.10.29...
>> >>
>> >> How would I write the query to pull the records from the following
2
>> >select
>> >> statements as 1 select statement? Notice that the last condition in
>each
>> >> WHERE clause is different.
>> >>
>> >> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> >> FROM cells ce,
>> >> cell_names cn,
>> >> extent_size es,
>> >> extent_type et
>> >> WHERE ce.cell_id = cn.cell_id and
>> >> cn.cell_name_current = 'Y' and
>> >> ce.cell_size = upper(es.extent_size_name) and
>> >> ce.cell_type = upper(et.extent_type_descr);
>> >>
>> >> SELECT ce.cell_id, cn.cell_name, ce.cell_size, ce.cell_type
>> >> FROM cells ce,
>> >> cell_names cn,
>> >> extent_size es,
>> >> extent_type et
>> >> WHERE ce.cell_id = cn.cell_id and
>> >> cn.cell_name_current = 'Y' and
>> >> ce.cell_size = upper(es.extent_size_name) and
>> >> ce.cell_type IS NULL;
>> >>
>> >> Thanks in advance. MH
>> >
>> >
>>
>
>