The problem is that I have two rows in table "a" that I have to map (JOIN) into each row of table "b".
It might be something like:
a.objName, a.text, (for the b.short_descr_id column)
a.objName, a.text (for the b.long_descr_id column)
I assume it is possible but don't know the syntax.
SELECT a1.objName AS short_name
, a1.text AS short_text
, a2.objName AS long_name
, a2.text AS long_text
JOIN table_a AS a1
ON a1.txt_id = table_b.short_descr_id
JOIN table_a AS a2
ON a2.txt_id = table_b.long_descr_id
here is the syntax that works in my php code:
text_resource_names AS nameID,
text_resource_names AS descriptionId
products.name_text_id = nameID.text_id
products.description_text_id = descriptionId.text_id
products.page_id = " . $page_id . "
I have to code SQL maybe once a month, if at all, so the alias escaped me.
may I ask why I could get into trouble using the "comma list" style in the JOINs?
It seems way more readable to me and it is coherent with the SELECT statement.
i say that because i've seen people post problems on forums where the "comma style" join was the cause of the problem -- there are several problems, and i won't go into them, i'll just say that they do come up regularly
as for readable, sorry, i have to disagree -- the explicit JOIN syntax is way more readable, as it isolates each joined table and clearly indicates the join conditions for that table alone
the "leading comma" convention for lists of columns, however, is a separate issue, and is purely a matter of style -- syntactically it is equivalent, but the effect on maintenance (adding or removing columns from the list) is remarkable, at least in my opinion