select and where statement

[softboy12]

Hi all

I have the following problem with the statement below

---------------------------------------------------------
SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best] FROM [data].[dbo].[Kunden] WHERE [FK_ID_SERVER]= select [BS] from [data].[dbo].[Server] Where [BS]='NT'
--------------------------------------------------------

i need to select only the recordsets where FK_ID_Server and BS match, how can i realize this, which means, if FK_ID_Server = 1 then BS = Linux and so on, so i can select the right recordset

thank you in advance for your trouble

 

[KenReay]

Hi

SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best] FROM [data].[dbo].[Kunden] WHERE [FK_ID_SERVER]IN (select [BS] from [data].[dbo].[Server] Where [BS]='NT');

would do it

but if you know the values of FK_ID_Server you could shorten it to

SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best] FROM [data].[dbo].[Kunden] WHERE [FK_ID_SERVER]= 1

or

SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best] FROM [data].[dbo].[Kunden] WHERE [FK_ID_SERVER]= '1'

if FK_id_Server is a string column




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[softboy12]

Hi Ken

Thanks but the following statement

SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best] FROM [data].[dbo].[Kunden] WHERE [FK_ID_SERVER]IN (select [BS] from [data].[dbo].[Server] Where [BS]='NT');

woud produces the following error

Syntaxfehler beim Konvertieren des nvarchar-Wertes 'NT' in eine Spalte vom Datentyp int.

maybe i shoud convert the FK_ID_Server to a string colum, cause now it's a integer ?

 

[Golom]

It looks like this
[blue][tt]
select [BS] from [data].[dbo].[Server] Where [BS]='NT'
[/tt][/blue]
is guaranteed to return 'NT' as its value since the WHERE restricts [BS] to that value and the query returns [BS]. Can you shorten the whole query to
[blue][tt]
SELECT [ID], [Domain], [TLD], [LoginName], [Passwort], [FK_ID_Server], [Email_best]
FROM [data].[dbo].[Kunden]
WHERE [FK_ID_SERVER]= 'NT'
[/tt][/blue]?

 

[KenReay]

Hi

Golam, yes agreed, that is what I said in my earlier post

Softboy12 - sorry I do not speak or read German so the error mssage was lost on me, but if FK_ID_Server is an integer, how can it ever be equal to BS which must be a string, and has a value of NT ??

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[softboy12]

Hi all

The error messages means the following in english

Syntax Error while converting of varchar value 'NT' to a colum of type int

now it's clear why it can't work:
(thanks to Ken: --> the type of both colums are different so i can't work --> logically

question: can i convert a existing colum whithout loosing datas inside

 

[KenReay]

Hi

Yes and no, if that makes sense!

If the data is convertible it will convert eg if you have a string column and change it to a number column, then if the string holds (say) 123 it will convert, but if it holds (say) A123, that cannot be converterd to a numeric and you will lose data - TAKE A BACKUP COPY - be safe

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Golom]

KenReay
Yes. My post was essentially a restatement of what you had already said. Since it didn't appear to sink in the first time I was just taking another run at it.