one-to-many join complication

ntlarson · #1 (**permalink**) 06-11-08, 12:34

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

healdem · #2 (**permalink**) 06-11-08, 13:12

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.

r937 · #3 (**permalink**) 06-11-08, 13:30

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

r937 · #4 (**permalink**) 06-11-08, 13:41

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

ntlarson · #5 (**permalink**) 06-11-08, 15:45

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.

dbecker88 · #6 (**permalink**) 11-16-11, 22:10

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

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!

r937 · #7 (**permalink**) 11-17-11, 05:49

Quote:

Originally Posted by dbecker88

but I only get data from units_sub to print?

no idea, sorry

the query looks okay, so i would check your data

dbecker88 · #8 (**permalink**) 11-17-11, 08:36

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?

r937 · #9 (**permalink**) 11-17-11, 10:41

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

dbecker88 · #10 (**permalink**) 11-17-11, 15:58

Quote:

Originally Posted by r937

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!

r937 · #11 (**permalink**) 11-17-11, 16:36

what software do you use to connect to mysql?

perl? php? asp? coldfusion?

that's what i meant by your application language

dbecker88 · #12 (**permalink**) 11-17-11, 16:40

Quote:

Originally Posted by r937

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

dbecker88 · #13 (**permalink**) 11-18-11, 20:52

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?

r937 · #14 (**permalink**) 11-18-11, 22:11

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

dbecker88 · #15 (**permalink**) 11-21-11, 14:32

Quote:

Originally Posted by r937

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!!

Internet Services

Web Development

Digital Marketing

Consumer Tech