MySQL Join Query without duplicate values

Question

invoice table

 SELECT id, fname, gtotal, `date` FROM invoice WHERE id = 1;


| id |   fname | gtotal |                  date |
|----|---------|--------|-----------------------|
|  1 | Brandon |    860 | May, 11 2016 00:00:00 |

invoice_contents table,

SELECT * FROM invoice_contents WHERE invoice_id = 1;


| id | invoice_id |       item | price | quantity | discount | total |
|----|------------|------------|-------|----------|----------|-------|
|  1 |          1 |   Dextrose |    10 |       10 |        5 |    95 |
|  2 |          1 |   Nescaine |    20 |       30 |       10 |   540 |
|  3 |          1 | Anticavity |    30 |       10 |       25 |   225 |

This JOIN query

SELECT invoice.id, invoice.fname, invoice_contents.item,
       invoice_contents.price, invoice_contents.quantit,
       invoice_contents.discount, invoice_contents.total, 
       invoice.gtotal
  FROM invoice_contents
 INNER JOIN invoice ON invoice_contents.invoice_id=1 AND invoice.id=1;

gives this result.

 | id |   fname |       item | price | quantity | discount | total | gtotal |
 |----|---------|------------|-------|----------|----------|-------|--------|
 |  1 | Brandon |   Dextrose |    10 |       10 |        5 |    95 |    860 |
 |  1 | Brandon |   Nescaine |    20 |       30 |       10 |   540 |    860 |
 |  1 | Brandon | Anticavity |    30 |       10 |       25 |   225 |    860 |

I need this result.

| id |   fname |       item | price | quantity | discount | total | gtotal |
|----|---------|------------|-------|----------|----------|-------|--------|
|  1 | Brandon |   Dextrose |    10 |       10 |        5 |    95 |    860 |
|    |         |   Nescaine |    20 |       30 |       10 |   540 |        |
|    |         | Anticavity |    30 |       10 |       25 |   225 |        |

I am just a beginner in MySQL. I have been trying from this morning to get this kind of output by experimenting on different combinations please help me out.

It's odd that no relationship exists between these tables. But, putting that to one side, you're confusing data retrieval and data display - the latter being more properly addressed in application level code (e.g. a simple php loop) — Strawberry
– Strawberry, Commented May 20, 2016 at 11:44
Though not explicitly set in MySQL, the relationship is created by assigning the same number as the id field in the invoice table to the invoice_contents table's invoice_id field (courtesy: php's mysqli_insert_id) — Rex
– Rex, Commented May 20, 2016 at 11:56

Taras · Accepted Answer · 2016-05-20 11:30:55Z

2

@Rex, Your select is correct. You should make desired output using some script e.g. PHP.

answered May 20, 2016 at 11:30

Taras

5923 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rex Over a year ago

That was my gut feeling too. This is ultimately intended for a PHP MySQL application.

Bernd Buffen · Accepted Answer · 2016-05-20 13:14:17Z

1

try this in SQL:

in this Query i save everytime fname in a variable is not equal and at the next row i compare it and return a empty string is it equal. and the same for gtotal.

the cross join is only to initialize the variables.

in this case it is important that the rows are order by fname to ensure that the same name is behind each other

SELECT
    invoice.id,
    IF(@last_fname = invoice.fname, '', (@last_fname:=invoice.fname)) as fname,
    invoice_contents.item,
    invoice_contents.price,
    invoice_contents.quantity,
    invoice_contents.discount,
    IF(@last_gtotal = invoice.gtotal, '', (@last_gtotal:=invoice.gtotal)) as gtotal
FROM invoice_contents
INNER JOIN invoice ON invoice_contents.invoice_id=1 AND invoice.id=1
CROSS JOIN ( select @last_fname := '' , @last_gtotal := '' ) AS parameter
ORDER BY invoice.fname;

Sample

MariaDB [bb]> SELECT
    ->     invoice.id,
    ->     IF(@last_fname = invoice.fname, '', (@last_fname:=invoice.fname)) AS fname,
    ->     invoice_contents.item,
    ->     invoice_contents.price,
    ->     invoice_contents.quantity,
    ->     invoice_contents.discount,
    ->     IF(@last_gtotal = invoice.gtotal, '', (@last_gtotal:=invoice.gtotal)) AS gtotal
    -> FROM invoice_contents
    -> INNER JOIN invoice ON invoice_contents.invoice_id=1 AND invoice.id=1
    -> CROSS JOIN ( SELECT @last_fname:='' , @last_gtotal:='' ) AS parameter
    -> ORDER BY invoice.fname;
+----+---------+------------+-------+----------+----------+--------+
| id | fname   | item       | price | quantity | discount | gtotal |
+----+---------+------------+-------+----------+----------+--------+
|  1 | Brandon | Dextrose   | 10.00 |       10 |     5.00 | 860.00 |
|  1 |         | Nescaine   | 20.00 |       30 |    10.00 |        |
|  1 |         | Anticavity | 30.00 |       10 |    25.00 |        |
+----+---------+------------+-------+----------+----------+--------+
3 rows in set, 1 warning (0.00 sec)

MariaDB [bb]>

edited May 20, 2016 at 13:14

answered May 20, 2016 at 11:35

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

7 Comments

O. Jones Over a year ago

Because the original poster is a beginner, may I suggest you edit this answer to explain what's going on?

Rex Over a year ago

Thanks. Running your query in SQLFIDDLE demo shows the desired output. But in my wamp setup using both mysql cli and adminer.php (similar to phpmyadmin) I get the duplicate values

Bernd Buffen Over a year ago

@Rex - can you please post some output

Rex Over a year ago

Working SQL Fiddle: sqlfiddle.com/#!9/b29412/15 Not the expected output - postimg.org/image/twnwnvy2p

Rex Over a year ago

Ran the same query in MySQL CLI but got this output postimg.org/image/twnwnvy2p. Also tried in my Linux LAMP setup got the same postimg.org/image/jsrm20jwh

|

Collectives™ on Stack Overflow

MySQL Join Query without duplicate values

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related