"JOIN" syntax

F

flynn

Well-known member
Joined
Jul 28, 2005
Messages
58
Given this SQL query (which uses the old style "WHERE" clause to do joins):

Code: 
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, Im not quite sure how to translate this line
Code: 
	( c.employee_number = sr.employee_number or ( sr.territory = @sr_territory and sr.branch = c.branch )) AND

Thanks in advance,
flynn
 
Since youre 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, Ive moved each table into an INNER JOIN (invalid SQL syntax, just showing step 1):
Code: 
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:
Code: 
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 youd 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 its right.

-ner
 
Thank you Nerseus.

This is the part that had me stumped:

Code: 
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 )
)
 
Back
Top