CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Michi
Michi

Reputation: 5471

Getting conditions for WHERE clause from a table of criterias

I created the following simple DataModel:

enter image description here

And I filled the tables with the following Data:

1) Table Customer

INSERT INTO test.customer
(CustomerName, Country, RegistrationDate)
VALUES 
("Customer A","DE","2015-05-03"),
("Customer B","US","2015-07-25"), 
("Customer C","US","2016-02-15"), 
("Customer D","DE","2017-09-21"), 
("Customer E","AU","2018-12-07");

2) Table Orders

INSERT INTO test.orders
(idCustomer, PaymentMethod, OrderDate, OrderValue)
VALUES 
("1","CreditCard","2015-05-04","500"),
("1","PayPal","2015-11-18","200"), 
("3","PayPal","2017-09-04","300"), 
("2","Invoice","2018-04-30","100");

3) Table Criterias

INSERT INTO test.criterias
(Country, MinimumOrderValue)
VALUES 
("DE","300"),
("US","150"), 
("AU","200");

Afterwards, I created a query to get Customers and their Orders based on the criterias Country and OrderValue:

SELECT 
test.customer.idCustomer, CustomerName, Country,
test.orders.OrderValue

FROM test.customer
LEFT JOIN test.customer_info ON test.customer.idCustomer = test.customer_info.idCustomer
LEFT JOIN test.orders ON test.customer.idCustomer = test.orders.idCustomer

WHERE 
Country = "US" AND OrderValue >= "150"
OR Country = "DE" AND OrderValue >= "300" 
OR country = "AU" AND OrderValue >= "200";

All this works fine so far.

However, instead of having the criterias in the WHERE clause within the SQL query they should be stored in a seperate table called Criterias. The query should then get the datas from this table and apply them exactly like it does now in the query above.

What do I have to change in my code to achieve this?

Upvotes: 2

Views: 68

Answers (3)

Chandra Shekhar Paatni
Chandra Shekhar Paatni

Reputation: 91

As per the above database schema, You have to make one field customer table which is primary and it will map with the table name order. After this you need to use Join and exists clause in your query.

select c.cutomerId, c.customerName, c.country, o.orderValue from customer c, order o where  c.customerId = o.customerId and 
        exists (select * from criteria cr where cr.country = c.country and o.order >= cr.MinimumOrderValue)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270411

You would just join the tables or use a subquery in the where clause.

The latter is easier to type, so:

WHERE EXISTS (SELECT 1
              FROM Criterias cr
              WHERE cr.Country = c.Country AND
                    cr.MinOrderValue <=  o.OrderValue
             )

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Not necessarily an answer; too long for a comment...

A valid set of query conditions might look like this...

 WHERE (country = "US" AND OrderValue >= 150)
    OR (country = "DE" AND OrderValue >= 300)
    OR (country = "AU" AND OrderValue >= 200);

...or indeed (though potentially slower)...

WHERE (country,ordervalue) IN(('US',150),('DE',300),('AU',200));

Upvotes: 1

Related Questions