CODEWITHSUNDEEP
mysqlsql
Mark Richards
Mark Richards

Reputation: 434

need to filter duplicate record from a particular selected records

need to filter duplicate record from a particular selected records

SQL table site_metatag and i am using MySQL client version: 5.5.30 

metatag_id | store_id | name
    1          0        copyright   
    2          0        author  
    3          0        robots  
    4          0        googlebot   
    5          0        revisit-after
    6          0        google-site-verification    
    9          1        google-site-verification    
    8          1        revisit-after
    10         1        googlebot   
    11         1        robots
    12         2        googlebot
    13         2        robots
    14         2        google-site-verification

need those record from store_id in 1 and 0 but name will be unquie like distinct() function give uniqe all record , but i need record that not used more then 1 time

metatag_id | store_id | name
     1         0        copyright   
     2         0        author  
     3         0        robots

i try

SELECT * FROM site_metatag v 
where metatag_id NOT IN
(
   select metatag_id from site_metatag p where v.name=p.name
) 
AND v.store_id in (0,1)

but not working..

Upvotes: 0

Views: 53

Answers (3)

nicolae-stelian
nicolae-stelian

Reputation: 344

I think you want this:

SELECT 
    metatag_id, store_id, `name`
FROM
    site_metatag v
group by `name`

Or this, if you want to group by number of occurrences(in this case 1 -> cnt = 1).

SELECT 
    metatag_id, store_id, v.`name`, a.cnt
FROM
    site_metatag v
        inner join
    (select 
        `name`, count(*) as cnt
    from
        site_metatag
    group by `name`
    having cnt = 1) as a ON (v.`name` = a.`name`)
group by v.`name`

Upvotes: 2

Yazan
Yazan

Reputation: 6082

what about this:

SELECT * FROM site_metatag WHERE v.store_id in (0,1) GROUP BY name

Upvotes: 0

Shariati
Shariati

Reputation: 119

use distinct() function. see Distinct MYSQL

Upvotes: 0

Related Questions