CODEWITHSUNDEEP
mysql
gavin stanley
gavin stanley

Reputation: 1102

Eliminating unwanted rows from mysql result

I have a MySQL result (obtained by joining three tables) which looks like this:

  article_id |  article_name  | tag_id |    tag      | article_ref |  tag_ref
-----------------------------------------------------------------------------------
    00021    |   Red jumper   |   14   | fisherman   |    00021    |    14
    00023    | Fishermans knit|   13   |  batwing    |    00023    |    13       
    00023    | Fishermans knit|   14   | fisherman   |    00023    |    14  
    00023    | Fishermans knit|   15   | rollneck    |    00023    |    15    
    00025    | Green Guernsey |   14   | fisherman   |    00025    |    14

(the search being %fisherman%)
I have obtained this by searching for %like% results and comparing two tables - the article table and the tag table (with a relational table tagsrel).

I would like to eliminate extraneous rows of the same article_id (see first column). I am looking into doing this with

SELECT FROM (SELECT FROM /*original query */)

with perhaps a DISTINCT? although the rows are not unique, as you can see. Ideally I would like to just select the first with that id. so my results would be:

  article_id |  article_name  | tag_id |    tag     | article_ref |  tag_ref
-----------------------------------------------------------------------------------
    00021    |   Red jumper   |   14   | fisherman  |    00021    |    14
    00023    | Fishermans knit|   13   |  batwing   |    00023    |    13       
    00025    | Green Guernsey |   14   | fisherman  |    00025    |    14

My question really is can I apply (as above) another SELECT to this to eliminate said rows? Thanks in advance (I have been stuck on this for days now!!!)

SOLUTION: As per Juan Carlos Oropeza's answer below, with the alias added for inserting the query 

SELECT *
FROM ( SELECT *,
              @rn := if(@article = `article_id`,
                        @rn + 1,
                        if(@article := `article_id`, 1, 1)
                       ) as rn
       FROM (/*query*/) AS bob
       CROSS JOIN (SELECT @rn := 0, @article := '' ) as T
       ORDER BY `article_id`, tag_id 
     ) T
WHERE rn = 1
ORDER BY  `article_id`

Upvotes: 0

Views: 42

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

use variables to select the first one of each article_id

SQL DEMO

SELECT *
FROM ( SELECT *,
              @rn := if(@article = `article_id`,
                        @rn + 1,
                        if(@article := `article_id`, 1, 1)
                       ) as rn
       FROM yourTable
       CROSS JOIN (SELECT @rn := 0, @article := '' ) as T
       ORDER BY `article_id`, tag_id 
     ) T
WHERE rn = 1
ORDER BY  `article_id`

OUTPUT

enter image description here

ADITIONAL TIP:

SELECT bda_articles.article_name,bda_articles.article_id, bda_tags.*, bda_tagsrel.*
FROM bda_articles 
JOIN bda_tagsrel
    ON bda_articles.article_id = bda_tagsrel.article_ref                    
JOIN bda_tags
    ON bda_tagsrel.tag_ref=bda_tags.tag_id          
WHERE (`article_name` LIKE "%fisherman%"
   OR `tag` LIKE "%fisherman%")
  AND tag_ref  = (SELECT MIN(tag_ref) 
                  FROM bda_tagsrel TAG
                  WHERE TAG.article_ref = bda_articles.article_id)

Upvotes: 1

Related Questions