[sqlite] Re: Combining queries

[Philippe BESSAGUET]

Sebastian Tennant <[EMAIL PROTECTED]>
wrote: 
> I have three tables; users, propositions, and subscriptions:
> 
>  users
>  -
>  user_id  user_name  user_password  user_email
> 
>  propostions
>  ---
>  prop_id proposition user_id
> 
>  subscriptions
>  -
>  sub_id  prop_id  user_id
> 
> Given a particular user, I want to return the set of 'interesting'
> propositions, i.e., those which were not proposed by the user and to
> which the user is not already subscribed...

select * from propositions p
where p.user_id != :userId and not exists (
select * from subscriptions s
where p.prop_id = s.prop_id and s.user_id = :userId
);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Combining queries

[Sebastian Tennant]

Quoth "Igor Tandetnik" <[EMAIL PROTECTED]>:
> Sebastian Tennant <[EMAIL PROTECTED]>
> wrote: 
>> I have three tables; users, propositions, and subscriptions:
>>
>>  users
>>  -
>>  user_id  user_name  user_password  user_email
>>
>>  propostions
>>  ---
>>  prop_id proposition user_id
>>
>>  subscriptions
>>  -
>>  sub_id  prop_id  user_id
>>
>> Given a particular user, I want to return the set of 'interesting'
>> propositions, i.e., those which were not proposed by the user and to
>> which the user is not already subscribed...
>
> select * from propositions p
> where p.user_id != :userId and not exists (
>select * from subscriptions s
>where p.prop_id = s.prop_id and s.user_id = :userId
> );
>
> Igor Tandetnik

Thank you _very_ much Igor.  I wasn't even close!  Now I need to sit
down and understand exactly why it works.

Sebastian

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Combining queries

[Igor Tandetnik]

Sebastian Tennant <[EMAIL PROTECTED]>
wrote: 

I have three tables; users, propositions, and subscriptions:

 users
 -
 user_id  user_name  user_password  user_email

 propostions
 ---
 prop_id proposition user_id

 subscriptions
 -
 sub_id  prop_id  user_id

Given a particular user, I want to return the set of 'interesting'
propositions, i.e., those which were not proposed by the user and to
which the user is not already subscribed...


select * from propositions p
where p.user_id != :userId and not exists (
   select * from subscriptions s
   where p.prop_id = s.prop_id and s.user_id = :userId
);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-