Why is my SQL query pulling other data?

Question

0.00/5 (No votes)

See more:

I have a SQL Query that compares one database table to another. When I run the query it gives me the data but it also gives me data that I know that is in the database. What I am trying to do is to take table 1 and compare it to Table 2. I want to display the data that Table 2 doesn't have that Table 1 has.

Here is my Query:

SQL

SELECT User_ID, Name FROM Table1

EXCEPT 

SELECT User_ID, NAME FROM Table2 where YEAR = 2016

GROUP BY

    User_ID, Name

Did I miss something?

What I have tried:

I have tried to switch the query around.

Posted 6-Feb-17 9:08am

Computer Wiz99

Updated 14-Feb-17 0:48am

Add a Solution

3 solutions

Solution 2

You might be missing something, it works as expected.

SQL

declare @t1 table (
 col1 varchar(10)
)

declare @t2 table (
 col1 varchar(10),
 id int)

 insert into @t1 (col1) values ('a')
 insert into @t1 (col1) values ('b')
 insert into @t1 (col1) values ('c')

  insert into @t2 (col1,id) values ('a',0)
 insert into @t2 (col1,id) values ('b',1)
 insert into @t2 (col1,id) values ('c',0)

 select col1 from @t1 except
 select col1 from @t2 where id = 1
 group by col1

it returns 'a' and 'c'

Posted 6-Feb-17 19:30pm

Karthik_Mahalingam

Solution 3

(SELECT User_ID, Name FROM Table1)

MINUS

(SELECT User_ID,Name from Table2 where Year =2016)

Posted 14-Feb-17 0:48am

Member 12999924

Comments

Computer Wiz99 14-Feb-17 9:15am

Member 12999924, I got a syntax error when running your code.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MINUS'.

AnvilRanger 14-Feb-17 13:29pm

MINUS is the Oracle equivalent of EXCEPT

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

barneyman · Accepted Answer · 2017-02-06T10:13:00

Solution 1

select * from table1 where user_id not in (select user_id from table2 where year='2016')

i don't *think* EXCEPT works over different tables

Posted 6-Feb-17 10:13am

barneyman

Comments

Computer Wiz99 14-Feb-17 9:18am

Thanks for the help. I do have a question. When I use EXCEPT in other queries it works for different tables. Why do you think it started now?

Richard Deeming 14-Feb-17 9:39am

There is no such restriction - EXCEPT works across different tables, different databases, and even different servers. The only restriction is that both inputs must have the same number of columns, with compatible data types.

EXCEPT and INTERSECT (Transact-SQL)[^]

Computer Wiz99 14-Feb-17 10:18am

So you are saying that Table1 and Table2 must have the same number of columns in order for EXCEPT to work correctly? Even if I SELECT those columns?

Richard Deeming 14-Feb-17 10:21am

No - the input queries must have the same number of columns.

It doesn't matter how many columns the source table has. It doesn't matter if your joining multiple tables. It doesn't matter if you've got calculated columns in the query.

So long as the two queries have the same number of columns, and the data types are compatible, EXCEPT will work.