select 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

General SQL Server Forums

New to SQL Server Programming

select query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2009-11-17 : 02:25:52

Hi Experts,

i have two table LP_Language and LP_Expert please find the sample datas

CREATE TABLE [LP_Language] (
[LanguageId] [bigint] IDENTITY (1, 1) NOT NULL ,
[Language] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

insert into LP_Language(Language)values('English')
insert into LP_Language(Language)values('French')
insert into LP_Language(Language)values('Greek')
insert into LP_Language(Language)values('German')
insert into LP_Language(Language)values('Turkish')
insert into LP_Language(Language)values('Italian')
insert into LP_Language(Language)values('Spanish')
insert into LP_Language(Language)values('Romen')
insert into LP_Language(Language)values('Polish')
insert into LP_Language(Language)values('Ukraine')

CREATE TABLE [LP_Expert] (
[ExpertId] [bigint] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LanguageId] [bigint] NULL ,
[MorethanoneLanguages] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_LP_Expert] PRIMARY KEY CLUSTERED
(
[ExpertId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO LP_Expert(Name,LanguageId,MorethanoneLanguages)VALUES('sports',1,'1,2,10')
INSERT INTO LP_Expert(Name,LanguageId,MorethanoneLanguages)VALUES('Tennis',1,'3,4,10')
INSERT INTO LP_Expert(Name,LanguageId,MorethanoneLanguages)VALUES('Foodball',2,'5,6,7,8,9')
INSERT INTO LP_Expert(Name,LanguageId,MorethanoneLanguages)VALUES('vollyball',1,'8,10')

Expected output

-----------------------------------------------------------------
ExpertId Name LanguageId MorethanoneLanguages
-----------------------------------------------------------------
1 sports 1 1,2,10
3 Foodball 2 5,6,7,8,9
------------------------------------------------------------------

I am using the below query to fetch the data 3 ExpertId record only this is incorrect

could please guid me this one

select * from LP_Expert where ((LanguageId=2) OR (MorethanoneLanguages IN (CONVERT(VARCHAR(64),(select LanguageId from LP_Language where LanguageId=2)))))

if you need more clarification means i will explain you

Thanks an advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 02:37:17

Try

select * from LP_Expert where ','+MorethanoneLanguages+',' like '%,2,%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources