SQL query - Get one record if it exists else get another one

SQL query - Get one record if it exists else get another one

Post by Curtis Brow » Fri, 16 Jan 1998 04:00:00



I have a table containing fees charged at various airports for a
variety of aircraft types.  The pertinent field then are

   Apt - Airport (ie SAT)
   ACT - Aircraft Type (ie LR-24)
   CT  - Charge Type (ie RAMP)
   Fee - Fee amount ($)

Some charges are the same for all/most aircrafts and so there is a
possible record with ACT = '<DFLT>'.  Given the following records, I
want to get back records 1 and 3.

  Apt  ACT   CT    Fee
1 SAT LR-24  LF    50.00
2 SAT <DFLT> LF    45.00
3 SAT <DFLT> RAMP  70.00
4 STL LR-24  LR    75.00
5 STL LR-24  RAMP  30.00
6 STL <DFLT> SRVC  50.00
...

  Can anyone please help me construct a query that will return just
records 1 & 3 rather than having to retrieve 1, 2, and 3 and using
additional code to filter out the unneeded default record 2?

thank you,

Curtis.

 
 
 

SQL query - Get one record if it exists else get another one

Post by Roy Harv » Fri, 16 Jan 1998 04:00:00


Curtis,

SELECT *
  FROM tbl T1
 WHERE Apt = 'SAT'
   AND ACT = 'LR-24'
UNION ALL
SELECT *
  FROM tbl T2
 WHERE Apt = 'SAT'
   AND ACT = '<DFLT>'
   AND NOT EXISTS (SELECT * FROM tbl T3
                    WHERE T2.Apt = T3.Apt
                      AND T2.CT = T3.CT
                      AND T3.ACT = 'LR024')

or

SELECT *
  FROM tbl T1
 WHERE Apt = 'SAT'
   AND ACT IN ('LR-24', '<DFLT>')
   AND CASE
         WHEN ACT = 'LR24' THEN 1
         WHEN EXISTS(SELECT * FROM tbl T2
                      WHERE T1.Apt = T2.Apt
                        AND T1.CT = T2.CT
                        AND T2.ACT = 'LR024')
           THEN 0
         ELSE 1
       END = 1

Roy


>I have a table containing fees charged at various airports for a
>variety of aircraft types.  The pertinent field then are

>   Apt - Airport (ie SAT)
>   ACT - Aircraft Type (ie LR-24)
>   CT  - Charge Type (ie RAMP)
>   Fee - Fee amount ($)

>Some charges are the same for all/most aircrafts and so there is a
>possible record with ACT = '<DFLT>'.  Given the following records, I
>want to get back records 1 and 3.

>  Apt  ACT   CT    Fee
>1 SAT LR-24  LF    50.00
>2 SAT <DFLT> LF    45.00
>3 SAT <DFLT> RAMP  70.00
>4 STL LR-24  LR    75.00
>5 STL LR-24  RAMP  30.00
>6 STL <DFLT> SRVC  50.00
>...

>  Can anyone please help me construct a query that will return just
>records 1 & 3 rather than having to retrieve 1, 2, and 3 and using
>additional code to filter out the unneeded default record 2?

>thank you,

>Curtis.


 
 
 

SQL query - Get one record if it exists else get another one

Post by Curtis Brow » Fri, 16 Jan 1998 04:00:00


Roy,

  A tremendous thank you.  I tried both and they work great.  I am
curious though what the final '= 1' is for after the CASE's END in the
second query?  Also, are these pretty equal queries or would you suggest
one over the other for efficiency (and readability)?

Curtis.


> Curtis,

> SELECT *
>   FROM tbl T1
>  WHERE Apt = 'SAT'
>    AND ACT = 'LR-24'
> UNION ALL
> SELECT *
>   FROM tbl T2
>  WHERE Apt = 'SAT'
>    AND ACT = '<DFLT>'
>    AND NOT EXISTS (SELECT * FROM tbl T3
>                     WHERE T2.Apt = T3.Apt
>                       AND T2.CT = T3.CT
>                       AND T3.ACT = 'LR024')

> or

> SELECT *
>   FROM tbl T1
>  WHERE Apt = 'SAT'
>    AND ACT IN ('LR-24', '<DFLT>')
>    AND CASE
>          WHEN ACT = 'LR24' THEN 1
>          WHEN EXISTS(SELECT * FROM tbl T2
>                       WHERE T1.Apt = T2.Apt
>                         AND T1.CT = T2.CT
>                         AND T2.ACT = 'LR024')
>            THEN 0
>          ELSE 1
>        END = 1

> Roy


> >I have a table containing fees charged at various airports for a
> >variety of aircraft types.  The pertinent field then are

> >   Apt - Airport (ie SAT)
> >   ACT - Aircraft Type (ie LR-24)
> >   CT  - Charge Type (ie RAMP)
> >   Fee - Fee amount ($)

> >Some charges are the same for all/most aircrafts and so there is a
> >possible record with ACT = '<DFLT>'.  Given the following records, I
> >want to get back records 1 and 3.

> >  Apt  ACT   CT    Fee
> >1 SAT LR-24  LF    50.00
> >2 SAT <DFLT> LF    45.00
> >3 SAT <DFLT> RAMP  70.00
> >4 STL LR-24  LR    75.00
> >5 STL LR-24  RAMP  30.00
> >6 STL <DFLT> SRVC  50.00
> >...

> >  Can anyone please help me construct a query that will return just
> >records 1 & 3 rather than having to retrieve 1, 2, and 3 and using
> >additional code to filter out the unneeded default record 2?

> >thank you,

> >Curtis.

 
 
 

SQL query - Get one record if it exists else get another one

Post by Roy Harv » Sat, 17 Jan 1998 04:00:00


Curtis,

Quote:>  A tremendous thank you.  I tried both and they work great.  I am
>curious though what the final '= 1' is for after the CASE's END in the
>second query?

Any CASE expression resolves to a single value.  What this CASE
expression does is resolve to a 1 when you want the row, and to a 0
when you don't.  The "= 1" at the end is testing the result of the
CASE to see if the row should be included or not.

Quote:>Also, are these pretty equal queries or would you suggest
>one over the other for efficiency (and readability)?

The UNION is perhaps a bit clearer.  I have no clear idea which would
perform better, but the UNION has less chance of performing badly.

Roy


>> Curtis,

>> SELECT *
>>   FROM tbl T1
>>  WHERE Apt = 'SAT'
>>    AND ACT = 'LR-24'
>> UNION ALL
>> SELECT *
>>   FROM tbl T2
>>  WHERE Apt = 'SAT'
>>    AND ACT = '<DFLT>'
>>    AND NOT EXISTS (SELECT * FROM tbl T3
>>                     WHERE T2.Apt = T3.Apt
>>                       AND T2.CT = T3.CT
>>                       AND T3.ACT = 'LR024')

>> or

>> SELECT *
>>   FROM tbl T1
>>  WHERE Apt = 'SAT'
>>    AND ACT IN ('LR-24', '<DFLT>')
>>    AND CASE
>>          WHEN ACT = 'LR24' THEN 1
>>          WHEN EXISTS(SELECT * FROM tbl T2
>>                       WHERE T1.Apt = T2.Apt
>>                         AND T1.CT = T2.CT
>>                         AND T2.ACT = 'LR024')
>>            THEN 0
>>          ELSE 1
>>        END = 1

>> Roy


>> >I have a table containing fees charged at various airports for a
>> >variety of aircraft types.  The pertinent field then are

>> >   Apt - Airport (ie SAT)
>> >   ACT - Aircraft Type (ie LR-24)
>> >   CT  - Charge Type (ie RAMP)
>> >   Fee - Fee amount ($)

>> >Some charges are the same for all/most aircrafts and so there is a
>> >possible record with ACT = '<DFLT>'.  Given the following records, I
>> >want to get back records 1 and 3.

>> >  Apt  ACT   CT    Fee
>> >1 SAT LR-24  LF    50.00
>> >2 SAT <DFLT> LF    45.00
>> >3 SAT <DFLT> RAMP  70.00
>> >4 STL LR-24  LR    75.00
>> >5 STL LR-24  RAMP  30.00
>> >6 STL <DFLT> SRVC  50.00
>> >...

>> >  Can anyone please help me construct a query that will return just
>> >records 1 & 3 rather than having to retrieve 1, 2, and 3 and using
>> >additional code to filter out the unneeded default record 2?

>> >thank you,

>> >Curtis.

 
 
 

1. Help: Getting VB6 and SQL to Match and Return More than One D/B Record

Okay - so I have built this great program which interacts in numerous ways
with an Access d/b however I have discovered that I can only return one
record. For example in the database I have the following records

Surname: Jones, First_Name:David
Surname: Jones, First_Name:Kate

If I search for surname Jones my program only returns data related to the
first record it finds, in this case David Jones, Kate Jones is ignored. How
do I get VB6 / SQL to return all the records which match the WHERE field,
i.e. how do I get David Jones data AND Kate Jones data back?

I'm really stuck on this one - any help / advice would be most welcome
cheers guys
dave

2. Looking for folks with exp. in designing multimedia databases

3. Getting one unique updateable record

4. Database searches

5. Getting only one record

6. Creating Report off Pass Through Query

7. Bi-directional replication

8. Updating records and getting more than one value

9. selecting a record in a DBGrid and always getting the first one

10. Getting records from one DB to another...

11. getting forms 4.0 to execute a query in one click

12. Queries that return one and only one master record