Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2008
    Posts
    2

    Unanswered: one-to-many join complication

    Hello All,

    I am new to this forum and relatively new to SQL. I am building a webapplication with php and MySql and am having a bit of difficulty getting my query to return/display the right information.

    I have the basic join working properly but I cant get it to return the information as I need it. So either A) I need to do a different select statement and a different join altogether or B) I need to explore different options on the PHP end.

    I figured I would start with enlisting any help available here first in trying to determine if I might be able to adjust my Select statement as that would be the easier route, I think, than trying to do funky loops and interesting acrobats in PHP.

    Basically I have a simple db architecture set up with a one to many table relationship amongst 3 tables. Basically it is 2 one to many relationships with the primary key in the contact_info table (name_id) being used as a foreign key in the other two tables to link them. In general it looks like the following.

    Table1: contact_info

    name_id___first_name___last_name___street_addr___c ity___state___zip
    1_________bob________frapples_____13 test lane___Boston_Ma_____00000
    2_________fred________hast________12 roob ave___barre__vt______11111
    3_________fran________hill_________11 temp dr____york___pa______22222


    Table2: phone_numbers

    phone_id___name_id___phone_number
    1_________1_________000-000-0000
    2_________1_________111-111-1111
    3_________1_________222-222-2222
    4_________2_________333-333-3333
    5_________2_________444-444-4444
    6_________3_________555-555-5555
    7_________3_________666-666-6666
    8_________3_________777-777-7777

    Table3: email_addr

    email_id____name_id___email_addr
    1__________1_________bob@frapples.com
    2__________1_________bob.frapples@work.com
    3__________2_________fred@taboo.com
    4__________2_________hast_fam@smail.com
    5__________2_________fred.hast@work.com
    6__________3_________fran@online.com
    7__________3_________hill_fam@web.com
    8__________4_________fran.hill@work.com


    What I want to be able to do is list all the information for the people in the contact info table but I am getting duplicate information

    I have a query that looks like the following:

    SELECT first_name, last_name, street_addr, city, state, zip, phone_number, email_addr FROM contact_info, phone_numbers, email_addr WHERE contact_info.name_id=phone_number.name_id AND contact_info.name_id=email_addr.name_id;

    From what I understand of the basic inner join this is indeed working flawlessly but its not what I want for output as the output I am getting has too many rows and information. What I am getting is one row for every item in the many tables. In other words for each phone number or email address I am getting all of the other contact info repeated. like this (if there is only on number and one email address it is listed normally):


    Name_______Address____City___State_Zip___Phone____ ____Email
    bob frapples_13 test lane Boston Ma___00000_000-000-0000_bob@frapples.com
    bob frapples_13 test lane Boston Ma___00000_000-000-0000_bob.frapples@work.com
    bob frapples_13 test lane Boston Ma___00000_111-111-1111_bob@frapples.com
    bob frapples_13 test lane Boston Ma___00000_111-111-1111_bob.frapples@work.com
    bob frapples_13 test lane Boston Ma___00000_222-222-2222_bob@frapples.com
    bob frapples_13 test lane Boston Ma___00000_222-222-2222_bob.frapples@work.com
    fred hast____12 roob ave Barre__Vt___11111_333-333-3333_fred@taboo.com
    fred hast____12 roob ave Barre__Vt___11111_444-444-4444_fred@taboo.com
    fred hast____12 roob ave Barre__Vt___11111_333-333-3333_hast_fam@smail.com
    fred hast____12 roob ave Barre__Vt___11111_444-444-4444_hast_fam@smail.com
    fred hast____12 roob ave Barre__Vt___11111_333-333-3333_fred.hast@work.com
    fred hast____12 roob ave Barre__Vt___11111_444-444-4444_fred.hast@work.com
    fran hill_____11 temp dr__york___Pa___22222_555-555-5555_fran@online.com
    fran hill_____11 temp dr__york___Pa___22222_666-666-6666_fran@online.com
    fran hill_____11 temp dr__york___Pa___22222_777-777-7777_fran@online.com
    fran hill_____11 temp dr__york___Pa___22222_555-555-5555_hill_fam@web.com
    fran hill_____11 temp dr__york___Pa___22222_666-666-6666_hill_fam@web.com
    fran hill_____11 temp dr__york___Pa___22222_777-777-7777_hill_fam@web.com
    fran hill_____11 temp dr__york___Pa___22222_555-555-5555_fran.hill@work.com
    fran hill_____11 temp dr__york___Pa___22222_666-666-6666_fran.hill@work.com
    fran hill_____11 temp dr__york___Pa___22222_777-777-7777_fran.hill@work.com

    All I want is a listing of the name address etc once and then all 3 phone numbers and both email address.... something like below

    Name_______Address____City___State_Zip____Phone___ ______Email
    bob frapples_13 test lane_Boston_Ma___00000 000-000-0000 bob@frapples.com
    _______________________________________ 111-111-1111 bob.frapples@work.com
    ________________________________________222-222-2222
    fred hast____12 roob ave_Barre__Vt____11111 333-333-3333 fred@taboo.com
    ________________________________________444-444-4444 hast_fam@smail.com
    __________________________________________________ __ fred.hast@work.com
    fran hill______11 temp dr_york___Pa____22222 555-555-5555 fran@online.com
    ________________________________________666-666-6666 hill_fam@web.com
    ________________________________________777-777-7777 fran.hill@work.com

    and then that sort of format repeated for every entry in the contact_info table.

    Now again I am hoping that this might be accomplished via a SQL select statement with joins or some other fancy approach I have yet to learn or discover but if its not possible then I will explore other approaches with multiple queries and loops in php to format the data differently

    I apologize for the length and underscores. I wanted to make it as clear and readable as possible and the forum removes extra spaces so that made it necessary to use underscores.

    Thanks in advance for any help offered on this

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    may be better off using a join rather than a where clause

    Code:
    SELECT first_name, last_name, street_addr, city, state, zip, phone_number, email_addr FROM contact_info
    left join phone_number on phone_number.name_id=contact_info.name_id 
    left join email_addr on email_addr.name_id=contact_info.name_id;
    however Im not convicned neccesarily thats the right approach for what you want to do
    you may be better with an outer loop extracting all contacts, then an inner loop extracting all phone contacts for that person, then another inner loop for the email addresses.

    performance wise its not going to make that much difference, especially if you ensure the sort sequence is identical (eithe oin the PK on joining to the contact table and enforcing the saem sequence. you could issue a separate SQL for each contact ID to retrieve all phone & all email ID's. I m guessing its not going to make that much of a difference performance wasie as the major cost in the script is opening the connection to the db in the first place


    of course you could just take the view that you filter your data, noting whent he contact name id changes to satrt a new whatever in your php script.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    you may be better with an outer loop extracting all contacts, then an inner loop extracting all phone contacts for that person, then another inner loop for the email addresses.
    nooooooooooooooooooooo!!!!!!!!!!!!!!!!!!!!!!!!

    one query, and then two more queries inside loops?

    it is to cry

    Quote Originally Posted by healdem
    performance wise its not going to make that much difference
    sez you
    Code:
    SELECT contact_info.first_name
         , contact_info.last_name
         , contact_info.street_addr
         , contact_info.city
         , contact_info.state
         , contact_info.zip
         , p.phones
         , e.emails 
      FROM contact_info
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(phone_number) AS phones
               FROM phone_numbers
             GROUP
                 BY name_id ) AS p
        ON p.name_id = contact_info.name_id
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(email_addr) AS emails
               FROM email_addr
             GROUP
                 BY name_id ) AS e
        ON e.name_id = contact_info.name_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ntlarson
    What I am getting is one row for every item in the many tables. In other words for each phone number or email address I am getting all of the other contact info repeated. like this (if there is only on number and one email address it is listed normally):
    what you are seeing is called cross-join effects

    these are inescapable if you join more than one one-to-many relationships

    however, if you aggregate both of the two one-to-many relationships involved, so that there is only one row of each per contact, then you can simply join them in as shown
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Posts
    2
    Quote Originally Posted by r937
    nooooooooooooooooooooo!!!!!!!!!!!!!!!!!!!!!!!!

    one query, and then two more queries inside loops?

    it is to cry

    sez you
    Code:
    SELECT contact_info.first_name
         , contact_info.last_name
         , contact_info.street_addr
         , contact_info.city
         , contact_info.state
         , contact_info.zip
         , p.phones
         , e.emails 
      FROM contact_info
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(phone_number) AS phones
               FROM phone_numbers
             GROUP
                 BY name_id ) AS p
        ON p.name_id = contact_info.name_id
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(email_addr) AS emails
               FROM email_addr
             GROUP
                 BY name_id ) AS e
        ON e.name_id = contact_info.name_id

    Thank you so much. That is precisely what I was trying to accomplish. I had played with GROUP BY and LEFT OUTER JOINS etc but couldn't quite get any of them or the combination of them to do what I wanted and I figured it had to do with improper syntax on the query to begin with. I am still a beginner so I have not done much with sub queries etc like you showed here and that is what I was missing. Thanks again.

  6. #6
    Join Date
    Nov 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    nooooooooooooooooooooo!!!!!!!!!!!!!!!!!!!!!!!!

    one query, and then two more queries inside loops?

    it is to cry

    sez you
    Code:
    SELECT contact_info.first_name
         , contact_info.last_name
         , contact_info.street_addr
         , contact_info.city
         , contact_info.state
         , contact_info.zip
         , p.phones
         , e.emails 
      FROM contact_info
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(phone_number) AS phones
               FROM phone_numbers
             GROUP
                 BY name_id ) AS p
        ON p.name_id = contact_info.name_id
    LEFT OUTER
      JOIN ( SELECT name_id
                  , GROUP_CONCAT(email_addr) AS emails
               FROM email_addr
             GROUP
                 BY name_id ) AS e
        ON e.name_id = contact_info.name_id
    Hi there,

    I have a very similar situation, many records in the units_sub table that join to the units_main table. Using your code, I wired up the following:

    Code:
    $query = "SELECT units_sub.FACID
         , units_sub.Long_Name
         , a.asgmt_name
      FROM units_sub
    LEFT OUTER
      JOIN ( SELECT sub_key
                  , GROUP_CONCAT(ASGMT_Name) AS asgmt_name
               FROM units_main
             GROUP
                 BY sub_key ) AS a
        ON a.sub_key = units_sub.sub_key WHERE units_sub.FACID = '$real_facid'" ;
    
    $result = mysql_query($query) or die(mysql_error());
    		
    while ($rows = mysql_fetch_array($result))
    			
        {
    	echo $rows['ASGMT_Name'];
    	echo $rows['Long_Name'];
        }
    but I only get data from units_sub to print?

    I'd like to get

    units_main
    ----units_sub-join
    ----units_sub-join
    units_main
    ----units_sub-join
    ect...

    any pointers?

    thanks!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbecker88 View Post
    but I only get data from units_sub to print?
    no idea, sorry

    the query looks okay, so i would check your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2011
    Posts
    16
    this is what the tables look like, since they were imported to phpmyadmin from excel, they dont' have a primary key...i could of course add one

    units_main:
    FACID----sub_key-----ASGMT_Name
    AR01____1000_______LEGAL COMMAND
    AR01____1001_______OPPS 67
    AR02____1002_______REG D


    units_sub:
    FACID-----sub_key-----Long_Name
    AR01_____1000_______cmd a, b and sometimes c
    AR01_____1000_______secondary cmd structrue
    AR01_____1001_______opps seg 798 b
    AR02_____1002_______s cmd structure cc8


    I'm passing a FACID to the query via $_GET variable in URL. Based on this FACID, i'd like to query units_main, then join records from units_sub via sub_key.

    Right now, if I were to pass AR01, I would get this to print:
    cmd a, b and sometimes c
    secondary cmd structrue
    opps seg 798 b


    When I send AR01, I'd like this to print:

    LEGAL COMMAND
    ---cmd a, b and sometimes c
    ---secondary cmd structrue
    OPPS 67
    ---opps seg 798 b


    any ideas?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    seems to me that you do not want the GROUP_CONCAT after all, just to display the one-to-many relationship with multiple detail rows

    this is the query you should run...
    Code:
    SELECT m.FACID
         , m.sub_key
         , m.ASGMT_Name
         , s.Long_Name
      FROM units_main AS m
    LEFT OUTER
      JOIN units_sub AS s
        ON s.FACID = m.FACID
       AND s.sub_key = m.sub_key
    ORDER
        BY m.FACID
         , m.sub_key
         , s.Long_Name
    to achieve that "indented" or "interleaved" output, that's the job of your application language as you loop over the rows of the query result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    seems to me that you do not want the GROUP_CONCAT after all, just to display the one-to-many relationship with multiple detail rows

    this is the query you should run...
    Code:
    SELECT m.FACID
         , m.sub_key
         , m.ASGMT_Name
         , s.Long_Name
      FROM units_main AS m
    LEFT OUTER
      JOIN units_sub AS s
        ON s.FACID = m.FACID
       AND s.sub_key = m.sub_key
    ORDER
        BY m.FACID
         , m.sub_key
         , s.Long_Name
    to achieve that "indented" or "interleaved" output, that's the job of your application language as you loop over the rows of the query result set
    thanks for your help!
    this will be a simple web page, not sure by what you mean by application language?

    please forgive my ignorance!

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what software do you use to connect to mysql?

    perl? php? asp? coldfusion?

    that's what i meant by your application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    what software do you use to connect to mysql?

    perl? php? asp? coldfusion?

    that's what i meant by your application language
    lol; i'm half brain dead today!

    php is what i use

  13. #13
    Join Date
    Nov 2011
    Posts
    16
    Code:
    $result = mysql_query($query) or die(mysql_error());
    
    $res = array();
    while ($rows = mysql_fetch_array($result))
    { 
        if (!array_key_exists($res, $rows['ASGMT_Name']))
                {
                    $res[$rows['ASGMT_Name']] = array();
                }
                $values = $res[$rows['ASGMT_Name']];
                array_push($values,$rows['Long_Name']);
    }
    foreach($res as $key => $value )
    {
            echo "$key<br>";
            echo "$value<br>";
    }
    Here's the output:
    LEGAL COMMAND
    Array
    OPPS 67
    Array

    Seems like the $value of each $key is another Array? If so how do i access this Array?
    Last edited by dbecker88; 11-18-11 at 21:57.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should probably ask a moderator to move this thread to the php forum

    use the little icon to the right of the permalink for your post to report the request

    me, i don't do php, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2011
    Posts
    16
    Quote Originally Posted by r937 View Post
    you should probably ask a moderator to move this thread to the php forum

    use the little icon to the right of the permalink for your post to report the request

    me, i don't do php, sorry
    thanks again for your help, I finally fig'd out my php problem.

    Another question:

    How would i add a 3rd table to the query, here are the tables?

    units_branch:
    FACID-----branch_name
    AR01_____B1
    AR02_____B7

    units_main:
    FACID----sub_key-----ASGMT_Name
    AR01____1000_______LEGAL COMMAND
    AR01____1001_______OPPS 67
    AR02____1002_______REG D


    units_sub:
    FACID-----sub_key-----Long_Name
    AR01_____1000_______cmd a, b and sometimes c
    AR01_____1000_______secondary cmd structrue
    AR01_____1001_______opps seg 798 b
    AR02_____1002_______s cmd structure cc8

    I tried to use the same syntax, but got stuck??

    Code:
     $query = "SELECT
    	 b.branch
         , m.sub_key
         , m.ASGMT_Name
         , s.Long_Name
      FROM units_branch AS b
    LEFT OUTER
      JOIN units_main AS m
      ON m.FACID = b.FACID
    ------stuck here??------------
    FROM units_main AS m
    LEFT OUTER
      JOIN units_sub AS s
        ON s.sub_key = m.sub_key
        WHERE m.FACID = '$real_facid'
       ORDER
        BY m.ASGMT_Name
         , s.Long_Name";

    When I send AR01, I'd print:

    B1
    LEGAL COMMAND
    ---cmd a, b and sometimes c
    ---secondary cmd structrue
    OPPS 67
    ---opps seg 798 b


    If you can help with the query, I think i got the php bit under control this time!

    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
  •