using IN with inner join

svibuk
 
on Jan 28, 2013 03:31 AM
2015 Views

i hve multiple tables joined using inner join to the main table

but one of the field in the main table details has a field ( languages )having data as (3,7,8)

i need to display the corresponding language 

 

 

select l.LANGuage from  M_Language l where l.LID in(SELECT LANGUAGES FROM details  WHERE  memberid='000005' )

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mustafa
 
on Jan 28, 2013 09:32 AM
on Jan 29, 2013 01:31 AM

Hi svibuk,

The output which you have mentioned above cannot be generated through a single line of query. For this you will have to create the function. So you can create the below function in SQL like this

--SELECT [dbo].[GetLanguages] ('000004')
CREATE FUNCTION GetLanguages 
(
	@MemberId VARCHAR(10)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @Languages AS VARCHAR(50)
	DECLARE @LanguagesKnown AS VARCHAR(MAX)
	DECLARE @Char AS VARCHAR(1)
	DECLARE @Length AS INT
	DECLARE @I AS INT

	SELECT @Languages = Languages  FROM Details WHERE MemberId = @MemberId

	SET @Length = LEN(@Languages)
	SET @LanguagesKnown = ''
	SET @I = 0
	WHILE(@I <= @Length) 
	BEGIN
		 SET @Char = SUBSTRING (@Languages,@I,1)
		 IF @Char <> ','
		 BEGIN
			SELECT @LanguagesKnown = (@LanguagesKnown + [Language] + ', ') FROM M_Language WHERE LID = CONVERT(INT,@Char)
		 END
		
		 SET @I = @I + 1
	END

    SET @LanguagesKnown = SUBSTRING(@LanguagesKnown,0,LEN(@LanguagesKnown)-1)
	
	RETURN @LanguagesKnown
END
GO

now you will need to call this function through code and it will give the Language Names for that particular MemberId