CODEWITHSUNDEEP
mysqlsqljoindistinct
user3302908
user3302908

Reputation: 13

MySQL DISTINCT function not working as I expected

Note: I am pretty new to MySQL so bear with me please.

I have 2 tables in my database which are set up as follows:

guides table:
guide_id (primary) | cat_id | title
   1                   0     guide01
   2                   0     guide02

steps table:
step_id | guide_id (foreign) | step_txt
   1              1            step1 text...
   2              1            step2 text...

And I am trying to search the database for keywords within steps.step_txt and return a list of guides. My current query looks like:

SELECT DISTINCT * 
  FROM guides 
    JOIN steps ON guides.guide_id=steps.guide_id 
  WHERE step_txt LIKE "%keyword%"

What I have found is that as some guides have more than one step with the keyword contained, this returns duplicated rows. I would like the query to output 1 row containing guide_id, cat_id and title even if it finds 2.

I think the problem is that I have used JOIN so the query is actually returning a joined row from both tables which would have different step_id and step_txt so the DISTINCT isn't effecting it. What is the best work-around for this?

Upvotes: 1

Views: 361

Answers (4)

Lovy
Lovy

Reputation: 1

You can also use IN operator

select guide_id from guides where guide_id IN (select guide_id from steps where step_txt LIKE "%keyword%")

Upvotes: 0

Aymanadou
Aymanadou

Reputation: 1220

simple solution is:

SELECT DISTINCT guides.*
FROM guides, steps
WHERE step_txt LIKE "%keyword%" 
AND guides.guide_id=steps.guide_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271201

distinct * is going to return rows where all the columns are distinct. For what you want, you can use:

SELECT *
FROM guides JOIN
     steps
     ON guides.guide_id = steps.guide_id 
WHERE step_txt LIKE "%keyword%"
GROUP BY guides.guide_id;

This uses a MySQL extension to group by and it will not work in other databases. The columns returned from steps comes from arbitrary matching rows.

Upvotes: 0

Kickstart
Kickstart

Reputation: 21533

Crude solution would be:-

SELECT DISTINCT guides.* 
  FROM guides 
    JOIN steps ON guides.guide_id=steps.guide_id 
  WHERE step_txt LIKE "%keyword%"

Possibly more elegant and giving you the matched text :-

SELECT g.guide_id, g.cat_id, g.title, GROUP_CONCAT(s.step_txt)
FROM guides g
INNER JOIN steps s
ON g.guide_id = s.guide_id 
WHERE step_txt LIKE "%keyword%"
GROUP BY g.guide_id, g.cat_id, g.title

Upvotes: 1

Related Questions