Problems with the From Clause

1

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

2

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:

3

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

4

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
5

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

6

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