English, Please !?!?!!

[ScotDiddle]

Hi Folks,

 

      I pretty much have my head wrapped around php, but my new job requires me to become proficient at more complex SQL queries.

 

I inherited the following SQL query and was hoping some kind SQL guru can explain it in english pseudo code for me.

 

I am having trouble deciphering the "AS i", "AS cp" and "AS l" statements, as well as what the "LEFT JOINS" are trying to accomplish.

 

Here is the query, followed by the output row from the query.

 

Thanks in advance,

 

Scot L. Diddle, Richmond VA

 

SELECT DISTINCT 

	i.id, i.location, 
	l.description AS loc, i.closeout, 

	IF( i.quantity > "6000", "6000+", i.quantity ) AS quantity, 

	UNIX_TIMESTAMP( i.sale_expir ) AS sale_expir, i.size_no, 

	CASE 

		WHEN IFNULL( cp.c_price, 999999 ) > IFNULL( cp.d_price, 999999 ) 

			THEN 
				IFNULL( cp.d_price, 999999 ) 
			ELSE 
				IFNULL( cp.c_price, 999999 ) 
	END 

	AS cust_price, 

		i.v_price AS prod_price, 

			FORMAT( IF( i.sv_price<i.v_price, i.sv_price, "" ), 2 ) AS sale_price 

	FROM inventory 

		AS i 

			LEFT JOIN custprice 

				AS cp 

					ON cp.customer_num="9898989898" 

						AND cp.style_no=i.style_no 
							AND cp.size_no=i.size_no 
								AND beg_date<=20100519093909 
									AND end_date>=20100519093909 
										AND ( cp.color=i.color ) 

											LEFT JOIN locations 

												AS l 

													ON l.code=i.location 
														AND l.allow_order=1 

	WHERE i.style_no = "G2000" 
		AND i.color="WHT" 

	ORDER by i.location, i.size_no

 

Array
(
    [id] => G2000WHT11
    [location] => 1
    [loc] => Virginia
    [closeout] => SA
    [quantity] => 6000+
    [sale_expir] => 1275080400
    [size_no] => 1
    [cust_price] => 999999
    [prod_price] => 1.45
    [sale_price] => 1.43
)

 

 

 

[andrewgauger]

Esentially you are pulling together two tables (well three but lets start with two).  You have your inventory table, and the customer price table. 

 

The AS statements are simply a way to ease the coding of the sql statement.  Instead of having to reference "inventory" you can reference "i" through out the query. 

 

The cust_price part of the query result set is from the custprice table.  Without the left join you wouldn't have that data accessible.  The reason LEFT JOIN is used is because not all customers have prices in the table (which is also where the CASE IFNULL section comes in, to handle queries without custprice data for that inventory.

 

Hope that helps.

[ScotDiddle]

andrewgauger,

 

    Thank-you for your reply.  It now makes more sense.  I will continue my googling for SQL join tutorials to determine when and why one needs a join, and what to expect from such a construct.

 

Scot L. Diddle, Richmond VA