CODEWITHSUNDEEP
mysqlsql
Happy Coder
Happy Coder

Reputation: 4682

mysql query repeats results

I have this query. I need to select the rows if logo is empty OR button is empty OR vanitylogo is empty. This seems to be working but, printing multiple rows for same mid. How to fix this ?

SELECT fmm.mid,
       fmm.name ,
       fmn.mnname,
       fmm.button,
       fmm.logo,
       fmm.vanitylogo
FROM X fmm ,
     Y fmn
WHERE fmm.`button` = ''
  OR fmm.`button` = 'NULL'
  OR fmm.`button` = 'None'
  OR fmm.`logo` = ''
  OR fmm.`logo` = 'NULL'
  OR fmm.`logo` = 'None'
  OR fmm.`vanitylogo` = ''
  OR fmm.`vanitylogo` = 'NULL'
  OR fmm.`vanitylogo` IS NULL
  AND fmm.mid=fmn.nid
  AND fmm.status='active'
  AND fmm.xyz_status='active'

Upvotes: 0

Views: 64

Answers (2)

Bohemian
Bohemian

Reputation: 424983

You have got two major problems with your query:

  1. You have not correctly bracketed your OR conditions
  2. You have no join condition between the tables, leading to a "cross join"

Try this:

SELECT fmm.mid,
       fmm.name ,
       fmn.mnname,
       fmm.button,
       fmm.logo,
       fmm.vanitylogo
FROM X fmm
JOIN Y fmn ON fmn.some_column = X.some_column -- FIX THIS
WHERE (fmm.`button` in ('', 'NULL', 'None')
  OR fmm.`logo` IN ('', 'NULL', 'None')
  OR fmm.`vanitylogo` IN ('', 'NULL')
  OR fmm.`vanitylogo` IS NULL)
  AND fmm.mid=fmn.nid
  AND fmm.status='active'
  AND fmm.xyz_status='active'

You must fill in the "FIX THIS": line with the appropriate column names

Regarding the ORs, I bundled some up into IN() condition, and bracketed up the lot.

Upvotes: 3

jle
jle

Reputation: 9479

You are selecting the table 'Y fmn'. For every row in Y, the results will be repeated. You need a way to aggregate the results, possibly with a group by.

SELECT fmm.mid,
       fmm.name ,
       fmn.mnname,
       fmm.button,
       fmm.logo,
       fmm.vanitylogo
FROM X fmm ,
     Y fmn
WHERE fmm.`button` = ''
  OR fmm.`button` = 'NULL'
  OR fmm.`button` = 'None'
  OR fmm.`logo` = ''
  OR fmm.`logo` = 'NULL'
  OR fmm.`logo` = 'None'
  OR fmm.`vanitylogo` = ''
  OR fmm.`vanitylogo` = 'NULL'
  OR fmm.`vanitylogo` IS NULL
  AND fmm.mid=fmn.nid
  AND fmm.status='active'
  AND fmm.xyz_status='active'
group by fmm.mid,
       fmm.name ,
       fmn.mnname,
       fmm.button,
       fmm.logo,
       fmm.vanitylogo

Upvotes: 0

Related Questions