SQLTeam.com | Weblogs | Forums

Problems with the From Clause

Hello Everyone,

I have a query that was originally written in an MS Access database that I now need to put into SQL. I have encountered something I haven't seen before in the From Clause.

This is what I have written so far:

Select c.name, a.order_ref, p.product_code, u.amount, p.purchase_number, pc.purchase_code
From Customer c

Inner Join Action a

ON c.client_Id = a.client_id

Inner Join Products p

ON a.client_Id = p.client_id

AND a.product_no = p.product_no

This is the From Clause I am trying to translate from Access

FROM dbo_Customer INNER JOIN (dbo_Products INNER JOIN dbo_Action ON (dbo_Products.Product_No = dbo_Action.Product_No) AND (dbo_Products.Client_Id = dbo_Action.Client_Id)) ON dbo_Customer.Client_Id = dbo_Action.Client_Id**, (dbo_Uplift INNER JOIN dbo_Purchase ON dbo_Uplift.Payment_Ref = dbo_Purchase.Payment_Ref) INNER JOIN dbo_PurchaseCode ON dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode**

How do I incorporate the last 3 tables (the section of the code starting with ** and ending with **) into the query I have started to write in SQL?

The use of the comma in the FROM clause is confusing to me, any assistance would be much appreciated.

Many Thanks

Vinnie

hi

trying to do some organizing of information to make sense ... !!!!

Here it is

Tables Joins
dbo_Customer dbo_Customer.Client_Id = dbo_Action.Client_Id
dbo_Products dbo_Products.Client_Id = dbo_Action.Client_Id
dbo_Products.Product_No = dbo_Action.Product_No
dbo_Action dbo_Products.Product_No = dbo_Action.Product_No
dbo_Customer.Client_Id = dbo_Action.Client_Id
dbo_Products.Client_Id = dbo_Action.Client_Id
dbo_Uplift dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode
dbo_Uplift.Payment_Ref = dbo_Purchase.Payment_Ref
dbo_Purchase dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode
dbo_Uplift.Payment_Ref = dbo_Purchase.Payment_Ref
dbo_PurchaseCode dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode

From this You have to write the SQL Statement !!!
Hope this helps !!!

If not please let me know ..I can show you HOW !!!
:slight_smile: :slight_smile:

Looking at the info I gave you ..

Tables
customer
Products
Action
... common is Action Table join

Tables
UpLift
Purchase
PurchaseCode
... common is uplift table join

this is the top part and bottom part into SQL
but the connecting part to both .. that seems to be missing ... common table

from dbo_Action 
                join dbo_Products 
                             on  dbo_Action.Client_Id  = dbo_Products.Client_Id 
			                 and dbo_Action.Product_No = dbo_Products.Product_No 
                             and dbo_Action.Client_Id  = dbo_Products.Client_Id 

         	    join dbo_Customer 
                                   on  dbo_Action.Client_Id  =  dbo_Customer.Client_Id 
					               and dbo_Action.Client_Id  =  dbo_Customer.Client_Id 


from  dbo_Uplift        
               join dbo_Purchase 
                          on  dbo_Uplift.Payment_Ref  = dbo_Purchase.Payment_Ref
			              and dbo_Uplift.Payment_Ref  = dbo_Purchase.Payment_Ref  

               join dbo_PurchaseCode 
                          on  dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode
				          and dbo_Uplift.Purchase_Type = dbo_PurchaseCode.TypeCode

Hi Harish.

Thanks for looking into this for me. I don't know how to join the top and bottom parts of the query. The query does work in MS Access.
Can you write the full query in SQL for me ?
Is this query possible in SQL?

Thanks

Vinnie

Looking at what you had copy pasted
this is the sense i could make out of it

Looks like something is missing
Maybe You did not copy the whole Query
Or
Access does something else that I have no idea about
which makes the whole query work !!!

Please Look into it a little .. and let me know
Thanks