I'm newer DB Learner and i have one specific question. I Have to search results on my database and only bring the data who have specific codes, and i made the following query:

SELECT * 
FROM MYDB 
WHERE C6_PRODUCT = 'OP78987' 
    AND C6_NOTE = '' 
    AND D_E_L_E_T_ = '' 
    AND C6_CF LIKE 'Q06%' 
    OR C6_CF LIKE 'a06%' 
    OR C6_CF LIKE 'a08%' 
    OR C6_CF LIKE 'Q08%' 
    OR C6_CF LIKE 'd03%'

The C6_CF have a lot of codes, but i only need a few of them (5106,6106,6108,5108,6403,5403,5933,6933,6912,5912,5908,6908)

When i run my query, with the Like, he brings the result only if i have 2 Likes, more of this, he bring all the database again.

There is a best way to do this? Any tips that i can search about ? (I made a search but i dont find anything by now, but im still searching!)

CodePudding user response：

If you can't change your database tables, you would have to use your code :

SELECT * 
FROM MYDB 
WHERE
    C6_PRODUCT = 'OP78987' 
    AND C6_NOTE = '' 
    AND D_E_L_E_T_ = '' 
    AND
    (
        C6_CF LIKE 'Q06%' 
        OR C6_CF LIKE 'a06%' 
        OR C6_CF LIKE 'a08%' 
        OR C6_CF LIKE 'Q08%' 
        OR C6_CF LIKE 'd03%'
    )

But it is generally a bad idea to look after a part of varchar in a database. If you can change your DB I would advice you to create a new table :

CREATE TABLE yourTable (
    C6_PRODUCT VARCHAR(255), -- Put the right type
    C6_CF INT
)
GO
-- Create a FK between your tables for C6_PRODUCT
-- Create an index with C6_PRODUCT and C6_CF

Populate your table like so :

Then you could have a powerfull query like :

SELECT DISTINCT T1.C6_PRODUCT
FROM MYDB T1
INNER JOIN yourTable T2 ON T2.C6_PRODUCT = T1.C6_PRODUCT
WHERE T2.C6_CF IN (6106,6108,5108,6403)
    AND T1.C6_PRODUCT = 'OP78987' 
    AND T1.C6_NOTE = '' 
    AND T1.D_E_L_E_T_ = '';