Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: JOINing 2 colums of one table to 1 column of another

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    SELECT a.objName, a.text

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

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    56
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2009
    Posts
    56
    Thanks Rudy
    Quote Originally Posted by r937 View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •