I am trying to do a select query where I select the customer
information from the customer table and from the customer address file
only one address. If the address file for the customer has a
address_type= A and B and C I want C. If it has A and B I want B and if
it only has A I want A. Is this doable with a query
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
cust_addresses.address_type, cust_addresses.address_1,
cust_addresses.address_2, cust_addresses.city, cust_addresses.state,
cust_addresses.postal_code, cust_addresses.country,
cust_addresses.telephone
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
The above gives me all the addresses and I want only onehi you can use CASE statement
For Example:
SELECT customers.acct_no, customers.company, customers.territory,
customers.telephone, customers.fax, customers.AP_contact,
customers.AP_telephone, customers.send_stmts, customers.tax_code,
customers.tax_code, customers.stmt_code, customers.date_added,
'cust_addresses.address_1'=
CASE
WHEN cust_addresses.address_type = A and B and C THEN C
WHEN cust_addresses.address_type= A and B THEN B
WHEN cust_addresses.address_type= A THEN A
END,
FROM customers LEFT JOIN cust_addresses ON customers.acct_no =
cust_addresses.acct_no
WHERE (((customers.acct_no)>"000114"))
ORDER BY cust_addresses.address_type
GO
You can refer to books online
bye
No comments:
Post a Comment