How to get all fields except one with inner join?

Question

I am using MS Sql server and I have 2 tables as below;

School(scid, name, address, ....)
Student(scid, studentName, studentSurname, ....)

What I want to query is;

SELECT * FROM Student ST INNER JOIN School SC ON ST.scid = SC.scid;

But I don't want to get double scid field as result(It returns scid and scid_1). I mean

SELECT *(except School.scid) FROM Student ST INNER JOIN School SC ON ST.scid = SC.scid;

Is there a way to get all fields except one?

When you're using all fields from 2 or more tables, it might make sense to create a View and explicitly define all the columns you'll need. Then you need to perform a simple select in your application i.e. `Select * from StudentFullView` — navigator, Nov 01 '16 at 08:02

score 4 · Answer 1 · answered Nov 01 '16 at 07:57

Unfortunately the only way to do this is explicitly name all the fields. The only shortcut you could take is use the * for the individual tables:

SELECT ST.*, School.Field1, School.Field2, etc 
    FROM Student ST INNER JOIN School SC ON ST.scid = SC.scid;

If you have this alot, you could consider using Sql Prompt from RedGate, they have some features that make it easier to work with Sql Databases.

1 Answers1