FROM statement: join three tables in chain arrangement? 1

[VBAjedi]

I want to join a table named SALES and three copies of a table named IDENTIFIER in a chain arrangement (S -> I1 -> I2 -> I3). I tried:

FROM
SALES AS S
LEFT OUTER JOIN IDENTIFIER AS I1 ON S.FI_IDENTID = I1.PI_IDENTID,
I1 LEFT OUTER JOIN IDENTIFIER AS I2 ON I1.N_IDENTCOMESUNDER = I2.PI_IDENTID,
I2 LEFT OUTER JOIN IDENTIFIER AS I3 ON I2.N_IDENTCOMESUNDER = I3.PI_IDENTID

but it gives me an "I1 table name not unique" error. . .

What is the syntax to "chain-join" tables together like this?



VBAjedi

 

[sleipnir214]

It's the "I1 LEFT OUTER JOIN IDENTIFIER " which begins the 4th line of your query.

I don't believe you can use a table alias in that place.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[VBAjedi]

Thanks for the reply and the problem diagnosis! Now for a solution. . . how do I specify that I want to link I2 to the specific copy of IDENTIFIER (I1) that I just linked to SALES?

TIA

VBAjedi

 

[sleipnir214]

You're thinking in the wrong metaphor. SQL is set operations. Whan you use a join, you're asking for the intersection of all sets that fulfill a group of conditions.

In any regard, you're not issuing a chained join. Your syntax is equivalent to

SELECT * from a, b, c

since you've aggregated the three joins with commas and have not specified a joining WHERE clause. With this syntax, MySQL will assume you're asking for a cross join and give you a lot more returned values than you probably want.

Try something like this:

FROM 
			SALES AS S 
		LEFT OUTER JOIN
			IDENTIFIER AS I1
			ON
				S.FI_IDENTID = I1.PI_IDENTID
	LEFT OUTER JOIN
		IDENTIFIER AS I2
		ON
			I1.N_IDENTCOMESUNDER = I2.PI_IDENTID
LEFT OUTER JOIN
	IDENTIFIER I3
	ON
		I2.N_IDENTCOMESUNDER = I3.PI_IDENTID

(I've used deliberate indentation to try to make what I'm doing more obvious.)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[VBAjedi]

My understanding had been that using your approach would link all three copies of Identifier directly to my Sales table, and I didn't think that was what I was after.

Still, I tried it, and it appears to give me what I'm after (although I haven't been able to test my results to see that it's returning "the right, all the right, and nothing but the right" records)! A star for your help.

I obviously need to study some more to understand joining better. . . [LOL]

VBAjedi