CODEWITHSUNDEEP
mysql
trackmeifUcan
trackmeifUcan

Reputation: 103

two queries giving different results

why do I get null for the first query but not the 2nd ? I know the file has that value, I also included the explain result hopefully it helps. Also helps if one can suggest how can I do this in a better way than using case and when to combine multiple values into a 1, here am starting with only city but i plan to combine city,town,village into woohoo

thanks

        select filename,(case when road like '%city%' then 'woohoo' end) as x
        from files,metadata where files.id = metadata.id and
        filename like '%ABC%'
        group by filename ;




        select filename,(case when road like '%city%' then 'woohoo' end) as x
        from files,metadata where 
        filename like '%ABC%'
        group by filename ;



        1   SIMPLE  files   index   PRIMARY filename_idx    767     313728  Using where; Using index
        1   SIMPLE  metadata    ref MetaData$id_FK  etaData$id_FK   4   schema.files.id 1   

        1   SIMPLE  metadata    ALL                 315423  Using temporary; Using filesort
        1   SIMPLE  files   index       filename_idx    767     313728  Using where; Using index; Using join buffer

Upvotes: 0

Views: 69

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

you have a filter that is probably returning no results, files.id = visionmetadata.id

If you used non-deprecated join syntax, you would see it easier. your query now:

  select filename,(case when road like '%city%' then 'woohoo' end) as x
    from files inner join metadata on files.id = metadata.id 
    where
    filename like '%ABC%'
    group by filename ;

Upvotes: 1

Related Questions