[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] Combining queries

[Sebastian Tennant]

Hi all,

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

  users
  -
  user_id user_name   user_password   user_email
  --- -   -   
--
  U01 mikescott   [EMAIL 
PROTECTED]
  U02 steve   wickham [EMAIL 
PROTECTED]
  U03 kevin   wilkinson   [EMAIL 
PROTECTED]
  U04 anthony thistlewaite[EMAIL 
PROTECTED]
  U05 karlwalinger[EMAIL 
PROTECTED]

  propostions
  ---
  prop_id proposition user_id
  --- ---
  P01 "Learn Chinese" U01
  P02 "Boycott U2"U02
  P03 "Come Back As A God"U03

  subscriptions
  -
  sub_id  prop_id  user_id
  --  ---  ---
  S01 P02  U01 --+
  S02 P02  U03   |--> Everyone has decided 
to boycott U2
  S03 P02  U04   |(Steve isn't listed 
because he proposed it)
  S04 P02  U05 --+
  S05 P01  U02 -> Steve has decided to 
learn Chinese
  S06 P03  U05 -> Karl wants to come 
back as a God

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...

For example, if Kevin performs the query, his only 'interesting'
proposition is to "learn Chinese".

The propositions marked *Interesting* constitute the desired result
set for each user:

  "Learn Chinese" "Boycott U2" "Come Back As A God"
   -   --   --
  Mike ProposedSubscribed   *Interesting*

  SteveSubscribed  Proposed *Interesting*

  Kevin*Interesting*   Subscribed   Proposed

  Anthony  *Interesting*   Subscribed   *Interesting*

  Karl *Interesting*   Subscribed   Subscribed

In short, returning to Kevin, I want to construct a single SQL query
that effectively subtracts B from A:

A: SELECT prop_id FROM propositions WHERE ( user_id != U03 )

prop_id
---
P01Kevin can subscribe to these
P02(because he didn't propose them).

B: SELECT prop_id FROM subscriptions WHERE (user_id = U03 )

prop_id

P02 He can't subscribe to this one
(becuase he is already subscribed to it.)

Any help very much appreciated.

Sebastian

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

[sqlite] Importing data without a primary key

[Sebastian Tennant]

Hi all,

I have a CSV file of data that doesn't include a column suitable to be the 
primary key.  Should I 
edit the CSV data file before importing it (a pain to do), or is there an easy 
way to create (and 
populate) a primary key column once the data has been imported?

Any suggestions much appreciated.

Sebastian

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