simple SQL: select missing rows

microsoft.public.dotnet.framework.adonet

3 Nov 2007 8:48 AM

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

3 Nov 2007 10:06 AM

Frans Bouma [C# MVP]

Lloyd Dupont wrote:

Show quote

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

    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#)
------------------------------------------------------------------------

4 Nov 2007 12:31 AM

Lloyd Dupont

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

Show quote

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
news:xn0fd97tl47fdr000@news.microsoft.com...
> Lloyd Dupont wrote:
>
>> 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....
>
> 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#)
> ------------------------------------------------------------------------

4 Nov 2007 12:50 PM

Frans Bouma [C# MVP]

Lloyd Dupont wrote:

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

Show quote

> 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

    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

Show quote

>
>
>
> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
> message news:xn0fd97tl47fdr000@news.microsoft.com...
> > Lloyd Dupont wrote:
> >
> > > 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....
> >
> > 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
> > )

4 Nov 2007 1:33 PM

Lloyd Dupont

Indeed it works nicely.
Thanks very much Frans!

Show quote

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
news:xn0fdaqml6t30b000@news.microsoft.com...
> Lloyd Dupont wrote:
>
>> 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
>
> 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
>
>
>>
>>
>>
>> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
>> message news:xn0fd97tl47fdr000@news.microsoft.com...
>> > Lloyd Dupont wrote:
>> >
>> > > 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....
>> >
>> > 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
>> > )
>
>
> --
> ------------------------------------------------------------------------
> 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#)
> ------------------------------------------------------------------------