sql query help - 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 2005 Forums

Transact-SQL (2005)

sql query help

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-05 : 13:11:47

I have a projects table and a project type table

tblProjects: this table has project no, project name and projecttype fields. Projecttype field is null for some of the projects.

tblProjectType: this table has ProjectTypeName and FriendlyName fields.

Now I want to get the list of all projects from tblProjects along with their friendly name from the tblProjectType table. For the projects that have null in the projecttype should return "null" as their friendly name.

How do I do this in SQL?

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-11-05 : 13:16:18

does tblProject.ProjectType = tblProjectType.ProjectTypeName?

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-05 : 13:16:44

oh, that's right...forgot to mention that. Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-11-05 : 13:17:53

Try:

select p.*, pt.FriendlyName
from tblProjects p
left outer join tblProjectType pt on p.ProjectType = pt.ProjectTypeName

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-11-05 : 13:28:09

This is working great, but why can't I do .....

select p.*, pt.FriendlyName
from tblProjects p
left outer join tblProjectType pt on p.ProjectType = pt.ProjectTypeName AND (P.ProjectName='xxx'). I get error in Join expression error.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-11-05 : 13:37:14

That seems to parse fine for me. Can you provide a small set of sample data?

Also, are you trying to have the FriendlyName for every record where ProjectName does not equal 'xxx' to be null or to have them excluded from your result? Your above query will still return rows where ProjectName <> 'xxx' but will show the FriendlyName as null whether it is available in the tblProjectType table or not. If you wish to exclude those records from your result set then you need to move that logic to the where clause like so:

select p.*, pt.FriendlyName
from tblProjects p
left outer join tblProjectType pt on p.ProjectType = t.ProjectTypeName
where (P.ProjectName='xxx')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources