Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    São Paulo - Brazil
    Posts
    3

    Question Unanswered: Problem with Query

    Hi people, I need your help:

    I made a query which should return the values:

    Customer's Name (table CUSTOMERS)
    Customer's ID (table CUSTOMERS)
    Customer's City (table CITY)
    Date of Visits to Customer (table VISITS)
    Visit's Status (table STATUSVIS) = returns value '0' - if the customer was visited or returns '1' if the customer wasn't at his office.

    My problem is: to get the value of the field City I have 2 tables for Customers Addresses, they are CUSTADD1 and CUSTADD2, and my customer can only have one address in one of those tables.

    The query I made isn't working and I have no clue how to fix it up.

    ------------------------------------------------------------------------------------SELECT distinct(VISITS.visdate), CUSTOMERS.customername, CUSTOMERS.customerID, CITY.cityname

    FROM
    CUSTOMERS, VISITS, STATUSVIS, CUSTADD1, CUSTADD2, CITY
    WHERE
    CUSTOMERS.customerID in (CUSTADD1.customerID, CUSTADD2.customerID) and
    CUSTADD1.cityname = CITY.cityname or
    CUSTADD2.cityname = CITY.cityname and
    CUSTOMERS.customerID = VISITS.customerID and
    STATUSVIS.status = 0
    ------------------------------------------------------------------------------------

    Does someone have an idea about what isn't working in this query?

    Thanks in advance,
    Gisele

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your query is failing because you are mixing AND and OR comparisons in your WHERE clause without defining their order of precedence with parentheses, and becuase you have an unjoined table (STATUSVIS) in your statement.

    The query would be a lot clearer if you would establish table relationships with JOIN statements rather than in your WHERE clause. Joining tables in the WHERE clause is a bad habit. Very bad.

    Here is your query rewritten using JOINs. Notice how the CITY table isn't necessary. You aren't pulling and data from it that you can't get from the CUSTADD tables. Also, the STATUSVIS table has been dropped because it was not joined in the original query anyway. If you want to add its criteria to the query, join it to one of the existing tables (CUSTOMER?).

    The isnull function returns the cityname from CUSTADD1 if it exists, otherwise it returns the value from CUSTADD2. Modify it if you want different selection logic.

    Also, I'm not sure if the DISTINCT clause is necessary. I doubt it, but it depends on your data.

    Please read up on the SELECT statement in Books Online and familiarize yourself with good coding practices.


    SELECT distinct(VISITS.visdate),
    CUSTOMERS.customername,
    CUSTOMERS.customerID,
    isnull(CUSTADD1.cityname, CUSTADD2.cityname) cityname
    FROM CUSTOMERS
    inner join VISITS on CUSTOMERS.customerID = VISITS.customerID
    left outer join CUSTADD1 on CUSTOMERS.customerID = CUSTADD1.customerID
    left outer join CUSTADD2 on CUSTOMERS.customerID = CUSTADD2.customerID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, you gotta start using COALESCE instead of ISNULL, it will go further

    gisele, your problems with ANDs and ORs is quite common, i have seen many people write them linearly and expect them to be evaluated linearly

    however, they are evaluated as though there were parentheses around any terms connect with AND

    for example

    ... WHERE x=1 AND y=4 OR z=7

    will be evaluated as

    ... WHERE (x=1 AND y=4) OR z=7

    ANDs always take precedence over ORs

    this type of expression is called a boolean expression, named after Jim Boolean, inventor of the word AND


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

  4. #4
    Join Date
    Jan 2004
    Location
    São Paulo - Brazil
    Posts
    3
    Now it worked, thanks a lot guys!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    COALESCE vs ISNULL???

    What's the diff for two values?

    ..and the word OR was first coined by George Orwell, who also, by the way, invented the OReo cookie.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no difference in sql server

    my advice was meant for sql in general

    COALESCE is standard

    ISNULL isn't (i think)

    besides, then you will never get it mixed up with the NULLIF function


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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, ISNULL it not ANSI standard, while COALESCE is, so I suppose COALESCE is preferable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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