CODEWITHSUNDEEP
mysqldistinct
Toniq
Toniq

Reputation: 4996

MYSQL select DISTINCT values in multiple columns

I have a table:

$taxonomy_table (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `type` varchar(10) NOT NULL,
                `title` varchar(255) NOT NULL,
                `media_id` int(11) NOT NULL,
                `playlist_id` int(11) NOT NULL,
                PRIMARY KEY (`id`),
                INDEX `media_id` (`media_id`),
                CONSTRAINT `mqap_taxonomy_ibfk_1` FOREIGN KEY (`media_id`) REFERENCES {$media_table} (`id`) ON DELETE CASCADE ON UPDATE CASCADE
            )";


media_id     type      title
------------------------------
23           tag       fashion
23           tag       beauty
23           category  foo
24           tag       fashion
24           category  foo

I want to select item with ALL these tags="fashion, beauty" and category="foo", so only media_id=23.

The query is:

$query = "SELECT media_id
                FROM {$taxonomy_table}
                WHERE type='tag' AND title IN ($arg) 
                AND type='category' AND title IN ($arg2)
                AND playlist_id=%d
                GROUP BY media_id
                HAVING count(DISTINCT title) = $total";

$arg = "fashion, beauty", $arg2="foo"

"SELECT media_id 
 FROM wp_mvp_taxonomy 
 WHERE type='tag' 
   AND title IN (%s,%s) 
   AND type='category' 
   AND title IN (%s) 
 GROUP BY media_id 
 HAVING count(DISTINCT title) = 3"

But this its not returning anything.

Upvotes: 0

Views: 42

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

You need OR, also add parenthesis because AND has precendence.

$query = "SELECT media_id
          FROM {$taxonomy_table}
          WHERE playlist_id=%d 
            AND (   ( type='tag' AND title IN ($arg) )
                 OR ( type='category' AND title IN ($arg2) )
                )
          GROUP BY media_id
          HAVING count(DISTINCT title) = $total";

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You probably want OR:

"SELECT media_id 
 FROM wp_mvp_taxonomy 
 WHERE (type='tag' AND title IN (%s,%s))
   OR  (type='category'  AND title IN (%s))  -- here
 GROUP BY media_id 
 HAVING count(DISTINCT title) = 3"

Original:

WHERE type = 'tag' ... AND type ='category' => always false

Upvotes: 1

Related Questions