Problems with the SQL query function

Tags:
Query
SQL
tables
I am designing a website, and I’m having a problem with the SQL query function. My table looks like this: Table Structure Ref_ID Mem_ID 1001 1002 1001 1003 1001 1004 1002 1005 1003 1006 1003 1007 1003 1008 When I try the command “select * from table where ref_id='1001',” it returns 1002, 1003 and 1004. However, I want all the co-related data together. What can I do?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You have to write a function which strings all the values together.

<pre>CREATE FUNCTION ReturnMemIDs (@Ref_Id INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Output NVARCHAR(MAX)
SET @Output = ”
DECLARE @Mem_Id INT
DECLARE cur CURSOR FOR SELECT Mem_ID FROM YourTable WHERE Ref_Id = @Ref_Id
OPEN cur
FETCH NEXT FROM cur INTO @Mem_Id
WHILE @@FETCH_CURSOR = 0
BEGIN
SET @Output = @Output + CAST(@Mem_Id AS NVARCHAR(10)) + ‘, ‘
FETCH NEXT FROM cur INTO @Mem_Id
END
CLOSE cur
DEALLOCATE cur

SET @Output = SUBSTRING(@Output, 0, LEN(@Output)-2)

RETURN @Output
END
GO</pre>

This will take all the values and put them into a single value. You can then call the function.

<pre>SELECT dbo.ReturnMemIDs(1001)</pre>
Or
<pre>SELECT DISTINCT dbo.ReturnMemIDs(RefId)
FROM YourTable</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following