Simple Query - 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 2000 Forums

SQL Server Development (2000)

Simple Query

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-04-21 : 07:46:32

Hi All

I have 2 table projmaster(projid,projname,custid) and custmaster(custid,custname)

While filling the Project master the customer selection is optional. In that case a "0" gets inserted into projmaster table as custid.

I need to display Project Name, Customer Name using these 2 tables

Please help in the query. I tried CASE statement but not working

Njoy Life

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-21 : 08:19:23

Post the query that you have and someone will let you know where you're going wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

swatib
Posting Yak Master

173 Posts

Posted - 2006-04-21 : 08:23:17

select projectid,projectname,
(case when projectmaster.custid<>0 then (select customername from custmaster,projectmaster where
projectmaster.custid=custmaster.custid) else '' end)
from projectmaster,custmaster

Also I tried

(select projectid,projectname,custmaster.customername
from projectmaster,custmaster
where projectmaster.custid=custmaster.custid)
union
(select projectid,projectname
from projectmaster where custid=0)

But UNION operator must have an equal number of expressions in their target lists.

Can't find the way

Njoy Life

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-04-21 : 08:27:46

can u provide table data, if u have. (can be solved with joins) but need data.

BMahesh

Krankensteins
Starting Member

24 Posts

Posted - 2006-04-21 : 08:41:07

In UNIOIN query add colon with value NULL :
select projectid,projectname,NULL
from projectmaster where custid=0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 08:59:29

select P.projectid,P.projectname,Isnull(C.customername,'') from
projectmaster P left outer join custmaster C
on p.custid=c.custid

Madhivanan

Failing to plan is Planning to fail

swatib
Posting Yak Master

173 Posts

Posted - 2006-04-24 : 00:26:58

Thats Great Madhivanan.......Thanks a lot

Njoy Life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources