simple SQL: select missing rows

  • Thread starter Thread starter Lloyd Dupont
  • Start date Start date
L

Lloyd Dupont

I have some simple data like that

Product
--------
PR_Id (PK), Name
--------
1 Wall Clock
2 Nails

Invoice
-------
IN_id (PK)
-------
1
2

InvoiceLine
------------
LI_Id (PK), IN_id (FK in Invoice), PR_id (FK in Product)
------------
1 1 1
2 2 1
3 2 1


Now I'm trying to write some SQL that will get the Invoice with WallClock
and no Nails.

Any tips?
I'm stuck....
 
Lloyd said:
I have some simple data like that

Product
--------
PR_Id (PK), Name
--------
1 Wall Clock
2 Nails

Invoice
-------
IN_id (PK)
-------
1
2

InvoiceLine
------------
LI_Id (PK), IN_id (FK in Invoice), PR_id (FK in Product)
------------
1 1 1
2 2 1
3 2 1


Now I'm trying to write some SQL that will get the Invoice with
WallClock and no Nails.

Any tips?
I'm stuck....
Click to expand...

With these kind of questions, it's best if you post what you've
already tried. Now it looks like you're too lazy to try it yourself and
let us write the query for you.

But let's not get carried away with semantics here.

the query is a 'fetch x with filter on related entity' query. You have
a predicate where you want a test on another set. This is typically
done with a subquery. So your query is simply:
'Get me all invoices which have a wallclock and remove from that set
all invoices which have Nails.'. That's the final set you need :)

SELECT I.*
FROM Invoice I INNER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.PR_id = 1
AND IL.IN_id NOT IN
(
SELECT I.IN_id FROM Invoice I INNER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.PR_id = 2
)

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Hi Frans,

It's more like whatever I did I have no idea how to select invoice with no
invoice_line associated to nails.
I kind of forget my SQL these last 2 years and I'm trying to refresh my
memory.

Anyway....
thanks for your query!
But....
it still yields 0 line.

As a starting point that would be nice to be able to find all invoice
without nail.
I have no clue how to do that.


For example for a start that would be nice to be able to select all Invoice
without nail.
To show you that I'm trying below is a SQL query which looks like it's
trying to do that, excep it doesn't work (returns 0 line)

Any tips?

==== trying (and failing) to get all invoice with no nail =====
SELECT I.*, IL.*
FROM Invoice I LEFT OUTER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.IN_id is NULL
 
Lloyd said:
Hi Frans,

It's more like whatever I did I have no idea how to select invoice
with no invoice_line associated to nails. I kind of forget my SQL
these last 2 years and I'm trying to refresh my memory.

Anyway....
thanks for your query!
But....
it still yields 0 line.
strange.

As a starting point that would be nice to be able to find all invoice
without nail. I have no clue how to do that.

For example for a start that would be nice to be able to select all
Invoice without nail. To show you that I'm trying below is a SQL
query which looks like it's trying to do that, excep it doesn't work
(returns 0 line)

Any tips?

==== trying (and failing) to get all invoice with no nail =====
SELECT I.*, IL.*
FROM Invoice I LEFT OUTER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.IN_id is NULL
Click to expand...

this is a query which gives all invoices without invoicelines. :)
If you want all invoices which have no invoiceline with a nail, you
should do: select all invoices which aren't in the set of invoices
which have a nail. :)

Typically one would first think this would work:

SELECT I.*
FROM Invoice I INNER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.PR_id <> 2

however it doesn't. The reason is that this query is executed on each
row in the tables included and then checked if these rows match the
filter. If the IL row contains a wall clock, the row matches, and the
invoice is selected.

So instead do:
SELECT *
FROM Invoice
WHERE IN_id NOT IN
(
-- select invoices with nails
SELECT IN_id FROM InvoiceLine
WHERE PR_id=2
)

The inner query results in all invoiceID's which have nails. (run it
separately to see this)

The outer query selects all invoices which have an ID which isn't in
the set of id's which have nails. This results in the set of invoices
which don't have nails :)

FB



--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Indeed it works nicely.
Thanks very much Frans!

Frans Bouma said:
this is a query which gives all invoices without invoicelines. :)
If you want all invoices which have no invoiceline with a nail, you
should do: select all invoices which aren't in the set of invoices
which have a nail. :)

Typically one would first think this would work:

SELECT I.*
FROM Invoice I INNER JOIN InvoiceLine IL
ON I.IN_id = IL.IN_id
WHERE IL.PR_id <> 2

however it doesn't. The reason is that this query is executed on each
row in the tables included and then checked if these rows match the
filter. If the IL row contains a wall clock, the row matches, and the
invoice is selected.

So instead do:
SELECT *
FROM Invoice
WHERE IN_id NOT IN
(
-- select invoices with nails
SELECT IN_id FROM InvoiceLine
WHERE PR_id=2
)

The inner query results in all invoiceID's which have nails. (run it
separately to see this)

The outer query selects all invoices which have an ID which isn't in
the set of id's which have nails. This results in the set of invoices
which don't have nails :)

FB




--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
Click to expand...
 
Back
Top