"JOIN" syntax

[flynn]

Given this SQL query (which uses the old style "WHERE" clause to do joins):

 

SELECT 
c.cust_id, c.name, c.address_1, c.address_2, c.address_3, c.city, c.state, c.zipcode, 
co.first_name, co.last_name , co.fax, 
sr.rep_name, sr.rep_phone, sr.rep_fax, sr.rep_email
FROM 
customer c, contact co, sales_rep sr, customer_price cp 
WHERE
( c.static_cust_numb = cp.static_cust_numb ) and
( c.rep_id = sr.rep_id ) and
( cp.sales_code = @cp_sales_code ) AND
( c.employee_number = sr.employee_number or ( sr.territory = @sr_territory and sr.branch = c.branch )) AND
( c.status <> @customer_status ) AND
( c.rep_id = @rep_id ) AND
( co.default_contact = 'Y' AND c.static_cust_numb = co.static_cust_numb )

 

Can someone translate to use "JOIN" statements instead of using the "WHERE" statement? In particular, I'm not quite sure how to translate this line

	( c.employee_number = sr.employee_number or ( sr.territory = @sr_territory and sr.branch = c.branch )) AND

 

Thanks in advance,

flynn

[Nerseus]

Since you're not doing any outer joins (no "*=" syntax), the translation needs two things: move the separated tables into "INNER JOIN" syntax, and add an "ON" clause to each join.

 

First, I've moved each table into an INNER JOIN (invalid SQL syntax, just showing step 1):

SELECT 
c.cust_id, c.name, c.address_1, c.address_2, c.address_3, c.city, c.state, c.zipcode, 
co.first_name, co.last_name , co.fax, 
sr.rep_name, sr.rep_phone, sr.rep_fax, sr.rep_email
FROM 	customer c
INNER JOIN contact co
INNER JOIN sales_rep sr
INNER JOIN customer_price cp 
WHERE
( c.static_cust_numb = cp.static_cust_numb ) and
( c.rep_id = sr.rep_id ) and
( cp.sales_code = @cp_sales_code ) AND
( c.employee_number = sr.employee_number or ( sr.territory = @sr_territory and sr.branch = c.branch )) AND
( c.status <> @customer_status ) AND
( c.rep_id = @rep_id ) AND
( co.default_contact = 'Y' AND c.static_cust_numb = co.static_cust_numb )

 

And the final:

SELECT 
c.cust_id, c.name, c.address_1, c.address_2, c.address_3, c.city, c.state, c.zipcode, 
co.first_name, co.last_name , co.fax, 
sr.rep_name, sr.rep_phone, sr.rep_fax, sr.rep_email
FROM 	customer c
INNER JOIN contact co ON co.static_cust_numb = c.static_cust_numb
INNER JOIN sales_rep sr ON sr.rep_id = c.rep_id
	AND 
	(
		(sr.employee_number = c.employee_number)
		OR ( sr.territory = @sr_territory and sr.branch = c.branch )
	)
INNER JOIN customer_price cp ON cp.static_cust_numb = c.static_cust_numb
WHERE c.status <> @customer_status
AND 	c.rep_id = @rep_id
AND cp.sales_code = @cp_sales_code
AND	co.default_contact = 'Y'

 

Note that you can move the last two lines of the WHERE clause into the Joins if it makes more sense. If the joins were Outer Joins then you'd almost certainly want that logic moved into the join as leaving that kind of logic in the WHERE makes the join act like an inner join.

 

I made an assumption that you always want to join to sales_rep by rep_id - check out the nested "AND (a OR (b and c))" clause to make sure it's right.

 

-ner

[flynn]

Thank you Nerseus.

 

This is the part that had me stumped:

 

INNER JOIN sales_rep sr ON sr.rep_id = c.rep_id
AND 
(
  (sr.employee_number = c.employee_number)
  OR ( sr.territory = @sr_territory and sr.branch = c.branch )
)