Multiple Left Joins in ONE query (this is a tough one) 3

[Genotix]

Hey guys,

I've got a nice one for you this time.

I'm programming a recipe application at the moment to create recipes for factory product.

My database is in short like this :
- table "Recipe" that holds the recipe number(autonumber), code ("PC123") and name ("Pancake")
- table "TT_Recipe_Component" that holds the Recipe_NUMBER(Foreign key to Recipe), Component_NUMBER(Foreign key to Component) and Percentage("15") of component.
- table Components that holds Component_NUMBER, Description ("Butter")

I use ADODB to connect to the database.

The Source :

SELECT * FROM Component
                LEFT JOIN TT_Recipe_Component ON (Component.NUMBER = TT_Recipe_Component.NUMBER)

AND

SELECT * FROM Component
                LEFT JOIN Recipe ON (TT_Recipe_Component.NUMBER = Recipe.NUMBER)

Both work!

So I tried using the source :

SELECT * FROM Component
                LEFT JOIN TT_Recipe_Component ON (Component.NUMBER = TT_Recipe_Component.NUMBER)
                LEFT JOIN Recipe ON (TT_Recipe_Component.NUMBER = Recipe.NUMBER)

This gives me a Syntax Error :
Run-time error '-2147217900 (80040e14)':

Syntax error(missing operator) in query expression :
Component.NUMBER = TT_Recipe_Component.Component_NUMBER
LEFT JOIN Recipe ON Recipe.NUMBER =
TT_Recipe_Component.Recipe_NUMBER

Have you got any idea how I can combine the three tables?

Thanx in advance

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[cbsm]

Are you using access db ?
If this is the case, I usually create the query using the query tool from access, have a look at the SQL view and copy it (usually with slight modifications) then in VB.

SELECT Table2.test2, Table1.test1, Table3.test3
FROM (Table1 left JOIN Table2 ON Table1.ID = Table2.id) left JOIN Table3 ON Table1.ID = Table3.id;

 

[BogdanMBM]

First of all, I don't think that

SELECT * FROM Component
                LEFT JOIN Recipe ON (TT_Recipe_Component.NUMBER = Recipe.NUMBER)

works. It should raise error:

The column prefix 'TT_Recipe_Component' does not match with a table name or alias name used in the query.

Maibe you misspelled the table here.
Secondly:
I've tried your query over my database (with TableNames changed) and it works just fine (it doesn't make any sense on my database, I've just wanted to get the same error as you did). Here it is:

SELECT * FROM Customer
                LEFT JOIN SalesOrder ON (Customer.ID = SalesOrder.OrderCustomerID)
                LEFT JOIN DiscountCustomer ON (SalesOrder.OrderCustomerID = DiscountCustomer.CustomerID)

For me it works...

Hope I've been helpful,
Bogdan Muresan.

 

[BogdanMBM]

Oups, yep, cbsm is write!
I tested on an SQL server database, doh...
My fault!

 

[Genotix]

cbsm > Yes, I am working on an Access Database.
Your first post did the trick..
I'm fimiliar with SQL but not the one Access is using.
Probably because the recordset links to the database it needs a different syntax then the default.

Thanx a million!

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[Genotix]

Would one of you happen to know how I can explain to VB6 or Access that when I delete a "component" or a "recipe", the link may be deleted but the component or recipe may not.

E.G. :
When I delete a record from the joined recordset :

SELECT * FROM ((Component 
LEFT JOIN TT_Recipe_Component on Component.NUMBER = TT_Recipe_Component.Component_NUMBER)
LEFT JOIN Recipe ON Recipe.NUMBER = TT_Recipe_Component.Recipe_NUMBER)
WHERE TT_Recipe_Component.Recipe_NUMBER IN 
(SELECT NUMBER FROM Recipe WHERE CODE = "PC123"

the "deletion" is cascaded to Component (which deletes an Ingrediënt) and cascaded to Recipe (which deletes the recipe)
Is there a way to use the same RecordSet and not to cascade the delete command to the Components and Recipes.

(I sure hope that I explained this in right English for I'm Dutch )

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[bjd4jc]

Cascading deletes are setup in your relationships. Examine the relationship setup and see if the option to cascade is turned on.

 

[Genotix]

Cascading deletes are setup in your relationships. Examine the relationship setup and see if the option to cascade is turned on.

I've examined this and could not find any cascade options in Access. (Using office 2003)
There is a field "force referential integrity" (translaten from Dutch) though.
I am dutch but the Dutch Office I'm using has got "non-common" terms in this field, so the options don't mean anything to me.
I you could point me to where I can set the cascading options in Access you'd be more than helpful.

Thnx for your reply.

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[bjd4jc]

I do not have office 2003 on my machine here. But, I have XP and I doubt it would have changed. On my version there is a "Enfore Referential Integrity" as you stated (it is a check box). When this is checked two other options are available...

Cascade Update related fields
Cascade Delete related fields

 

[Genotix]

Cascade Update related fields
Cascade Delete related fields

Now THAT are options i can understand

Thnx!

Linux IS userfriendly.
It's only very selective about who it's friends are.