join query help?

[lucasm]

Hi everyone, I'm kind of new to more advanced sql than update,select, etc. and I'm trying to select all records from one table, modifying the returned value of a field on some of them based on records from another table. Basically, I have a customers table and an orders one, trying to select all customers from customer, but add an (A) at the beginning of the customer name in the results for those who have an entry in the orders table (A for active).

Here is my select statement for getting all customers:
SELECT * FROM Customers WHERE cust_name LIKE '$queryString%'

and here is how I get their orders
SELECT * FROM Orders WHERE order_cust = '$customer' ORDER BY order_date DESC

I'm extremely hazy on joins, and I intend to un-haze soon, but would like to churn this out fast for a friend's site.
Thanks

 

[RiverGuy]

SELECT 
ResolvedName =
CASE
  WHEN o.order_cust IS NULL THEN cust_name
  ELSE '(A) ' + cust_name
END,
c.*
FROM Customers c
LEFT OUTER JOIN
  (SELECT DISTINCT order_cust FROM Orders) o
ON c.cust_number_column = o.order_cust
WHERE c.cust_name LIKE '$queryString%'

 

[bborissov]

Using [*] as field list is BAD!
I doubt you ALWAYS need all fields from table(s).
Use just needed fields in the queries. Also HOW you will join these tables?
What is the common field between them?
And how do you know if the some customers is still active,
or it is enough to have a record in Orders Table for them?
What if the last date in that table is 01 Jan 1432?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.