Reputation: 1102
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
Reputation: 48207
use variables to select the first one of each article_id
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
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