conan conan - 4 months ago 7
PHP Question

Trying to get certain values with mysql php?

Look at my table, I am trying to get the fruit from USA with ups shipping and also any fruit that doesn't include USA. Look at my table, apple include USA and ups, berry doesn't include USA. So I want to get apple and berry. The below query is the best I got, but it fail, it doesn't echo berry which doesn't include usa.

SELECT fruit FROM table fruit WHERE shipping = 'ups' AND country ='USA' GROUP BY fruit


table fruit
fruit country shipping
apple USA ups
apple China ups
apple UK ups
banana USA fedez
banana Russia fedez
banana Italy fedez
berry Italy fedez
berry China fedez
berry French fedez

Answer
SELECT fruit FROM fruit WHERE (country = "USA" AND shipping = "ups") OR fruit NOT IN (SELECT fruit FROM fruit WHERE country = "USA") GROUP BY fruit

SELECT fruit
FROM fruit
WHERE
    (country = "USA" AND shipping = "ups")
    OR
    fruit NOT IN (
        SELECT fruit FROM fruit WHERE country = "USA"
    )
GROUP BY fruit

So if I'm right, the first statement will return you apple. The second one has a nested query which will return apple and banana because both have country USA , and the statement will find the fruit which is not in this group , so it is berry. So in the end it will only return apple and berry

If it echoes everything I will try it on my computer.

Comments