Query for 3 table comparison

[memkam]

The scenario is: I have 3 tables.

Commercial: NewPrice, OldPrice, Weight, Zone, ServiceType
Residential: NewPrice, OldPrice, Weight, Zone, ServiceType
TransactionReport: Price, Weight, Zone, IsItRes (a boolean)


Task at hand: Write a query, which takes all the records in the transaction table, first of all checks whether it is a residential or Commercial by looking at IsItRes field in the TransactionReport table.

If a specific record is a residential delivery, then matches the service type, weight and zone in the TransactionReport table to the Residential table and displays the Weight, Zone, Price, NewPrice from Residential table and OldPrice from Residetial table.

If record is not residential, then prints similar information from the commercial table.

I have attched my SQL code, any help or suggestions to improve logic will be greatly appreciated.

SELECT TransactionReport.ServiceType, TransactionReport.Weight, TransactionReport.Zone, TransactionReport.Price, ResPrice.Contractprice, ResPrice.HistoricPrice, ResPrice.ServiceType, ResPrice.Weight, ResPrice.Zone
FROM TransactionReport, ResPrice
WHERE (((ResPrice.ServiceType)=[TransactionReport].[ServiceType]) AND ((ResPrice.Weight)=[TransactionReport].[Weight]) AND ((ResPrice.Zone)=[TransactionReport].[Zone]) AND ((([TransactionReport].[ServiceType]=[ResPrice].[ServiceType]) & ([TransactionReport].[Zone]=[ResPrice].[Zone]) & ([TransactionReport].[Weight]=[ResPrice].[Weight]))<>False));

Problems i am facing now:

I am able to match all the transactions with the same servce type from the transactions table to the residential table.

However, the query for some reason does not traverse to the next service type.

Also, wanted to know if there is a way for me to insert the if functionality. That is.. if ItIsRes = true then matc with res, otherwise match with com.?

 

[NathanGriffiths]

You will find it easier to develop and debug queries if you write them using proper JOIN syntax between the tables. Also using table aliases will make your code easier to understand.

This query will return data from either your residential (resprice) table or your commercial table depending on the IsItRes flag;

SELECT
	TR_ServiceType = tr.ServiceType,
	TR_Weight      = tr.Weight,
	TR_Zone        = tr.Zone,
	TR_Price       = tr.Price,
	ContractPrice = CASE IsItRes 
	                  WHEN 1 THEN rs.ContractPrice 
			  ELSE cm.ContractPrice 
		        END,
	HistoricPrice  = CASE IsItRes 
		           WHEN 1 THEN rs.HistoricPrice 
			   ELSE cm.HistoricPrice 
		         END,
	ServiceType    = CASE IsItRes 
			   WHEN 1 THEN rs.ServiceType 
			   ELSE cm.ServiceType 
			 END,
	Weight	       = CASE IsItRes 
			   WHEN 1 THEN rs.Weight 
			   ELSE cm.Weight 
		         END,
	Zone	       = CASE IsItRes 
			   WHEN 1 THEN rs.Zone 
			   ELSE cm.Zone 
			 END
FROM
	TransactionReport tr 
	JOIN ResPrice rs 	
		ON tr.ServiceType = rs.ServiceType AND 
			tr.Weight = rs.Weight AND 
			tr.Zone = rs.Zone
	JOIN Commercial cm 
		ON tr.ServiceType = cm.ServiceType AND 
			tr.Weight = cm.Weight AND 
			tr.Zone = cm.Zone

I'm not sure what you mean by "traverse to the next Service Type" though.

cheers

Nathan

----------------------------------------
Pessimists, we're told, look at a glass containing 50% air and 50% water and see it as half empty. Optimists, in contrast, see it as half full. DBAs, of course, understand the glass is twice as big as it needs to be.
----------------------------------------

 

[ESquared]

Hey memkam,

Is there any reason you can't just combine the two tables and add a bit field distinguishing which row is which type? Then you could do all that with a simple join.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[memkam]

BlueStringPudding,

Thanks for taking the time to write this out. However, when I paste this in a query, it gives me a syntax error (missing operator) in query expression 'ContractPrice = Case IsItRes When 1 etc.

Thanks again.

 

[memkam]

ESquared,

The reason it is in seperate tables is because the tables are 10000 lines each, hence i put it in seperate tables to make the search more efficient.

 

[memkam]

BlueStringPudding,

what i meant by traverse was:

for every record in the transactionReport table, the query will look up a value in either the Res or Comm table.

Thanks.

 

[ESquared]

memkam, SQL server is designed to handle tables with MILLIONS of rows.

I strongly recommend combining the two. Your goal is admirable, but I think the search will be more efficient with one table than two. Since you have to join to both tables in some situations anyway, there is a ton of extra overhead you've introduced by splitting them. For one thing, look how complicated it is to query!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[memkam]

I am doing this on a normal MS Access database. Am I on the wrong forum? If someone can help me, I will greatly appreciate.

 

[ESquared]

This is the SQL Server: Programming forum. It says right at the top! Those CASE statements aren't going to work in Access.

I still recommend you put them in one table. Let the database do its own job of optimizing things, it's just making it more complicated to use two.

If you have more questions it's best to go to the Microsoft Access Queries and JET SQL forum.

-------------------------------------
A sacrifice is harder when no one knows you've made it.