Any good at SQL?

Is this statement bloated? is there a better way of achieving the same thing?

Basically it grabs the date and value of a product SKU based on information in a couple of tables.

	SELECT ns_tracker_1_stat_log.STAMP, ns_tracker_sale_set.COST
	FROM ns_tracker_1_stat_log
	INNER JOIN ns_tracker_sale_set, ns_tracker_sale_item, ns_tracker_1_stat_sale
	ON ns_tracker_sale_item.NAME = '$sku' 
	AND ns_tracker_sale_set.ITEM_ID = ns_tracker_sale_item.ID 
	AND ns_tracker_sale_set.SALE_ID = ns_tracker_1_stat_sale.ID 
	AND ns_tracker_1_stat_sale.LOG_ID = ns_tracker_1_stat_log.ID 
	ORDER BY ns_tracker_1_stat_log.STAMP ASC 

--  Indent code and use mixed case for readibility
Select
	TLog.Stamp
	,TSet.Cost	--  Put commas on the new line in lists since it is easier to check and maintain them
From
	--  Give each table a short and meaningful alias
	--  Re-arranged the order to match the logical join sequence TItem->TSet->TSale->TLog
	--  (this is irrelevant to performance but easier to understand)
	--  INNER JOIN is the default join type
	--  Rather than the ANSI standard explicit JOIN, I prefer the 'theta join' syntax wherein the From clause
	--  lists the tables and Where clause provides the join conditions
	ns_tracker_sale_item	TItem
	,ns_tracker_sale_set	TSet
	,ns_tracker_1_stat_sale	TSale
	,ns_tracker_1_stat_log	TLog
Where
	TItem.NAME = '$sku'
	--  Join conditions in order of tables given above
	AND TSet.Item_ID = TItem.ID 
	AND TSale.ID = TSet.Sale_ID
	AND TSale.Log_ID = TLog.ID 
Order by
	TLog.Stamp		-- ASC is the default so don't need to specify it
;

Without the comments:

Select
	TLog.Stamp
	,TSet.Cost
From
	ns_tracker_sale_item	TItem
	,ns_tracker_sale_set	TSet
	,ns_tracker_1_stat_sale	TSale
	,ns_tracker_1_stat_log	TLog
Where
	TItem.NAME = '$sku'
	AND TSet.Item_ID = TItem.ID 
	AND TSale.ID = TSet.Sale_ID
	AND TSale.Log_ID = TLog.ID 
Order by
	TLog.Stamp
;

Oooh, did not know you could do that.

Thanks

yeah, thanks from me aswell, I just learned something new :D

I just use letters for the tables e.g. A, B, C etc. can't get any shorter!

I think I'll stick with the complete table names, I don't care for abbreviation just a better way of executing the same code.

There's no more efficient way to do the SQL. You could build a combo table with the fields you are interestedf in but then you'd have to maintain it, swings and roundabouts, depends how slow the SQL is.

Suit yourself.

Apart from the layout and style comments that I have already made, you can't really change much. This is assuming that the structure is as can be inferred from your query - that there is no foreign key to go directly from TItem to say TLog and no duplication of Name or Cost in TLog.

You are using four chained tables and the bulk of the code is just listing them and specifying the chain. You qualify on one table - TItem.NAME = '$sku', and select data from two others - what I refer to as TLog and TSet. TSale is only there as a link in the chain.

A view could be defined that provides the entire chain and reduces your query to something like:

Select
	V.Stamp
	,V.Cost
From
	ItemToLog	V
Where
	V.Name = '$sku'
Order by
	V.Stamp
;

This is worth doing if you often have to 'walk the chain' and want to simplify the whole process. You can always do it the long way if the view isn't adequate for a different request.

Even "Name = '$sku'" could be pushed into the view if that is always a condition.

Of course the tradeof is having to define and maintain the view definition.

Most DBMSs should handle the query against a view in exactly the same way as the long-hand version. Some can even provide performance improvements using 'materialised views' albeit with space and maintenance costs.

yeah... i agree... indenting sql is a waste for me as well... the form in the top query looks fine...

just a personal thing... i see no advantage...

--Mike

Having made databases and used SQL for 9 years, sometimes I found that it is not always "best practice" to never have data redundancy. It can be useful to duplicate keys in other tables if you find yourself having to write massive SQL joins or do a summary table as I suggested. A View could be another good way too. But this is more work and if the SQL is fast, why bother? However if you expect (and hope) that the database grows massive, well then it could slow down a lot. Some of the tables I've dealth with had over a million records and were 500Mb big i.e. slow as hell so optimisations were needed in terms of summaries and duplicate info for certain functions that needed speed e.g sales figures for the boss or stock levels for the shop floor.

I hate the mind numbing tedium of SQL.
Take the 'y' out of SQL, that sums it up perfectly.

Thanks all, it's all working. Just doing a real-time reporting system for Cloverleaf, now developers can log in and see real-time how many orders & downloads they got for any period. No need to bother me anymore :D



I might even give the developers access to the *LIVE* product descriptions :D not sure if that's a good thing though.

Neat reporting.

Do my eyes a favor and never code SQL again.

8)

Your web page layout is much better on the eyes.