On Jan 26, 2:35 pm, Martin Westin <martin.westin...@gmail.com> wrote:
> Hi,
> I am in need of an unusual query that I find it hard to search for on
> Google or MySQL.com since all the terms I think of are very common
> ones. Hopefully someone reading this can guide me in the right
> direction.
>
> I have a series of values in an array. I can find out which records
> have matching values by passing this array as a condition to get a
> "WHERE somefield IN (value1,value2)" clause.
>
> I can also find which records have non-matching fields by using a
> "WHERE NOT(...)" clause. Finding the "orphans" in the database so to
> speak.
>
> Of-course I want something else. I want to find out which values in my
> array do not match a record in the database. Finding the orphans in my
> array so to speak. I have not figured out how to do this without using
> the IN clause and then comparing the results. Since this is
> "expensive" (looping arrays take time) I really wanted to find an SQL-
> way that would be less expensive.
>
> "SELECT VALUES IN MY ARRAY THAT DO NOT FIND ANY MATCHING RECORD" -
> sort of.
>
> Any guidance would be appreciated
> /Martin
There's probably a more elegant way, This'd work:
DROP TABLE `tmp`;
CREATE TABLE `tmp` (
`id` INT( 11 ) NULL
) ENGINE = MEMORY;
INSERT INTO `tmp` (
`id`
)
VALUES (1),(2),(3),(4),(5); # The values you want to search for
select `tmp`.`id` from `mytable` RIGHT JOIN `tmp` on
`mytable`.`somefield` = `tmp`.`id` WHERE `mytable`.`somefiled` IS
NULL;
Anything returned is a `mytable`.`somefield` that doesn't exist.
AD
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
