CODEWITHSUNDEEP
mysqljoindistinct
damon
damon

Reputation: 15128

Getting distinct records from a mysql query

In my application, there are publishers and categories. One publisher can belong to several categories. When I make my mysql transaction, it will return the same publisher record for each category it belongs to. Here's the query:

SELECT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url,
    grdirect_category.name AS catname
FROM
    grdirect_publisher
JOIN
    grdirect_publisher_categories
    ON
    grdirect_publisher.id = grdirect_publisher_categories.publisher_id
JOIN
    grdirect_category
    ON
    grdirect_publisher_categories.category_id = grdirect_category.id

returns:

name    short_description   thumb_image url catname
------------------------------------------------------------
Foo Lorem Ipsum...      images/pic.png  d.com   Video Games
Foo Lorem Ipsum...      images/pic.png  d.com   Music
Bar Blah Blah...        images/tic.png  e.com   Music

Essentially, Foo should only show up once in the results.

Upvotes: 3

Views: 9155

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You can use DISTINCT but if any column in your result set has a distinct value, it forces the row to be duplicated. If you want to reduce the list to one row per name, then you have to use DISTINCT and you have to omit the catname column:

SELECT DISTINCT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url
FROM
. . .

Another solution instead of using DISTINCT is MySQL's aggregate function GROUP_CONCAT() which allows you to take multiple values in a group and produce a comma-separated list:

SELECT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url,
    GROUP_CONCAT(grdirect_category.name) AS catname
. . .
GROUP BY grdirect_publisher.id;

So you have to decide how you want the result set to look to get the right solution.

Upvotes: 4

Related Questions