SQL Server Forums - Querying two tables with select statement

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying two tables with select statement

New Topic

Reply to Topic

Printer Friendly

Author

Topic

Prosercunus
Starting Member

USA
22 Posts

Posted - 10/09/2012 : 05:56:15

I have two tables. One called "Prof" and one called "Dept"

The Prof table has the columns "pname" (Professor Name) and "dname" (Department Name)

The Dept table has the columns "dname" (Department Name) and "numphds" (Number of PhD's)

Of course this causes me Ambiguous column name 'dname'. which I understand because it shares the same named column "dname"

How do I properly write the select statement to find names of professors who work in departments that have fewer than 50 PhD students.

I know it is something to do with assignments, but I am confused. Thanks.

senthil_nagore
Aged Yak Warrior

India
997 Posts

Posted - 10/09/2012 : 06:01:07

Specify with table name Prof.dname / Dept.dname

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts

Posted - 10/09/2012 : 06:05:39

select p.pname, d.numphds
from Prof as p
join Dept as d on d.dname = p.dname and d.numphds < 50

Too old to Rock'n'Roll too young to die.

Prosercunus
Starting Member

USA
22 Posts

Posted - 10/09/2012 : 06:08:16

You guys are awesome. Thank you.

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.53 seconds.