Hello all, I need some help on setting up a query to solve the following.
Consider a table with this structure and these data:

ID    Location
1            1
2            1
3            1
4            1
5            1
1            2
2            2
3            2
1            3
2            3

I want to see all records with location = 1 minus / except those records
that have the same ID and Location <> 1. In this case it should return two
records with ID 4 and 5.

Can someone help out?

Thanks
Petter Topp
D7, Dbisam 4

Something like this should work.

select * from
  table
where
  Location = 1 and
  ID not in (select ID from table where Location <> 1)


Michael

Petter Topp wrote:
> Hello all, I need some help on setting up a query to solve the following.
> Consider a table with this structure and these data:
>
> ID    Location
> 1            1
> 2            1
> 3            1
> 4            1
> 5            1
> 1            2
> 2            2
> 3            2
> 1            3
> 2            3
>
> I want to see all records with location = 1 minus / except those records
> that have the same ID and Location <> 1. In this case it should return two
> records with ID 4 and 5.
>
> Can someone help out?
>
> Thanks
> Petter Topp
> D7, Dbisam 4
>
>
>
>
>

"Petter Topp" <petter.topp@atcdata.no> wrote in message
news:C7C039AB-D4B4-49D0-BD96-1560F3D631EF@news.elevatesoft.com...
> Hello all, I need some help on setting up a query to solve the following.

A subquery should do it, kind of slow in DBISAM but if the table is not too
big it might do the trick for you

SELECT FIELDS FROM TABLE T1 WHERE T1.LOCATION = 1 AND NOT T1.ID IN (SELECT
T2.ID FROM TABLE T2 WHERE T2.LOCATION <> 1)

Robert

> Consider a table with this structure and these data:
>
> ID    Location
> 1            1
> 2            1
> 3            1
> 4            1
> 5            1
> 1            2
> 2            2
> 3            2
> 1            3
> 2            3
>
> I want to see all records with location = 1 minus / except those records
> that have the same ID and Location <> 1. In this case it should return two
> records with ID 4 and 5.
>
> Can someone help out?
>
> Thanks
> Petter Topp
> D7, Dbisam 4
>
>
>
>
>

Thank you very much guys, I have never tried this before, looking forward
trying it.

Regards
Petter Topp


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i melding
news:FFE15574-4120-4310-B966-E2C699171663@news.elevatesoft.com...
>
> "Petter Topp" <petter.topp@atcdata.no> wrote in message
> news:C7C039AB-D4B4-49D0-BD96-1560F3D631EF@news.elevatesoft.com...
>> Hello all, I need some help on setting up a query to solve the following.
>
> A subquery should do it, kind of slow in DBISAM but if the table is not
> too big it might do the trick for you
>
> SELECT FIELDS FROM TABLE T1 WHERE T1.LOCATION = 1 AND NOT T1.ID IN (SELECT
> T2.ID FROM TABLE T2 WHERE T2.LOCATION <> 1)
>
> Robert
>
>> Consider a table with this structure and these data:
>>
>> ID    Location
>> 1            1
>> 2            1
>> 3            1
>> 4            1
>> 5            1
>> 1            2
>> 2            2
>> 3            2
>> 1            3
>> 2            3
>>
>> I want to see all records with location = 1 minus / except those records
>> that have the same ID and Location <> 1. In this case it should return
>> two records with ID 4 and 5.
>>
>> Can someone help out?
>>
>> Thanks
>> Petter Topp
>> D7, Dbisam 4
>>
>>
>>
>>
>>
>
>