JOINing 2 colums of one table to 1 column of another

vivoices · #1 (**permalink**) 02-24-10, 09:11

I have a table (a) with 3 columns:
txt_id, objName, text

and another Products table (b) including the columns:
short_descr_id, long_descr_id

I JOIN these two tables using:
. . .
ON
b.short_descr_id = a.txt_id
AND
b.long_descr_id = a.txt_id

how do I write the SELECT part to get the objName and text for both short_descr_id and long_descr_id?

Thanks,
David

dav1mo · #2 (**permalink**) 02-24-10, 10:49

SELECT a.objName, a.text

Other than that I think you need to describe your issue better.
Dave

vivoices · #3 (**permalink**) 02-24-10, 11:42

Thanks for your reply dav1mo.

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:
SELECT
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.

David

r937 · #4 (**permalink**) 02-24-10, 13:48

Code:

SELECT a1.objName AS short_name
     , a1.text    AS short_text
     , a2.objName AS long_name
     , a2.text    AS long_text
  FROM table_b
INNER
  JOIN table_a AS a1
    ON a1.txt_id = table_b.short_descr_id
INNER
  JOIN table_a AS a2
    ON a2.txt_id = table_b.long_descr_id

vivoices · #5 (**permalink**) 02-25-10, 04:46

Thanks r937,
here is the syntax that works in my php code:

Code:

"SELECT
	products.product_id,
	products.thumb_img_id,
	products.big_img_id,
	nameID.resource_name,
	descriptionId.resource_name
FROM
	products
	INNER JOIN
		(
			text_resource_names AS nameID,
			text_resource_names AS descriptionId
		)
	ON
		(
			products.name_text_id = nameID.text_id
			AND
			products.description_text_id = descriptionId.text_id
		)
WHERE
	products.page_id = " . $page_id . "
ORDER BY
	products.displayOrder ASC"

I have to code SQL maybe once a month, if at all, so the alias escaped me.

Thanks again,
David

r937 · #6 (**permalink**) 02-25-10, 05:04

Quote:

Originally Posted by vivoices

here is the syntax that works

could i ask you to change it slightly please?

Code:

SELECT products.product_id
     , products.thumb_img_id
     , products.big_img_id
     , nameID.resource_name
     , descriptionId.resource_name
  FROM products
INNER 
  JOIN text_resource_names AS nameID
    ON nameID.text_id = products.name_text_id
INNER
  JOIN text_resource_names AS descriptionId
    ON descriptionId.text_id = products.description_text_id 
 WHERE products.page_id = " . $page_id . "
ORDER 
    BY products.displayOrder ASC

note how the INNER JOINs are written

you should stay away from the "comma list" style of joining, you'll just get into trouble

vivoices · #7 (**permalink**) 02-25-10, 08:12

Thanks for your reply Rudy,
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.

Is it performance you are concerned about?
Has it something to do with the MySQL engine, I often use a DB Type of InnoDB?

Your positioning of the commas in the SELECT statement makes alot of sense, I will adopt it.

David

r937 · #8 (**permalink**) 02-25-10, 09:47

Quote:

Originally Posted by vivoices

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

vivoices · #9 (**permalink**) 02-25-10, 11:25

Thanks Rudy

Quote:

Originally Posted by r937

the explicit JOIN syntax is way more readable, as it isolates each joined table and clearly indicates the join conditions for that table alone

Agreed, so

Code:

SELECT
		products.product_id
	,	products.thumb_img_id
	,	products.big_img_id
	,	nameID.resource_name
	,	descriptionId.resource_name
FROM
	products
	INNER JOIN
			text_resource_names AS nameID
	ON
			products.name_text_id = nameID.text_id

	INNER JOIN
			text_resource_names AS descriptionId
	ON
			products.description_text_id = descriptionId.text_id
WHERE
	products.page_id = " . $rTxts[0] . "
ORDER BY
	products.displayOrder ASC

is more "fault proof" according to your experience. The next time I have to write complex nested JOINs, I will put the readabillity to test.

David

Internet Services

Web Development

Digital Marketing

Consumer Tech