Search for a parameter in a table and return bit

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

Search for a parameter in a table and return bit

Author

Topic

ojogador13
Starting Member

4 Posts

Posted - 2008-07-09 : 06:22:55

Hi,

I think a have a problem that goes in the category Easier done than Explained.

What I have a view that return some columns all good there, what I want now is to add a new columns that returns a bit(True or False).

I what the bit to turn 1 or "true" when the following happens.
A have to tables Supplyer and SupplyersContacts they are connected both with the ID of the Supplyer when this one has a contact.

So I what to check the supplyerContacts table for the Id of a Supplyer if no value is found this means he has no contacts and should return 0 "false" case he finds a value that Supplyer has contacts and it should return 1 "true"

I hope I made my problem clear, any help would be appreciated.
Thanks in Advance.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-09 : 06:32:05

select
case when sc.ID is null then 0 else 1 end as yourbitfield
from supplier s
left join suppliercontracts sc
on s.id = sc.id

ojogador13
Starting Member

4 Posts

Posted - 2008-07-09 : 07:17:18

Hi there again

It worked but now I have another problem every time a Supplyer has more than one contact it repeats the supllyer name in the View

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 07:52:43

quote:
Originally posted by ojogador13

Hi there again

It worked but now I have another problem every time a Supplyer has more than one contact it repeats the supllyer name in the View

what do you want it to do in that case? show only one record per supplier?

ojogador13
Starting Member

4 Posts

Posted - 2008-07-09 : 08:03:50

I figured out a solution I just can´t put it in the view as a column

stfornecedorcontacto ==> SupplyerContacts
stfornecedor ==> Supplyer

select (select count(*) from stfornecedorcontacto where fornecedor = a.fornecedor), * from stfornecedor a

how can I add a collumns that returns this value?

ojogador13
Starting Member

4 Posts

Posted - 2008-07-09 : 08:15:53

Thanks I for the help I solved the problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 08:17:37

did you mean this?

select a.*,b.contactcount  from stfornecedor a
inner join (select fornecedor,count(contactnofieldhere) as contactcount from stfornecedorcontacto group by fornecedor)b
on b.fornecedor = a.fornecedor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources