Geek asks and answers

MySQL LEFT JOIN duplicates results

I had a problem with the implementation of a module in which one projectcan belong to several categories. Example: the project "PHP Programmer" belongs to the categories: Programming, PHP.

Assuming the following query (select projects belonging to categories 1,3,11):

SELECT projects.* FROM projects 
    LEFT JOIN pojects_category on projects.id = pojects_category.project_id 
    WHERE pojects_category.category_id IN (1,3,11) and projects.id='94'`

I get the same project that returns twice , because project_categorythere are 2 in the table , for project_id=94

Table projects_categoryScheme:

CREATE TABLE IF NOT EXISTS `pojects_category` (
  `project_id` int(10) NOT NULL,
  `category_id` int(10) NOT NULL,
  KEY `category_id` (`category_id`),
  KEY `project_id` (`project_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `pojects_category` (`project_id`, `category_id`) VALUES
(94, 3),
(94, 1);

Am I missing something?

Solution : use GROUP BYorDISTINCT

+5
source share
2 answers

, . , DISTINCT .

, , . DISTINCT, .

:

  • , WHERE, IS NULL/IS NOT NULL, LEFT JOIN INNER JOIN, . (. : fooobar.com/questions/201333/...)

  • GROUP BY DISTINCT :

    1/ . GROUP BY , - , /.

    2/GROUP BY ORDER BY ( mysql), , , . , , , . (, , , )

.

+8

"IN", :

SELECT projects.*
FROM projects      
where projects.id in (select project_id
                      from projects_category
                      WHERE pojects_category.category_id IN (1,3,11)
                     ) and
      projects.id='94'

"in" .

0

More articles:


All Articles