Which COLUMN qualified my Query? 2

[JSProgramIA]

I am trying to figure out which column the query was qualified on.

For instance:

SELECT * 
  FROM products 
  WHERE product_id LIKE '%somethingHere%' 
        OR product_description LIKE '%somethingHere%'

And what I want to know is, which column qualified the query. Was it product_id, or product_description, or both? I am trying to use this in a search results page, and I would like to tell the user which column their keyword found a match on.

Is it possible to return a user defined column, such as matched_in and have a value in that column describe which column we made a match on?

Thanks for any help you may offer.

 

[TonyGroves]

How about:
[tt]
SELECT
*,
product_id LIKE '%somethingHere%' idmatch,
product_description LIKE '%somethingHere%' descmatch
FROM products
WHERE
product_id LIKE '%somethingHere%'
OR product_description LIKE '%somethingHere%'
[/tt]

 

[JSProgramIA]

This appears to be returning a boolean value? Is that correct?

Anyways, this will work just fine. Could you explain the following code a bit more, please?

SELECT
  *,
  product_id LIKE '%somethingHere%' idmatch

Am I reading this right, and you can put conditional statements in the SELECT portion of the query????

Just when I thought I was getting this SQL thing...

 

[TonyGroves]

product_id LIKE '%somethingHere%'" is an expression which evaluates to a boolean value of 0 or 1, regardless of where it's used.

 

[r937]

regardless of where it's used" could perhaps be qualified -- this may be the case in mysql, but it would fall down in any other database

better to use standard sql, it's not really that much more work to write, it works just as well in mysql, and it is certainly a lot easier to understand

plus, you can assign your own true/false values (not all systems are consistent, sometimes 0=false, sometimes -1=true)

select *
     , case when product_id 
            like '%somethingHere%' 
            then 'Y' else ' ' 
         end   as idmatch
     , case when product_description 
            like '%somethingHere%' 
            then 'Y' else ' ' 
         end   as descmatch
  from ...

r937.com | rudy.ca

 

[JSProgramIA]

Terrific Tip! Thanks so much.