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,
GiseleYour 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|||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

:cool:|||Now it worked, thanks a lot guys!|||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.|||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

:cool:|||Yes, ISNULL it not ANSI standard, while COALESCE is, so I suppose COALESCE is preferable.