Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    SQL weakness and errors on database processing

    SQL weakness and errors on database processing at SPAM LINK
    Last edited by blindman; 01-02-08 at 11:16.

  2. #2
    Join Date
    Jan 2003
    Posts
    5
    I want to share with you all some practical posts related to:

    Problems with EXISTS, ALL and INTERSECT
    Meanings of null

    in this site SPAM LINK

    And soon the complete papers.
    Last edited by blindman; 01-02-08 at 11:16.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, oswaldo, i moved your thread to the Database Concepts forum

    please note: many of us on this forum are very happy with sql, and some of us are especially fond of nulls

    so be careful, okay?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by r937
    thanks, oswaldo, i moved your thread to the Database Concepts forum

    please note: many of us on this forum are very happy with sql, and some of us are especially fond of nulls

    so be careful, okay?
    hi,

    i am facing some problem on using nulls value, can you please advice what is the thing we need to consider when use allow null field for comparison (during join) and sum...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    GongXi, please show your details

    because to answer your question based on the information you've provided, the answer is: "you need to consider that NULL is not equal to anything, not even another NULL, and that aggregate functions like SUM() ignore NULLs"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by GongXi
    i am facing some problem on using nulls value, can you please advice what is the thing we need to consider when use allow null field for comparison (during join) and sum...
    In the Database Concepts forum we can discuss this, but it will be at a pretty high (theoretical) level. I sense from the way you phrased your question that you are more interested in the practical details.

    As r937 suggested, we really need some specific details to work from unless you really want "database platitudes" that go into the theoretical basis of handling NULL values within SQL.

    -PatP

  7. #7
    Join Date
    Jun 2004
    Posts
    57
    I am sorry for not my question clear.

    I am not sure whether my table design in correct way or not.
    For example, we have one table which contain my customer company information.
    Example:
    Table Name: Customer
    CustCode
    CustName
    CustAddress
    CustPhone

    and our customer some will have branch, and we use another table to store
    Table Name: CustBranch
    CustCode
    BranchCode
    BranchName
    BranchAddress
    BranchPhone

    Our customer are able to post their advertisement for their HQ or Branch, we have one table to store all this information
    Table Name: CustAds
    CustCode (foreign key)
    BranchCode(foreign key, allow null)
    AdsDate
    AdsDetail

    for those advertisment which is post by HQ, we set null for BranchCode.

    how i can list all advertisment info with HQ/Branch address & phone?
    Means, if the advertisement is post for HQ then show Customer info, if is post for Branch, then show branch information.

    however, i really want to know what is the advantage of using null, i still new to all this, so i hope to learn more.

    very appreciate on your help.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by GongXi
    I am sorry for not my question clear.
    it is clear enough, don't worry

    Quote Originally Posted by GongXi
    I am not sure whether my table design in correct way or not.
    it is good

    Quote Originally Posted by GongXi
    how i can list all advertisment info with HQ/Branch address & phone?
    like this --
    Code:
    select CustAds.AdsDate
         , CustAds.AdsDetail 
         , 'customer'          as adtype
         , CustAds.CustName    as Name 
         , CustAds.CustAddress as Address
         , CustAds.CustPhone   as Phone
      from CustAds
    inner
      join Customer
        on Customer.CustCode = CustAds.CustCode
    union all
    select CustAds.AdsDate
         , CustAds.AdsDetail 
         , 'branch'             as adtype
         , CustBranch.BranchName    as Name 
         , CustBranch.BranchAddress as Address
         , CustBranch.BranchPhone   as Phone
      from CustAds
    inner
      join CustBranch
        on CustBranch.BranchCode = CustAds.BranchCode
    Quote Originally Posted by GongXi
    however, i really want to know what is the advantage of using null, i still new to all this, so i hope to learn more.
    you're doing fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2003
    Posts
    5

    About null

    Hi,

    I advise you to see the post "SOME MEANINGS OF NULL" at www.SPAMLINK.com, there you can find some helpful hints about nulls.
    Last edited by blindman; 01-02-08 at 11:17.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ofique, perhaps you should give the more specific link Some Meanings of NULL instead

    your article basically says that

    1. NULL can be either "missing" or "inapplicable"

    2. withdrawals not made through an ATM can't have an ATM foreign key and therefore it must be NULLable

    3. bank accounts that do not belong to a bank (??) cannot be included in queries which compute account balances per bank

    i think your article should actually go further, and give suggestions for fixing situations 2. and 3.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2003
    Posts
    5
    In "SOME MEANINGS OF NULL" the point is the following:

    In SQL we should have a way to differentiate null, I mean the result of a SQL query should be divided into three one for TRUE result, other for FALSE result and, of course, another for null result, whatever this mark means in the context.

    maybe the examples in the article are a little confusing but I think the point mentioned is important.

    Thanks

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i certainly agree with you, understanding NULL is very important

    have you seen this? Nulls - A Socratic Enquiry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    GonXi,

    I've just taken a quick look at your table structure, and something strikes me as... odd

    It's regarding your table "CustBranch"
    The name (to me) suggests that it should be an interim table in a many-to-many relationship between customers and branch. The design suggests (see last parenthesis) that one customer can have one branch, which again, just seems odd!
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2007
    Posts
    1

    Some SQL weaknesses on db processing

    Hello:

    I want to share with you all an investigation I made related to come of the SQL weaknesses and interesting characteristics of this language on database processing in the following site:

    www.SPAMLINK.com

    All the examples in this sites has been verified with DB2, the situation is practically the same in other DBMS.I think that the aspects analysed in this site are essential for people who know SQL.

    I hope it’s useful.
    Last edited by blindman; 01-02-08 at 11:18.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we frown on people doing "drive-by" postings with "throwaway" usernames -- you register, spam us with your link, go away, then come back, re-register, and spam us again.

    please go back to using ofigue

    this thread is now merged with your previous one

    thanks for understanding
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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