Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: How to get one row instead of two in a JOIN?

    The query is like this:

    Code:
    SELECT *
    FROM table1 a
    JOIN table2 b ON (a.first_id = b.name_id OR a.last_id = b.name_id)
    WHERE p_id = 1
    So table2 contains all names, table1 the p_id's, linking both first - and last name to table2.

    Is there a way of concatenate this so I only get one row of the query above instead of two?

    All help appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.foo
         , CONCAT_WS(' '
                    ,firstnametable.name 
                    ,lastnametable.name ) AS fullname
      FROM table1 a
    INNER
      JOIN table2 AS lastnametable
        ON lastnametable.name_id = a.last_id
    INNER
      JOIN table2 AS firstnametable
        ON firstnametable.name_id = a.first_id
     WHERE a.p_id = 1
    why in heaven's name did you create a table for last and first names???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    Code:
    SELECT a.foo
         , CONCAT_WS(' '
                    ,firstnametable.name 
                    ,lastnametable.name ) AS fullname
      FROM table1 a
    INNER
      JOIN table2 AS lastnametable
        ON lastnametable.name_id = a.last_id
    INNER
      JOIN table2 AS firstnametable
        ON firstnametable.name_id = a.first_id
     WHERE a.p_id = 1
    why in heaven's name did you create a table for last and first names???
    Thanks a lot, I will try this!

    It wasn't me who created it, I have downloaded the information in text files, then made a code to insert them in mysql-tables. And since it might be necessary to download updates of these tables, I thought it would be better to keep the same format.

    Will the above be much slower though?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Haydn View Post
    ...Will the above be much slower though?
    rather than speculate, why not try it out for yourself and see what happens?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    Will the above be much slower though?
    much slower than what?

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

  6. #6
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by healdem View Post
    rather than speculate, why not try it out for yourself and see what happens?
    Yes, of course, that's a good suggestion. It's just a lot of work to change the structure, so I thought I could ask you experts.


    Quote Originally Posted by r937 View Post
    much slower than what?

    Slower than e.g. having one name-id in table1 which refers to both first- and last name in table2...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    It's just a lot of work to change the structure...
    change the structure? you mean, dispense with the name table?

    yeah, that might take work, but it's probably worth doing

    leaving the tables as is and changing the query is trivial, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2012
    Posts
    44
    I looked at this now, I added two columns to table1, and with two small UPDATE-queries I had all first and last names in table1, and don't need table2. Of course, his have to be done when table1 is updated and replaced, but that's no bigger issue.

    Thanks!

Posting Permissions

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