Filtering duplicates while joining tables

Question

I need to select data from both tables and display it but my PHP file seems to be posting duplicates. I want to be able to pick up all of the descriptions and relate it to the ID's which are used in table1.

I managed to get some of it to work but it displays the name Jake twice is their anyway i can filter out the duplicates.

Thanks

Here is my code

$sql = "SELECT table1.name, table2.description
        FROM table1
        INNER JOIN table2
        on table1.id = table2.customerid";

$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {

        echo $row['name']."<br>";
        echo $row['description']."<br>";

    }

table1

id     name     
1      james            
2      jake            
3      aaron     
4      holly

table2

id     customerid   description
1      1            hey1
2      2            hey2
3      2            hey3
4      3            hey4
5      4            hey5

What is considered a duplicate? All entries seem different... — Willem Van Onsem, Oct 29 '14 at 23:35
You can use a [`DISTINCT`](http://stackoverflow.com/questions/8571902/mysql-select-only-unique-values-from-a-column) constraint. — Willem Van Onsem, Oct 29 '14 at 23:35

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

you can use a DISTINCT constraint.

If two rows are assumed to be "identical" only if the rows have the same values for all columns, you group them in one DISTINCT query:

SELECT DISTINCT table1.name,table2.description
FROM table1
INNER JOIN table2
ON table1.id = table2.customerid

Otherwise, you can use the GROUP BY statement. But then a problem arises: which "description" will you pick? Since there are two for Jake. MySQL has implemented several function aggregates. A potential candidate is MIN which returns the alphabetically sorted minimum. The query then reads:

SELECT table1.name,MIN(table2.description)
FROM table1
INNER JOIN table2
ON table1.id = table2.customerid
GROUP BY table1.name

edited May 23 '17 at 11:49

Community

1
1

answered Oct 29 '14 at 23:39

Willem Van Onsem

443,496
30
428
555

I did try to use DISTINCT but it prints out jake twice for some weird reason. – Hashey100 Oct 29 '14 at 23:51
Well as pointed out, this is because the description is different in the two rows. `DISTINCT` only works if **all** columns are identical. In the other case, you should use `GROUP BY`. – Willem Van Onsem Oct 29 '14 at 23:58

Filtering duplicates while joining tables

1 Answers1