SQL Query related conditions-CodePudding

I want to make a SQL conditions where if the

type=1 and item=1 then the product column must be product=4 else SQL should not select the row but If item=2 or any number then SQL will select the row. The product column does not matter what value it is.

But if type=0 or any number then item and product column does not matter what value it is. The SQL will select the row

In my SQL Query it only takes row that have type=1, item=1 and product=4. Here are an example table

Type	Item	Product
1	2	4
1	2	3
1	1	5
1	1	4
1	1	4
0	2	4
0	1	5
0	0	0

Please take a look at my SQL Query

$tables = Table::whereRaw("CASE WHEN (table.type = 1 AND table.item = 1) THEN table.product = 4 END");

The resulting output that I wanted should be like this

Type	Item	Product
1	2	4
1	2	3
1	1	4
1	1	4
0	2	4
0	1	5
0	0	0

CodePudding user response：

Programming is largely about translating the messy, ambiguous, and flexible wording of human language descriptions into something structured and logical that can be understood by a machine. In effect, you have to think like a computer, rather than converting sentences word by word.

In this case, you need to put all your requirements into the form of boolean statements of the same form: "if row satisfies condition X then include it". Then you can combine them: "if row satisfies condition X OR row satisfies condition Y, then include it". Note that we can assume that any row not mentioned is excluded, without saying so explicitly, because that's how an SQL "where" clause works.

So...

type=1 and item=1 then the product column must be product=4 else SQL should not select the row

Becomes

If type equals 1 and item equals 1 and product equals 4, then include the row

item=2 or any number then SQL will select the row.

Becomes (assuming "any number" means "any number other than 1" - if that description was given to me as a requirement at work, I would reject the task as unclear)

If item doesn't equal 1, then include the row

Similarly

But if type=0 or any number then item and product column does not matter what value it is. The SQL will select the row

Presumably means

If type didn't equal 1, then include the row

Now we have three concise statements that are easy to convert to SQL, and combine:

( type=1 and item=1 and product=4 )
or
( item != 1 )
or
( type != 1 )

(There are of course other ways to write the same thing, but the key is always to convert the requirements into some consistent form, rather than dealing with all the messy "but"s and "also"s of an English sentence.)

CodePudding user response：

Thanks @IMSoP, my code works by using something like this

->where(function($query){
        $query->whereRaw("
            CASE 
                WHEN (table.type = 1 AND table.item = 1) THEN table.product = 1 
                WHEN (table.type = 1 AND table.item != 1) THEN table.id
                WHEN (table.type != 1) THEN table.id
            END
        ");
    })