select 2 related tables - 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

General SQL Server Forums

New to SQL Server Programming

select 2 related tables

Author

Topic

padigan
Starting Member

3 Posts

Posted - 2006-05-25 : 10:19:52

hi this is probably a dumb question with an easy answer but does anyone know how to do this...

2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)

basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?

do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-25 : 10:22:08

select columnyouwant, othercolumnyouwant
from supplier s
inner join order o
on s.id = o.supplier_id

-------
Moo. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 10:25:24

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

padigan
Starting Member

3 Posts

Posted - 2006-05-25 : 10:29:57

what if... select * from orders inner join suppliers on suppliers.id=orders.supplier_id

would that work? cos i want to select all columns except the suppliers.id and orders.supplier_id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 10:32:12

* will select all columns from two tables

Madhivanan

Failing to plan is Planning to fail

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-25 : 10:34:49

Just select the column names that you want displayed. Using SELECT * is bad practice and lazy.

-------
Moo. :)

padigan
Starting Member

3 Posts

Posted - 2006-05-25 : 10:36:32

maybe... but is there a way of excluding the columns I dont want?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 10:43:25

What is the difficulty in typing the required columns?
There is no easy way other than dynamic sql

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-05-25 : 12:14:35

padigan,

" maybe... but is there a way of excluding the columns I dont want?"

Yes. Just don't type those column names in your SELECT.

Ken

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-25 : 20:42:02

quote:
Originally posted by padigan

maybe... but is there a way of excluding the columns I dont want?

use Query Analyzer and drag the columns from the object browser pane.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-25 : 23:20:41

i think you have lots of column and only few to exlude...


declare @cols varchar(4000)

select @cols=coalesce(@cols + ',','') + column_name from information_schema.columns
where table_name='tablename' and column_name not in ('col1','col2'...)

print @cols

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources