March 5th, 2003, 02:18 AM
complex (for me) sql join query
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,
March 5th, 2003, 02:46 AM
select * from invoice i
where not exists(
select * from invoice_action
where invoideid = i.id
and actionid = 2)
March 5th, 2003, 03:03 AM
You have an error in your SQL syntax near 'exists(
This is not a valid MySQL function I guess :-)
March 5th, 2003, 04:29 AM
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.
select i.* from invoice i
left join invoice_action ia
on ia.invoiceid = i.id
and actionid = 2
where ia.invoiceid is null
March 5th, 2003, 10:38 PM
Thanks for your help, I'm sure I had tried something along that line, but I think my head started hurting long before then.