#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0

    complex (for me) sql join query


    Hey guys,

    I've been battling with this on paper, in mysql and in my head for a few days, and cannot find any suitable help online. Mainly because it's hard to search for SQL help when you can't describe the problem in 10 words or less

    Here's the scenario:

    I have a table of invoices, and a table of action types. Then there's a table of actions performed.

    abstracted design and dummy content is:

    table: invoice (id)
    row 1: (1)
    row 2: (2)

    table: invoice_action (invoiceid, actionid)
    row 1: (1, 1)
    row 2: (1, 2)
    row 3: (2, 1)

    table: invoice_action_value (id, name);
    row 1: (1, 'created')
    row 2: (2, 'sent');

    What I want to do is select all invoices where the invoice has an actionid=1, but not an actionid=2 associated with it. I'm looking for all invoices that have been created but not sent yet. In this example, returning invoice 2.

    From a logical point of view you could say, if it exists, it's been created. But in the larger view I want to seach for all invoices that have been created and sent, are not due but paid and cleared in the bank. For example.

    So I know we can elimiate invoice and invoice_action_value from the first draft sql query, as all the info I need is in invoice_action, but I'm having problems getting the join working in my head on multiple instances of the invoice_action table...

    Many thanks in advance for your help,

    Ben
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    select * from invoice i
    where not exists(
    select * from invoice_action
    where invoideid = i.id
        and actionid = 2)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0
    Thanks, unfortunately:

    You have an error in your SQL syntax near 'exists(

    This is not a valid MySQL function I guess :-)

    B
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    You are correct in your surmise. Since you did not post in the Mysql fourm I assumed that you used a database that supported basic sql.

    Code:
    select i.* from invoice i
    left join invoice_action ia
    on ia.invoiceid = i.id
        and actionid = 2
    where ia.invoiceid is null
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    6
    Rep Power
    0
    Thanks for your help, I'm sure I had tried something along that line, but I think my head started hurting long before then.

IMN logo majestic logo threadwatch logo seochat tools logo