1

I'm looking for a way to join relational tables on a PK similar to MySQL "using" so that I can eliminate duplicate columns.

Here is an example:

select * 
from MovieHasDirector
join Director on MovieHasDirector.directorID = Director.directorID
where Director.name
like '%Steven Spiel%'

Returns:

3818    976 976 Steven Spielberg
3962    976 976 Steven Spielberg
4317    976 976 Steven Spielberg
4715    976 976 Steven Spielberg

When what I really want is:

3818    976 Steven Spielberg
3962    976 Steven Spielberg
4317    976 Steven Spielberg
4715    976 Steven Spielberg

You can see the duplicate directorID column is gone. In MySQL you can do this with the using(directorID) instead of the ON directorID = directorID

Of course I am trying to do this without having to manually specify the Select MovieHasDirector.movieID, Director.* I want the returned records to overlap the columns that are the same. How can I do this?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Kairan
  • 5,342
  • 27
  • 65
  • 104
  • 1
    So MySQL makes it easier to use `select *` in production code, while SQL Server makes it harder, [which is a good thing](http://stackoverflow.com/a/3180435/11683). – GSerg Oct 13 '13 at 22:11
  • I had [the same problem](http://stackoverflow.com/questions/32481055/alternatives-to-using-in-a-join-for-sql-server-for-avoiding-duplicated-columns-i). You might want to take a look to [this solution](http://stackoverflow.com/a/32482404/2436175). – Antonio Sep 10 '15 at 07:33

2 Answers2

3

You cannot do that in SQL Server, you have to specify column names that you want to return:

select MovieHasDirector.column1, MovieHasDirector.column2, Director.column1
from MovieHasDirector
join Director on MovieHasDirector.directorID = Director.directorID
where Director.name
like '%Steven Spiel%'

You can use table aliases to make it a bit easier:

select M.column1, M.column2, D.column1
from MovieHasDirector M
join Director D on M.directorID = D.directorID
where D.name
like '%Steven Spiel%'
Szymon
  • 42,577
  • 16
  • 96
  • 114
1

I think the best way will be to manually select the columns you need.

select a.ID, a.directorID, b.name from MovieHasDirector a, Director b
where a.directorID = b.directorID
demo.b
  • 3,299
  • 2
  • 29
  • 29