Reputation: 203
Join 2 children tables with a parent tables without duplicated
Problem
I have 3 tables: People, Phones and Emails. Each person has an UNIQUE ID, and each person can have multiple numbers or multiple emails.
Simplified it looks like this:
+---------+----------+
| ID | Name |
+---------+----------+
| 5000003 | Amy |
| 5000004 | George |
| 5000005 | John |
| 5000008 | Steven |
| 8000009 | Ashley |
+---------+----------+
+---------+-----------------+
| ID | Number |
+---------+-----------------+
| 5000005 | 5551234 |
| 5000005 | 5154324 |
| 5000008 | 2487312 |
| 8000009 | 7134584 |
| 5000008 | 8451384 |
+---------+-----------------+
+---------+------------------------------+
| ID | Email |
+---------+------------------------------+
| 5000005 | [email protected] |
| 5000005 | [email protected] |
| 5000008 | [email protected] |
| 5000008 | [email protected] |
| 5000008 | [email protected] |
| 8000009 | [email protected] |
| 5000004 | [email protected] |
+---------+------------------------------+
I am trying to joining them together without duplicates. It works great, when I try to join only Emails with People or only Phones with People.
SELECT People.Name, People.ID, Phones.Number
FROM People
LEFT OUTER JOIN Phones ON People.ID=Phones.ID
ORDER BY Name, ID, Number;
+----------+---------+-----------------+
| Name | ID | Number |
+----------+---------+-----------------+
| Steven | 5000008 | 8451384 |
| Steven | 5000008 | 24887312 |
| John | 5000005 | 5551234 |
| John | 5000005 | 5154324 |
| George | 5000004 | NULL |
| Ashley | 8000009 | 7134584 |
| Amy | 5000003 | NULL |
+----------+---------+-----------------+
SELECT People.Name, People.ID, Emails.Email
FROM People
LEFT OUTER JOIN Emails ON People.ID=Emails.ID
ORDER BY Name, ID, Email;
+----------+---------+------------------------------+
| Name | ID | Email |
+----------+---------+------------------------------+
| Steven | 5000008 | [email protected] |
| Steven | 5000008 | [email protected] |
| Steven | 5000008 | [email protected] |
| John | 5000005 | [email protected] |
| John | 5000005 | [email protected] |
| George | 5000004 | [email protected] |
| Ashley | 8000009 | [email protected] |
| Amy | 5000003 | NULL |
+----------+---------+------------------------------+
However, when I try to join Emails and Phones on People - I get this:
SELECT People.Name, People.ID, Phones.Number, Emails.Email
FROM People
LEFT OUTER JOIN Phones ON People.ID = Phones.ID
LEFT OUTER JOIN Emails ON People.ID = Emails.ID
ORDER BY Name, ID, Number, Email;
+----------+---------+-----------------+------------------------------+
| Name | ID | Number | Email |
+----------+---------+-----------------+------------------------------+
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 8451384 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| Steven | 5000008 | 24887312 | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| John | 5000005 | 5154324 | [email protected] |
| John | 5000005 | 5154324 | [email protected] |
| George | 5000004 | NULL | [email protected] |
| Ashley | 8000009 | 7134584 | [email protected] |
| Amy | 5000003 | NULL | NULL |
+----------+---------+-----------------+------------------------------+
What happens is - if a Person has 2 numbers, all his emails are shown twice (They can not be sorted! which means they can not be removed by @last)
What I want:
Bottom line, playing with the @last, I want to end up with somethig like this, but @last won't work if I don't arrange ORDER columns in the righ way - and this seems like a big problem..Orderin the email column. Because seen from the example above:
Steven has 2 phone number and 3 emails. The JOIN Emails with Numbers happens with each email - thus duplicated values that can not be sorted (SORT BY does not work on them).
**THIS IS WHAT I WANT**
+----------+---------+-----------------+------------------------------+
| Name | ID | Number | Email |
+----------+---------+-----------------+------------------------------+
| Steven | 5000008 | 8451384 | [email protected] |
| | | 24887312 | [email protected] |
| | | | [email protected] |
| John | 5000005 | 5551234 | [email protected] |
| | | 5154324 | [email protected] |
| George | 5000004 | NULL | [email protected] |
| Ashley | 8000009 | 7134584 | [email protected] |
| Amy | 5000003 | NULL | NULL |
+----------+---------+-----------------+------------------------------+
Now I'm told that it's best to keep emails and number in separated tables because one can have many emails. So if it's such a common thing to do, what isn't there a simple solution?
I'd be happy with a PHP Solution aswell.
What I know how to do by now that satisfies it, but is not as pretty.
If I do it with GROUP_CONTACT I geat a satisfactory result, but it doesn't look as pretty: I can't put a "Email type = work" next to it.
SELECT People.Ime,
GROUP_CONCAT(DISTINCT Phones.Number),
GROUP_CONCAT(DISTINCT Emails.Email)
FROM People
LEFT OUTER JOIN Phones ON People.ID=Phones.ID
LEFT OUTER JOIN Emails ON People.ID=Emails.ID
GROUP BY Name;
+----------+----------------------------------------------+---------------------------------------------------------------------+
| Name | GROUP_CONCAT(DISTINCT Phones.Number) | GROUP_CONCAT(DISTINCT Emails.Email) |
+----------+----------------------------------------------+---------------------------------------------------------------------+
| Steven | 8451384,24887312 | [email protected],[email protected],[email protected] |
| John | 5551234,5154324 | [email protected],[email protected] |
| George | NULL | [email protected] |
| Ashley | 7134584 | [email protected] |
| Amy | NULL | NULL |
+----------+----------------------------------------------+---------------------------------------------------------------------+
Upvotes: 8
Views: 139
Answers (3)
Reputation: 23
Fist create temporary table Name temp_tb than insert record. Then make partition using Row_Number() and delete all the record that list more than once. Like given blow.
Insert into temp_tb SELECT People.Name, People.ID, Phones.Number, Emails.Email
FROM People
LEFT OUTER JOIN Phones ON People.ID = Phones.ID
LEFT OUTER JOIN Emails ON People.ID = Emails.ID
ORDER BY Name, ID, Number, Email;
SELECT * from temp_tb
With A as
(
select People.Name, People.ID, Phones.Number, Emails.Email ROW_NUMBER()
OVER (Partition by ID ORDER BY Fee_temp.Name) As Number from temp_tb
)
DELETE FROM A WHERE Number>1
SELECT * FROM temp_tb
Hope it helps you.
Upvotes: 0
Reputation: 4145
If I've understand correctly you also need to associate other fields to email (eg: type).
Using group_concat you can add the email type to the results, in my example ";" separate mail from type and "," separate the results.
SELECT u.name,
GROUP_CONCAT( distinct p.phone),
GROUP_CONCAT( distinct 'mail=',e.mail,';type=' ,type)
FROM people u
LEFT OUTER JOIN phone p ON u.id=p.id
LEFT OUTER JOIN mail e ON u.id=e.id
GROUP BY u.id
Upvotes: 1
Reputation: 8472
What you want isn't actually what you want at all, if that makes any sense... you can't realistically do anything programatically with your database output, you need to do something with it (unless you're just running a query directly on your database).
And since you stated "I'd be happy with a PHP Solution aswell." ... what you really want is something like a PHP "User" object, something like (this is all hypothetical of course):
<?php
class User {
private $_id;
private $_telNos = array();
private $_emails = array();
public function __construct($iUserId = null, $oDatabaseAbstractionObject = null) {
if(!is_null($iUserId)) $this->setId($iUserId);
if(!is_null($oDatabaseConnectionObject)) $this->load($iUserId, $oDatabaseAbstractionObject);
}
public setId($iUserId) {
$this->_id = (int) $iUserId;
}
public getId() {
return $this->_id;
}
/* telephone and email setters and getters */
public function load($iUserId, $oDatabaseAbstractionObject) {
/* error trapping - for example if $iUserId is null */
$this->setTelNos($oDatabaseAbstractionObject->readTelNos($iUserId));
$this->setEmails($oDatabaseAbstractionObject->readEmails(iUserId));
}
}
?>
Your database abstraction object then just needs to do some very simple queries to read your user, user email and phone number tables and return the results as arrays which you can then put straight into your PHP object. For example:
<?php
/**
* this implements a database connection object as a private class member
*/
class DBUser {
private $_conn;
/* constructor other functionality */
/**
* method to pass an SQL query to the database and return an array of results
*/
public function readTelNos($iUserId) {
return $this->_conn->read("SELECT `number` from `tel` WHERE `user_id` = " . (int) $iUserId);
}
}
?>
This breaks your problem down into smaller, easier to handle problems, and wraps them all up in nice PHP objects that you can then actually work with.
Your user object will have a list if phone numbers and emails that can easily be retrieved with something like $oUser->getEmails(); and if they're stored as an associative array inside the object you could even retrieve them by "label" $oUser->getEmail('work');
Upvotes: 1
Related Questions
- JOIN two tables, sort by field on second table, no duplicates
- Order by combined joins
- MYSQL Sorting By Multiple Join Children
- Mysql Query Join two table Order by one table column
- how to join tables with a special sort/mysql-php
- MYSQL: Join two sorted result sets
- Using SQL join to sort elements of the same table
- MySQL join - ordering results via another table PHP
- Joining two tables so I can use ORDER BY
- Joining two tables with multiple field sorting in MySQL