CyberX

Unwired mind project homepage

How to compare MySQL multiple JOIN query without duplicate rows

When I create an API for DBX engine that should work like a charm to compare multiple INNER JOIN MySQL queries I was found some possibility of Data Base server to return duplicate rows in result.

For example, we use some instructions for collect some fields from 5 different tables:

SELECT 

`revolver__nodes`.field_id, 
`revolver__nodes`.field_title, 
`revolver__nodes`.field_content, 
`revolver__nodes`.field_description, 
`revolver__nodes`.field_user, 
`revolver__nodes`.field_time, 
`revolver__nodes`.field_route, 
`revolver__nodes`.field_category, 
`revolver__nodes`.field_published, 
`revolver__nodes`.field_mainpage, 
`revolver__nodes`.field_country, 

`revolver__categories`.field_id, 
`revolver__categories`.field_title, 
`revolver__categories`.field_description, 

`revolver__comments`.field_id, 
`revolver__comments`.field_node_id, 
`revolver__comments`.field_user_id, 
`revolver__comments`.field_user_name, 
`revolver__comments`.field_content, 
`revolver__comments`.field_time, 
`revolver__comments`.field_published, 
`revolver__comments`.field_country, 
`revolver__comments`.field_id, 

`revolver__users`.field_id, 
`revolver__users`.field_nickname, 
`revolver__users`.field_avatar, 

`revolver__subscriptions`.field_id, 
`revolver__subscriptions`.field_node_id, 
`revolver__subscriptions`.field_user_id, 
`revolver__subscriptions`.field_user_name, 
`revolver__subscriptions`.field_user_email, 
`revolver__subscriptions`.field_subscription_enabled 

FROM `revolver__nodes` 

INNER JOIN `revolver__categories` ON(`revolver__nodes`.field_category=`revolver__categories`.field_id) 
INNER JOIN `revolver__comments` ON(`revolver__nodes`.field_id=`revolver__comments`.field_node_id) 
INNER JOIN `revolver__users` ON(`revolver__comments`.field_user_name=`revolver__users`.field_nickname) 
INNER JOIN `revolver__subscriptions` ON(`revolver__users`.field_nickname=`revolver__subscriptions`.field_user_name);

Result of this MySQL query have consist double portion of data because some rows are duplicated. How to fix it?

Simple way is to add DISTINCT + GROUP BY like a code below:

SELECT DISTINCT 

`revolver__nodes`.field_id, 
`revolver__nodes`.field_title, 
`revolver__nodes`.field_content, 
`revolver__nodes`.field_description, 
`revolver__nodes`.field_user, 
`revolver__nodes`.field_time, 
`revolver__nodes`.field_route, 
`revolver__nodes`.field_category, 
`revolver__nodes`.field_published, 
`revolver__nodes`.field_mainpage, 
`revolver__nodes`.field_country, 

`revolver__categories`.field_id, 
`revolver__categories`.field_title, 
`revolver__categories`.field_description, 

`revolver__comments`.field_id, 
`revolver__comments`.field_node_id, 
`revolver__comments`.field_user_id, 
`revolver__comments`.field_user_name, 
`revolver__comments`.field_content, 
`revolver__comments`.field_time, 
`revolver__comments`.field_published, 
`revolver__comments`.field_country, 
`revolver__comments`.field_id, 

`revolver__users`.field_id, 
`revolver__users`.field_nickname, 
`revolver__users`.field_avatar, 

`revolver__subscriptions`.field_id, 
`revolver__subscriptions`.field_node_id, 
`revolver__subscriptions`.field_user_id, 
`revolver__subscriptions`.field_user_name, 
`revolver__subscriptions`.field_user_email, 
`revolver__subscriptions`.field_subscription_enabled 

FROM `revolver__nodes` 

INNER JOIN `revolver__categories` ON(`revolver__nodes`.field_category=`revolver__categories`.field_id) 
INNER JOIN `revolver__comments` ON(`revolver__nodes`.field_id=`revolver__comments`.field_node_id) 
INNER JOIN `revolver__users` ON(`revolver__comments`.field_user_name=`revolver__users`.field_nickname) 
INNER JOIN `revolver__subscriptions` ON(`revolver__users`.field_nickname=`revolver__subscriptions`.field_user_name) 

GROUP BY `revolver__comments`.field_id;

Try to perform your examples in PMA console and look at results. There are no any duplicate rows in result after adding DISTINCT and GROUP BY clauses.

Comments …

You can write here as guest with moderation. Please confirm your person if you have an account or register.

Add a comment as :
Captcha:
Pattern:
Repeat: