Page 1 of 1

Howto check for duplaicte entries in result and remove

Posted: Tue May 25, 2004 10:56 am
by Zooter
Hi,

I'm doing a join select query on two tables. One is a client table and the other a policies table. It is a one to many rlationship, eg. one client can have 1 or more policies, but a policy only belongs to one client.

Now in my resultset, I only want the following to happen. There is only one field from the policies table that I need in the result (eg. broker field), and the rest is all from the client table. If a client has two policies though, it will return 2 entries in the result, where the only field that is different is the broker field.

How do I make it display only one entry, but concatenates the broker fields (eg. broker1, broker2) so I can then display them in one box, and therefore only one row is displayed for this query?

My mySQL query looks like this...

Code: Select all

SELECT DISTINCT *
FROM client INNER JOIN policies WHERE client.IDNumber = policies.ClientID ORDER BY Surname, Initials
Thanks
Ettiene

Posted: Tue May 25, 2004 11:12 am
by lostboy
Use code to run the display. The recordset, from your description, is returning the correct values.

Posted: Tue May 25, 2004 12:17 pm
by evilMind
You can use CONCAT_WS to concat the two fields together, although you may want to specify the fields you want to choose instead of using *;

eg:

Code: Select all

SELECT DISTINCT row1,row2,CONCAT_WS(' -- ',a.row1,b.row7) AS `some_name` FROM table_1 a , table_2 b WHERE a.row1 = b.row2 ORDER BY a.row1 ASC
http://www.mysql.com/concat_ws
or, if you don't want to use a seperator
http://www.mysql.com/concat


example:

Code: Select all

mysql> select DISTINCT a.userID,a.username,CONCAT_WS(' -- ',a.userID,b.username) AS `id -- name` from foo_table a, foo_table2 b WHERE a.username = b.username;
+--------+----------+------------+
| userID | username | id -- name |
+--------+----------+------------+
|      1 | blah     | 1 -- blah  |
|      2 | garbl    | 2 -- garbl |
|      3 | argv     | 3 -- argv  |
+--------+----------+------------+
3 rows in set (0.00 sec)

Posted: Tue May 25, 2004 12:38 pm
by lostboy
EvilMind. That is not the solution to the problem. He has two records with a different name

Code: Select all

+--------+----------+ 
| userID | username |
+--------+----------+
|      1 | blah     | 
|      2 | garbl    |
and he is wondering how to make those two records appear in one cell (I am assuming a table cell in the html output) which he can do wth code...he cannot write a query to do this

Posted: Tue May 25, 2004 1:32 pm
by Weirdan
Actually Zooter looking for the function group_concat(). Unfortunately, it appears only in MySQL >= 4.1

So if you have older MySQL there is no efficient way to build such a query and you have to do this 'grouping' in your script (I assume that query executed from a script or something).
If you have MySQL >= 4.1 - just use this function and have the GROUP BY clause in your query:

Code: Select all

.......... group by client.id // assuming that id is UNIQUE key for client table