Home > sql > SQL Query Help - Selecting Only Non-Existent Rows

SQL Query Help - Selecting Only Non-Existent Rows

March 10Hits:1
Advertisement

I'm aware this has been asked previously, but I just can't get it to work the way I need.

I have two identical tables in two different schemas. Let's call them table1 and table2. table1 is the master, it contains all entries... table2 should have duplicate data, but is missing some rows. I need to be able to select from table1 only those rows which are missing from table2, I've tried the following

select order, item from table1 where status = 133 and not exists  (select order, item from table2 where status = 133) 

But it always returns no results. I'm guessing I've just got the syntax wrong but can't think of the correct method

Any help?

Answers

you are not doing it right, here's how you should do it:

select order, item from table1 where status = 133 and not exists
(select order, item from table2 where status = 133 and order = table1.order
and item=table1.item)

Or, another option (not tested):

(select order, item from table1 where status = 133)
except
(select order, item from table2 where status = 133)

Tags:sql server, sql

Related Articles

  • SQL Query Help - Selecting Only Non-Existent RowsMarch 10

    I'm aware this has been asked previously, but I just can't get it to work the way I need. I have two identical tables in two different schemas. Let's call them table1 and table2. table1 is the master, it contains all entries... table2 should have dup

  • SQL Query to select post title & post ID from a particular categoryJune 14

    I am looking for SQL query to select post title & post ID from a particular category. I looked at wordpress table structure but it is really confusing me. --------------Solutions------------- You can use $args = array( 'post_type' => 'post' ,'posts

  • Single sql query to select a record from parent table only if all it's records in child table has valuesNovember 3

    I've Table1 and Table2, Table1's PK is the FK of Table2. Now i need to perform a select from Table1 if and only if all the records in Table2 corresponding to a Table1 record has values (if any field value is null then skip). ie, for an id in Table1 t

  • SQL query within Select tool in ArcGIS model going very slow

    SQL query within Select tool in ArcGIS model going very slowMarch 12

    I have been working on finding the shortest routes from a series of residential blocks (a shapefile with polygon centroids) to a a number of recreational areas. I have a total of 9 resulting OD cost matrices (Playing fields within 1000m, Playing fiel

  • SQL query to select most recent versionAugust 28

    I have the following table definition: CREATE TABLE ProductPointValues ( id INT, effective DATE, minPoints INT, maxPoints INT, CONSTRAINT PK PRIMARY KEY (id, effective), CONSTRAINT FK FOREIGN KEY (id) REFERENCES Products(id) ) ; INSERT INTO ProductPo

  • SQL query to select posts from multiple categoriesOctober 11

    I have built a query to select posts within a category. Works fine. But when I choose to add a secondary filter to exclude a category, the query return the same result set, as if the secondary category filter is being ignored. In the following the qu

  • my Sql query to select 2 records per each categoryJanuary 21

    I have 2 tables. 1. news table 2. category table. in news table i have id, title, category id in category table i have id and category name. in news table there are many news items where multiple news items for each category. what i need was to selec

  • SQL query to select dates between two datesFebruary 26

    I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date between 2011/02/25 and 2011/02/

  • SQL query that concatenate values from duplicate rows in a single tableJuly 20

    Lets say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with this table of duplicated rows. I will fix it sometime but it's not the issue. What I currently need is something that queries such a ta

  • How to create an Sql table from SELECT with a multiple row DISTINCT constraint?August 5

    I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from another table and they are all unique. Here's my t

  • How to write an SQL query to select recently edited features with Editor Tracking enabled?November 4

    Conditions: ArcGIS for Desktop 10.2 and ArcSDE 10.1 Is there a way to select recently edited features using the automatically populated fields that Editor Tracking uses? The new Editor Tracking feature allows geodatabase managers to track changes to

  • ExactTarget - SQL Query Basics (Select column?)January 9

    I'm new to ExactTarget and trying to write a query just to select columns from one table into another to ensure the syntax I'm using is correct and even this basic syntax is not working. Can you please help with how the query should be written? I wan

  • Same SQL Query just working for some of rows

    Same SQL Query just working for some of rows November 25

    I have a table called Items which can be seen from picture. I'm trying to execute some queries on terminal however there is an interesting thing that I really didn't understand. Same simple query (UPDATE Items SET id= X WHERE id = Y) is not working f

  • SQL Query: Selecting a person who is a member of all existing groupsNovember 23

    I'm tinkering around with my very first SQL queries and am completely baffled by a problem I encountered. Let's say I have the following database: PERSON(PID, Name) MEMBER(PID, GroupID) How would I go about creating an SQL query that selects only the

  • Combine two SQL queries or prevent exclusion of data rows in existing queryJanuary 26

    I have a SQL query: SELECT AnalyticsHub_Requests.Id AS reqId, AnalyticsHub_Requests.*, AnalyticsHub_StatusCodes.ShortDescription AS StatusText, Users.FullName + ' (' + COALESCE(WorkPhone,'Phone empty') + ')' AS Value, Users.Id FROM AnalyticsHub_Statu

  • MX Select Plus CE - problems with SQL QueryMay 31

    I've added MX Select Plus CE in a Matrix row. The objective is when the user is digitizing inside the select options, appears "on the fly" the "title" of theses entries of a specific channel. I chose a SQL query: SELECT title FROM exp_

  • Export SQL query based on custom field?March 19

    I'm operating on two WordPress sites and I need to export posts only with a specific custom field from one site and import into the other. I can't do a generic dump of the database because some data would be overridden. My SQL query: SELECT * FROM wp

  • Replacing field result with hook_views_pre_execute when replacing SQL queryFebruary 25

    I have a simple page view (unordered list) with just one field (content: path) and one contextual filter (nid). The view isn't displaying the right path if the language of the selected node is not the current site language. For that reason i've decid

  • how can optimize this sql query for being faster than superman

    how can optimize this sql query for being faster than supermanFebruary 19

    this my sql query: SELECT * FROM (SELECT * FROM ClassifiedAd WHERE city_id = :city AND currency = :currency AND price > 100) UNION DISTINCT (SELECT c.* FROM ClassifiedAd c WHERE c.city_id = :city AND c.currency = :currency AND (c.price > 100 AND c.p

  • What's the quickest and most maintainable way to implement SQL querying of disparate C++ variables?July 3

    I've recently joined a team working on radar observations. The team has access to a repository of software (written in C++) used for dealing with these observations, but their work is focused towards research, not software development. There are vari

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.897 s.