exclude rows

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:

table 1
----
id
----
a
b
c
d
e
f
g

table 2
----
id / id2
----
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4

need results
----
id
----
a
e
g

any suggestions?

thanksSELECT * FROM tblOne WHERE id_field IN (SELECT id_field FROM tblTwo WHERE other_id = 1)

Exclude columns from a select statement

Hi,
Simple question:
Is there any way to exclude some columns with a SELECT Table1.* statement?
Thanks for any infosnope.
You can name the columns that you wish to return or create a dynamic sql statement with the columns in.

It is considered bad practise to use select * for returning data.|||For example:

table collums: id - name - adress - phone

If you wish select only name you can use the following query:

"SELECT name FROM table"

To add new collums you must add it and put behind a "," like:

"SELECT name,phone FROM table"

[ ]'s|||Hi,

In fact, I've an HUGE view with many column and i have to select all the fields without the description ones for a translation process. I'm forced to use SELECT Col1, Col2 ,,,,,, Col30... FROM View.

If a such notation exist I just have to write SELECT all appart(Col20,Col22,Col25) FROM View

Thanks anyway for answers

EXCEPT not working

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

The syntax should be valid if you are on SQL Server 2005, any version prior 2005 won′t support the Intersect keyword. I think thats your problem. Seems that you are connected to a SQL Server 2000 instance.

HTH, Jens SUessmeyer.

-
http://www.sqlserver2005.de
-

|||Or the database is running in 8.0 compatibility level.|||The new set operators will work in 80 and other compatibility modes also. So that is not the problem. User is either running on a older version of SQL Server or old CTP releases of SQL Server 2005. The set operators EXCEPT/INTERSECT was added late in the development cycle only.|||Please post the version of SQL Server (@.@.version) that you are running this code against.|||

Thanks for the replies ppl. Here's the requested information:

Information obtained from Help/About (about indicates "MS SQL Server 2005":

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Information obtained from "select @.@.version":

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

|||

>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation

>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The server version is 8.* which means it is SQL Server 2000. You are running SQL Server Management Studio though which ships with SQL Server 2005. The client doesn't have anything to do with the server language features. So you need to create your tables on a SQL Server 2005 server and try the EXCEPT query.

|||Did you happen to install SQL Server 2005 on a machine with an existing SQL Server 2000 installed? You might have mistaken the installation to be an upgrade (just like what I did a couple of months back )|||Well, I have no control over the infrastructure or anything in fact... I am just stepping in with the .NET development. Unfortunately, it does seem as if they are a bit out of date. Yes, on my client machine I am using 2005 but the database is in 2000.

except

i have a table name table1 with composite pks on (A,B,C) And non keyfields (D,E,F)

on table 2 I have also composite pks on (A,B,C)

is it possible to have

select * from table 1 except select a,b,c from table2

i want to compare on pks only

or do i need to

select a, b,c , d,e,f from table1 except select a,b,c from table 2

join

select * from table1 as table3 on

table1.a=table3.a and

table1.b=table3.b and

table1.c=table3.c

thanks

If you want to get the non-key columns also then you can just use NOT EXISTS like:

select a, b, c, d, e, f

from table1 as t1

where not exists(select * from table2 as t2 where t2.a = t1.a and t2.b = t1.b and t2.c = t1.c)

|||thanks