CODEWITHSUNDEEP
mysqlselection
Wickd
Wickd

Reputation: 156

Select data from first table and compare it with second one

I have a problem in here, I can't understand how this thing works. So here's the deal:

$q= mysql_query("SELECT * FROM fruits_list, fruits_have WHERE fruits_list.fruit != fruit_have.fruit AND fruit_list.color != fruit_have.color");

Tables:

fruits_list
- fruit --- color   -
-   1    -     2    -
-   2    -     3    -
-   1    -     3    -
-   1    -     4    -
---------------------

fruits_have
- fruit --- color -
-   1   ---   2   -
-   1   ---   3   -
-------------------

So now this query will remove everything from "fruits_list" that have "1" in "fruit" and "2", "3" in "color".

But I want remove only lanes where both columns are equals. In this example, only 2 lanes should be removed from "fruits_list" and outcome should be like that:

fruits_dont_have
- fruit --- color -
-  2    -    3    -
-  1    -    4    -
-------------------

So the question is, what should I change in my query? I hope I did myself clear enough for you to understand.

EDIT:

Ryan E - Simply remove from outcome.

bonCodigo - both are int.

Upvotes: 1

Views: 229

Answers (2)

Yogendra Singh
Yogendra Singh

Reputation: 34367

Try concatenating the two columns withe a separator and then compare as below:

     SELECT * FROM fruits_list, fruits_have 
     WHERE CONCAT_WS('@',fruits_list.fruit, fruits_list.color) !=  
               CONCAT_WS('@',fruits_have.fruit, fruits_have.color)

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60498

Ideally you would use the SQL MINUS set operator, but MySQL doesn't support this yet.

You should be able to do this via a left join with a WHERE to filter out the matches though instead:

SELECT fl.*
FROM fruits_list fl
LEFT JOIN fruits_have fh 
   ON fl.fruit = fh.fruit AND fl.color = fh.color
WHERE fh.fruit IS NULL

Upvotes: 1

Related Questions