Inverse select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inverse select

Author

Topic

leach
Starting Member

4 Posts

Posted - 2005-04-26 : 05:43:48

Hi

Im a newb to SQL, but have what i think is a simple question, yet have been unable to find a solution.

How would i do an inverse selection of the column headings...for example...i have an access database containing 10 headings and to display all of them i can do a simple SELECT * FROM table. But, what if i want to select all the columns, EXCEPT for 1. Surely i dont have to explicitly set all the ones i want to view, like SELECT a,b,c,d,e, FROM table.

There must be a more elegant way...

Hope someone can help

leach.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-26 : 06:34:24

Set up a view that only contains the columns that you want, and select from that. Generally though it is best to select the columns by name.

-------
Moo. :)

leach
Starting Member

4 Posts

Posted - 2005-04-26 : 06:43:27

cheers for the reply. Will look into views.

Im just curious if theres a way to not have to type in all the individual names. what would happen if you had 100's of names!? Maybe im just lazy, but i would have thought with such a query language that something along the lines of "SELECT * <> a FROM table" would have been logical. or am i just being daft!?

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-26 : 07:10:40

I think Oracle might have a function similar to what you are asking..

Really though, if you have 100s of column names you should revisit your design. :)

-------
Moo. :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-26 : 07:11:04

"SELECT * <> a FROM table" would have been logical. or am i just being daft"
tut, tut....don't offer us an out to slag you off "too early" in your SQLTeam.com experience!!!!

Search here for advice on why "SELECT *" is not recommended.

You can call some 'utility code' that some people have posted here (nr?) which will turn a "SELECT *" into a "SELECT a,b,c,d,e,f,g,h, etc"...to save you some of the 'bother'.

leach
Starting Member

4 Posts

Posted - 2005-04-26 : 07:49:58

Thanks for the replies chaps.

Was just wanting to make sure i didnt miss some simple trick, now i can get on with learning how to do things properly :-)

Thanks for taking the time to be nice to a noob! :-)

Leach

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-26 : 11:53:01

We have had some discussions about this in the past ... my take on it is usually this: If this was valid:

SELECT * (except colA) from SomeTable

then what should happen if SomeTable doesn't have a column called ColA ? Should the statement generate an error? or should it work just fine since ColA isn't requested anyway? Quite the philosophical question!

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-26 : 13:45:15

>>what would happen if you had 100's of names!? Maybe im just lazy...

I'm definately lazy so I usually generate my column lists. It's not a complete statement or anything fancy, just something that saves typing and typos:


select char(9) + column_name + ','
from	information_schema.columns 
where	table_name = '<MyTableName>' 
order by ORDINAL_POSITION

As far as the philosophical question Jeff raised, my column list is half empty and no one hears the other half fall in the forest so I say generate the error.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-26 : 16:38:39

This is a little bit more elaborate select column list generator. It lets you set the table alias, it adds the SELECT and FROM, and it leaves out the comma on the last column.

use pubs
go
declare @TableName1 varchar(70)
declare @alias1 varchar(6)

select @TableName1 = ' authors '

select @alias1 = 'a'

declare @TableName varchar(70)
declare @tab varchar(1)
declare @alias varchar(6)
declare @crlf	char(2)

set nocount on

select @alias1 = ltrim(rtrim(@alias1))
select @alias	= @alias1+'.'
select @tab = CHAR(9)
select @crlf	= char(13)+Char(10)

select @TableName = name from sysobjects
where name = ltrim(rtrim(@TableName1)) and type in ('U','V')

set nocount on

--print @crlf+'select'+@crlf

select [--ColumnName] = 
	ltrim(rtrim(convert(varchar(255),
	case
	when a.colid = 1
	then @crlf+'select'+@crlf
	else '' end+
	@tab+'['+a.Name+']'+@tab+@tab+'= '+@alias+'['+a.Name+']')+
	case
	when a.colid = c.max_colid
	then @crlf+'from'+@crlf+@tab+@TableName+' '+@alias1
	else ',' end
	))
from
	syscolumns a
	join
	sysobjects b
	on a.id = b.id
	cross join
	(
	select
		max_colid = max(cc.colid)
	from
		syscolumns cc
		join
		sysobjects dd
		on cc.id = dd.id
	where
		dd.name = @TableName	and
		dd.uid = 1
	) c
where
	b.name = @TableName	and
	b.uid = 1
order by
	colid

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-26 : 18:51:27

I wasn't really thinking about this before, but there is another way:

1. Open Query Analyzer
2. Click the Object Browser button on the toolbar
3. In the Object Browser pane, find your table.
4. Right-click on your table, and select Script Object to New Window as Select.

It opens a new window with a select statment with all the columns listed.

CODO ERGO SUM

leach
Starting Member

4 Posts

Posted - 2005-04-27 : 03:34:29

Hiya guys

Thanks for the additional info. I only started using SQL within my asp pages two days ago...so much of the previous code is a little over my head...but it's excellent learning material. I'll also have to check out this "Query AnalySer" :-) as i've seen this mentioned a few times now.

Cheers

leach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources