Is it right to use GROUP BY in this situation.

["Waine Ling" <waine@xxxxxxxxxx>]

Good day to you all.

This is not a problem so much as checking that I am not missing
something obvious, before I change the way my mind deals with this type
of situation in MySQL.

I have two tables

Table c:

+-----+-----+
| c1  |  c2 |
+-----+-----+
| 1   | foo |
| 2   | bar |
+-----+-----+

Table d:

+-----+-----------+-------+
| d1  |  d2       |  d3   |
+-----+-----------+-------+
| foo | red card  | shop1 |
| bar | blue card | shop1 |
| foo | red card  | shop2 |
| bar | blue card | shop2 |
+-----+-----------+-------+

Table d deliberately has multiple entries in d1

I want to select data from both table c and d so that I return

+-----+-----+-_---------+
| c1  |  c2 | d2        |
+-----+-----+-----------+
| 1   | foo | red card  |
| 2   | bar | blue card |
+-----+-----+-----------+

Basically I want the all of the data from table c but need to get some
extra info e.g. descriptions from table d.

Using:

SELECT c.c1,c.c2,d.d2 FROM c LEFT JOIN d ON c2=d1

returns

+-----+-----+-_---------+
| c1  |  c2 | d2        |
+-----+-----+-----------+
| 1   | foo | red card  |
| 1   | foo | red card  |
| 2   | bar | blue card |
| 2   | bar | blue card |
+-----+-----+-----------+

Due the duplications within d1.

I can GROUP BY c.c1 (unique row number) to return the results I want but
I am not sure if this is the intended/best way to get this result.

Regards

Waine.


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread92898@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php