may be I should. Thanks. "Jacco Schalkwijk" wrote in message news:%23lc5jf8RDHA.2144@TK2MSFTNGP11.phx.gbl...[quote] > Hey, I said "strictly speaking" :-) > I prefer non-changing primary keys as much as the next man (as long as[/quote] that[quote] > man is not Joe Celko ;)) > > -- > Jacco Schalkwijk MCDBA, MCSD, MCSE > Database Administrator > Eurostop Ltd. > > > "Aaron Bertrand - MVP" wrote in message > news:ungZdX8RDHA.1688@TK2MSFTNGP11.phx.gbl...[quote][quote] > > > Btw Aaron, regarding your first answer, strictly speaking you don't[/quote][/quote][/quote] have[quote][quote] > > to[quote] > > > keep the data up to date in two places if you have a foreign key with[/quote][/quote][/quote] On[quote][quote][quote] > > > Cascade Update between the tables,[/quote] > > > > Eeewww, would you really do that? Blecch. :-) > > > >[/quote] > > >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <#4649N7RDHA.2148@TK2MSFTNGP10.phx.gbl> <6GzPa.9245$Fc5.2713380937@newssvr10.news.prodigy.com> <#ZhP8U8RDHA.2432@TK2MSFTNGP10.phx.gbl> <#lc5jf8RDHA.2144@TK2MSFTNGP11.phx.gbl> [htmlstate] => on_nl2br [postusername] => Jay [ip] => jayesh131@hotma [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 11 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Best way to filter? - Microsoft SQL / MS SQL Server

Best way to filter? - Microsoft SQL / MS SQL Server

Lets say we have two tables (1) VIPCustomers and (2) Invoices with the usual data. I have the following valid statement: SELECT A.* FROM Invoices A INNER JOIN VIPCustomers B ON A.FirstName = B.FirstName AND A.LastName = B.LastName Q. How would I do the same using the "IN" clause or is there a simpler way? Note that I am not using any fields from table VIPCustomers in the SELECT clause. The thing is that many times I want to filter based on multiple tables. This results in multiple INNER JOIN statements and plus I may have some derived tables. This ...

  1. #1

    Default Best way to filter?


    Lets say we have two tables (1) VIPCustomers and (2) Invoices with the usual
    data.

    I have the following valid statement:

    SELECT A.*
    FROM Invoices A INNER JOIN VIPCustomers B
    ON A.FirstName = B.FirstName AND A.LastName = B.LastName


    Q. How would I do the same using the "IN" clause or is there a simpler way?
    Note that I am not using any fields from table VIPCustomers in the SELECT
    clause.


    The thing is that many times I want to filter based on multiple tables. This
    results in multiple INNER JOIN statements and plus I may have some derived
    tables. This makes my statement look confusing and complex when in reality
    it is not. I am just trying to simplify these statements such that they are
    easy to read. I feel that the single column based IN clause pushes the
    filter to the end and makes the core area of the statement easy to read.

    Actually, any suggestions are welcome.


    Thanks,
    Jay



    Jay Guest

  2. #2

    Default Re: Best way to filter?

    My first question would be why are you storing the name of the customer in
    the invoice table ? An Invoice is about an invoice not a customer. A
    customer is likely to have 0+ invoices so a preferred structure would be to
    hold an FK ref to the customer on the Invoices table.

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    [url]www.SQLDTS.com[/url]
    I support PASS - the definitive, global community
    for SQL Server professionals - [url]http://www.sqlpass.org[/url]

    "Jay" <jayesh131hotmail.com> wrote in message
    news:d%yPa.9231$PP4.2711796855newssvr10.news.prod igy.com...
    >
    > Lets say we have two tables (1) VIPCustomers and (2) Invoices with the
    usual
    > data.
    >
    > I have the following valid statement:
    >
    > SELECT A.*
    > FROM Invoices A INNER JOIN VIPCustomers B
    > ON A.FirstName = B.FirstName AND A.LastName = B.LastName
    >
    >
    > Q. How would I do the same using the "IN" clause or is there a simpler
    way?
    > Note that I am not using any fields from table VIPCustomers in the SELECT
    > clause.
    >
    >
    > The thing is that many times I want to filter based on multiple tables.
    This
    > results in multiple INNER JOIN statements and plus I may have some derived
    > tables. This makes my statement look confusing and complex when in reality
    > it is not. I am just trying to simplify these statements such that they
    are
    > easy to read. I feel that the single column based IN clause pushes the
    > filter to the end and makes the core area of the statement easy to read.
    >
    > Actually, any suggestions are welcome.
    >
    >
    > Thanks,
    > Jay
    >
    >
    >

    Allan Mitchell Guest

  3. #3

    Default Re: Best way to filter?

    > SELECT A.*
    > FROM Invoices A INNER JOIN VIPCustomers B
    > ON A.FirstName = B.FirstName AND A.LastName = B.LastName
    Uck... never mind that you could have two users named John Smith, why would
    you store character data twice? Doesn't it seem risky, given that a name
    can change (now you have to change it in two places).


    Aaron Bertrand - MVP Guest

  4. #4

    Default Re: Best way to filter?

    Thanks for the reply. In fact, I just made up this example (instead of using
    the mathematical field names that I have in my actual database). Anyway, my
    question is not really related to this specific example. Basically, how
    would you apply a multiple field based filter (of the type in my example)
    without using an INNER JOIN statement?

    Thanks,
    Jay





    "Allan Mitchell" <allanno-spam.sqldts.com> wrote in message
    news:%234649N7RDHA.2148TK2MSFTNGP10.phx.gbl...
    > My first question would be why are you storing the name of the customer in
    > the invoice table ? An Invoice is about an invoice not a customer. A
    > customer is likely to have 0+ invoices so a preferred structure would be
    to
    > hold an FK ref to the customer on the Invoices table.
    >
    > --
    >
    >
    > Allan Mitchell (Microsoft SQL Server MVP)
    > MCSE,MCDBA
    > [url]www.SQLDTS.com[/url]
    > I support PASS - the definitive, global community
    > for SQL Server professionals - [url]http://www.sqlpass.org[/url]
    >
    > "Jay" <jayesh131hotmail.com> wrote in message
    > news:d%yPa.9231$PP4.2711796855newssvr10.news.prod igy.com...
    > >
    > > Lets say we have two tables (1) VIPCustomers and (2) Invoices with the
    > usual
    > > data.
    > >
    > > I have the following valid statement:
    > >
    > > SELECT A.*
    > > FROM Invoices A INNER JOIN VIPCustomers B
    > > ON A.FirstName = B.FirstName AND A.LastName = B.LastName
    > >
    > >
    > > Q. How would I do the same using the "IN" clause or is there a simpler
    > way?
    > > Note that I am not using any fields from table VIPCustomers in the
    SELECT
    > > clause.
    > >
    > >
    > > The thing is that many times I want to filter based on multiple tables.
    > This
    > > results in multiple INNER JOIN statements and plus I may have some
    derived
    > > tables. This makes my statement look confusing and complex when in
    reality
    > > it is not. I am just trying to simplify these statements such that they
    > are
    > > easy to read. I feel that the single column based IN clause pushes the
    > > filter to the end and makes the core area of the statement easy to read.
    > >
    > > Actually, any suggestions are welcome.
    > >
    > >
    > > Thanks,
    > > Jay
    > >
    > >
    > >
    >
    >

    Jay Guest

  5. #5

    Default Re: Best way to filter?

    I do not see the reason why I would need to. If I can use a JOIN condition
    then this will most probably result in a better query plan and faster
    exacution than the IN version of the same Query

    You can, if you must use

    .................. WHERE col1 + '|' + Col2 IN (SELECT col8 + '|' + col9 FROM
    ......................)

    Not pretty

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    [url]www.SQLDTS.com[/url]
    I support PASS - the definitive, global community
    for SQL Server professionals - [url]http://www.sqlpass.org[/url]

    "Jay" <jayesh131hotmail.com> wrote in message
    news:6GzPa.9245$Fc5.2713380937newssvr10.news.prod igy.com...
    > Thanks for the reply. In fact, I just made up this example (instead of
    using
    > the mathematical field names that I have in my actual database). Anyway,
    my
    > question is not really related to this specific example. Basically, how
    > would you apply a multiple field based filter (of the type in my example)
    > without using an INNER JOIN statement?
    >
    > Thanks,
    > Jay
    >
    >
    >
    >
    >
    > "Allan Mitchell" <allanno-spam.sqldts.com> wrote in message
    > news:%234649N7RDHA.2148TK2MSFTNGP10.phx.gbl...
    > > My first question would be why are you storing the name of the customer
    in
    > > the invoice table ? An Invoice is about an invoice not a customer. A
    > > customer is likely to have 0+ invoices so a preferred structure would be
    > to
    > > hold an FK ref to the customer on the Invoices table.
    > >
    > > --
    > >
    > >
    > > Allan Mitchell (Microsoft SQL Server MVP)
    > > MCSE,MCDBA
    > > [url]www.SQLDTS.com[/url]
    > > I support PASS - the definitive, global community
    > > for SQL Server professionals - [url]http://www.sqlpass.org[/url]
    > >
    > > "Jay" <jayesh131hotmail.com> wrote in message
    > > news:d%yPa.9231$PP4.2711796855newssvr10.news.prod igy.com...
    > > >
    > > > Lets say we have two tables (1) VIPCustomers and (2) Invoices with the
    > > usual
    > > > data.
    > > >
    > > > I have the following valid statement:
    > > >
    > > > SELECT A.*
    > > > FROM Invoices A INNER JOIN VIPCustomers B
    > > > ON A.FirstName = B.FirstName AND A.LastName = B.LastName
    > > >
    > > >
    > > > Q. How would I do the same using the "IN" clause or is there a simpler
    > > way?
    > > > Note that I am not using any fields from table VIPCustomers in the
    > SELECT
    > > > clause.
    > > >
    > > >
    > > > The thing is that many times I want to filter based on multiple
    tables.
    > > This
    > > > results in multiple INNER JOIN statements and plus I may have some
    > derived
    > > > tables. This makes my statement look confusing and complex when in
    > reality
    > > > it is not. I am just trying to simplify these statements such that
    they
    > > are
    > > > easy to read. I feel that the single column based IN clause pushes
    the
    > > > filter to the end and makes the core area of the statement easy to
    read.
    >
    > > >
    > > > Actually, any suggestions are welcome.
    > > >
    > > >
    > > > Thanks,
    > > > Jay
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Allan Mitchell Guest

  6. #6

    Default Re: Best way to filter?

    > You can, if you must use
    >
    > ................. WHERE col1 + '|' + Col2 IN (SELECT col8 + '|' + col9
    FROM
    > .....................)
    >
    > Not pretty
    And it gets much uglier when they aren't n/var/char datatypes.


    Aaron Bertrand - MVP Guest

  7. #7

    Default Re: Best way to filter?

    Agreed which is why JOIN is by far the best solution

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    [url]www.SQLDTS.com[/url]
    I support PASS - the definitive, global community
    for SQL Server professionals - [url]http://www.sqlpass.org[/url]

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:eIOITz7RDHA.1624tk2msftngp13.phx.gbl...
    > > You can, if you must use
    > >
    > > ................. WHERE col1 + '|' + Col2 IN (SELECT col8 + '|' + col9
    > FROM
    > > .....................)
    > >
    > > Not pretty
    >
    > And it gets much uglier when they aren't n/var/char datatypes.
    >
    >

    Allan Mitchell Guest

  8. #8

    Default Re: Best way to filter?

    You could use
    SELECT <columnlist> FROM thistable
    WHERE EXISTS(SELECT * FROM othertable WHERE othertable.col8 = thistable.col1
    and othertable.col9 = thistable.col2), which might perform better than a
    join if the combination of col8 and col9 is not unique. Jay mentioned that
    his example had little to do with his real database, so this might be the
    case.

    Btw Aaron, regarding your first answer, strictly speaking you don't have to
    keep the data up to date in two places if you have a foreign key with On
    Cascade Update between the tables, but as you already pointed out the
    combination of firstname and lastname has to be unique for that. I agree of
    course with all your other comments.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Allan Mitchell" <allanno-spam.sqldts.com> wrote in message
    news:u61VUK8RDHA.3700tk2msftngp13.phx.gbl...
    > Agreed which is why JOIN is by far the best solution
    >
    > --
    >
    >
    > Allan Mitchell (Microsoft SQL Server MVP)
    > MCSE,MCDBA
    > [url]www.SQLDTS.com[/url]
    > I support PASS - the definitive, global community
    > for SQL Server professionals - [url]http://www.sqlpass.org[/url]
    >
    > "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    > news:eIOITz7RDHA.1624tk2msftngp13.phx.gbl...
    > > > You can, if you must use
    > > >
    > > > ................. WHERE col1 + '|' + Col2 IN (SELECT col8 + '|' + col9
    > > FROM
    > > > .....................)
    > > >
    > > > Not pretty
    > >
    > > And it gets much uglier when they aren't n/var/char datatypes.
    > >
    > >
    >
    >

    Jacco Schalkwijk Guest

  9. #9

    Default Re: Best way to filter?

    > Btw Aaron, regarding your first answer, strictly speaking you don't have
    to
    > keep the data up to date in two places if you have a foreign key with On
    > Cascade Update between the tables,
    Eeewww, would you really do that? Blecch. :-)


    Aaron Bertrand - MVP Guest

  10. #10

    Default Re: Best way to filter?

    Hey, I said "strictly speaking" :-)
    I prefer non-changing primary keys as much as the next man (as long as that
    man is not Joe Celko ;))

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:ungZdX8RDHA.1688TK2MSFTNGP11.phx.gbl...
    > > Btw Aaron, regarding your first answer, strictly speaking you don't have
    > to
    > > keep the data up to date in two places if you have a foreign key with On
    > > Cascade Update between the tables,
    >
    > Eeewww, would you really do that? Blecch. :-)
    >
    >

    Jacco Schalkwijk Guest

  11. #11

    Default Re: Best way to filter?

    Ok. I think "EXISTS" would work too. I will use EXISTS or INNER JOIN based
    and the specific situation. I guess I could have multiple EXISTS clauses
    too! I usually never use EXISTS --> may be I should.

    Thanks.



    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:%23lc5jf8RDHA.2144TK2MSFTNGP11.phx.gbl...
    > Hey, I said "strictly speaking" :-)
    > I prefer non-changing primary keys as much as the next man (as long as
    that
    > man is not Joe Celko ;))
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    > news:ungZdX8RDHA.1688TK2MSFTNGP11.phx.gbl...
    > > > Btw Aaron, regarding your first answer, strictly speaking you don't
    have
    > > to
    > > > keep the data up to date in two places if you have a foreign key with
    On
    > > > Cascade Update between the tables,
    > >
    > > Eeewww, would you really do that? Blecch. :-)
    > >
    > >
    >
    >
    >

    Jay Guest

Similar Threads

  1. about filter
    By yiyun0918@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 1
    Last Post: December 24th, 01:26 PM
  2. #40499 [NEW]: filter sapi does not register any highlightning filter
    By php at henke37 dot cjb dot net in forum PHP Bugs
    Replies: 0
    Last Post: February 15th, 06:31 PM
  3. Replies: 3
    Last Post: September 12th, 04:18 PM
  4. CSS Filter
    By cadenceguitar in forum Dreamweaver AppDev
    Replies: 3
    Last Post: March 16th, 08:52 PM
  5. XML Filter
    By rpjoseph in forum ASP Database
    Replies: 2
    Last Post: November 6th, 10:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •