Lloyd
Sun Nov 04 05:33:27 PST 2007
Indeed it works nicely.
Thanks very much Frans!
"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#)
> ------------------------------------------------------------------------