not-exists

Mysql select where not in table

I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works: select * from A where not exists (select * from B where A.pk=B.pk); however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B) Is there a better way to run this? Perhaps as a left join? ...

SQL - improve NOT EXISTS query performance

Hi, Is there a way I can improve this kind of SQL query performance: INSERT INTO ... WHERE NOT EXISTS(Validation...) The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTS clause if very slow. I have to do this verification because I can't insert duplicated data. I use SQLServ...

MySQL Join Where Not Exists

I have a MySQL query that joins two tables Voters Households they join on voters.household_id and household.id Now what i need to do is to modify it where the voter table is joined to a third table called elimination, along voter.id and elimination.voter_id, how ever the catch is that i want to exclude any records in the voter table ...

SELECT item types not provided by an entity

So I've got some data. There are entities. Entities have an arbitrary number of items. Items can be one of a defined set of types. An entity can have more than one item of a given type. I can get a list of items that an entity has. What I want is to get a list of types that an entity doesn't have an item for. Here's my schema: entitie...

Using a table variable inside of a exists statement

I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table: DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50), DevLastName VARCHAR(50), BugDate VARCHAR(20), IsValid VARCHAR(1)) UPDATE @BugRep SET IsValid =...

Getting record differences between 2 nearly identical tables

I have a process that consolidates 40+ identically structured databases down to one consolidated database, the only difference being that the consolidated database adds a project_id field to each table. In order to be as efficient as possible, I'm try to only copy/update a record from the source databases to the consolidated database ...

retrieve available day from date rante

I think I'm pretty close on this query, but can't seem to crack it, and I'm not sure if I've got the most efficient approach. I am trying to find a day where a user is not booked from a range of dates where they are booked. Think staff scheduling. I need to find who is available to work on Tuesday, and is working on other days this w...

CAML Query find records where parameter does not exist?

I have a sharepoint list which has several fields. It seems that when a field is left blank on one of the records - that attribute is missing on the field when I query the list using a CAML Query. Is it possible to write a query to return the records which do not contain this attribue? Example: id Employee Title description --------...

PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL? ...

get random code not exists mysql

I have this sql select concat( char(FLOOR(97+ RAND()*26)) , char(FLOOR(97+ RAND()*26)) , FLOOR(100+ RAND()*999) , char(FLOOR(97+ RAND()*26))) AS randomcode WHERE NOT EXISTS ( SELECT * FROM table WHERE code_felt = randomcode ); But I can't get it to work. D...

mysql insert if row does not exist already in a table with NO UNIQUE FIELDS

Looking for a while now already for how to accomplish this. Seems that all solutions need unique fields with indexes. ...

Array .push() if does not exist?

How can i push up into an array if neither values exist? Here is my json array/object: [ { name: "tom", text: "tasty" }, { name: "tom", text: "tasty" }, { name: "tom", text: "tasty" }, { name: "tom", text: "tasty" }, { name: "tom", text: "tasty" } ] If i tried to push again into the array with either name: "tom" or...

Insert record into table if entry does not exist in another table- with an extra twist

Hi to all you mighty SQLsuperheros out there.. Can anyone rescue me from imminent disaster and ruin? I'm working with Microsoft Access SQL. I'd like to select records in one table (table1) that don't appear in another (table2) .. and then insert new records into table2 that are based on records in table1, as follows: [table1] file_inde...

COUNT() Function in conjunction with NOT IN clause not working properly with varchar field (T-SQL)

Hi all. I came across a weird situation when trying to count the number of rows that DO NOT have varchar values specified by a select statement. Ok, that sounds confusing even to me, so let me give you an example: Let's say I have a field "MyField" in "SomeTable" and I want to count in how many rows MyField values do not belong to a dom...

How do I correctly use two Not Exists statements in a where clause using Access SQL VBA?

I have 3 Tables: NotHeard,analyzed,analyzed2. In each of these tables I have two columns named UnitID and Address. What I'm trying to do right now is to select all of the records for the columns UnitID and Address from NotHeard that don't appear in either analyzed or analyzed2. The SQL statement I created was as follows: SELECT UnitID,...

Mysql: Perform of NOT EXISTS. Is it possible to improve permofance?

I have two tables posts and comments. Table comments have post_id attribute. I need to get all posts with type "open", for which there are no comments with type "good" and created date MAY 1. Is it optimal to use such SQL-query: SELECT posts.* FROM posts WHERE NOT EXISTS ( SELECT comments.id FROM comments WHERE comments.post_id =...

SQL: not exist with openquery not working as expected

Hi, I do have an oracle 8 database from which I want to fetch data to SQL Server 2005. The following statement works fine, if the table on SQL Server 2005 is empty. If I run with let's say one entry missing, it does not work. Please let me know, if any additional information would be usefull! SELECT wdmsoracle.NO FROM (Select * from OP...

PHP copy says file does not exist when it does

Hi, im using the following code; if( ! file_exists( $path ) ) { die( "'" . $path . "' not vaild path"); } copy( $path, ltrim($create_folder . ltrim($path, "./"), ".") ); echo "'" . $path. "' => '" . $create_folder . ltrim($path, "./") . "'<br />"; The first if statement returns true yet the copy function returns; Warning: copy('./fil...

Magento: addAttributeToFilter but ignore for products that don't have this attribute?

I'm trying to show add some filters on my store, but they have a nasty side effect. Suppose I have product type A and B. Now I want to only show A where color = blue/red. $collection = Mage::getResourceModel('catalog/product_collection') ->setStoreId($this->getStoreId()) ->addCategoryFilter($this) ->addAttributeToFilter(ar...

RabbitMQ Message Queue Exists or Not

How Can i check whether a message Queue already exists or not.. Reason for this is i have 2 different applications one creating a queue and other reading from that queue..So if i run the Client which reads from the queue first than it crashes. So to avoid that i would like to check first whether the queue exists or not ...