JFrost10 SQL Function Question 1

[Alcar]

Jack,

I'm stumbling on this issue. I need to create a function that should loop through the fields of a table and get last and first names out. If it finds duplicates of last names then the function should return a string that is composed by:

vFirstName + ', ' + vFirstName + ' ' + vLastName

of course the two vFirstName fields are respectfully the first name of the first person with the same last name, and the first name of the second person with the same last name.

I have the function editor open on my VS.NET but I cannot seem to understand where I should begin with this one..
mumble mumble mumble

ps. I didn't want to post this one in the sql forum (full of strangers hehehe) I know you have knowledge in such field.

Thanks in advance! (loading a truck of purple stars for Jack)
Daren J. Lahey
Just another computer guy...

 

[jfrost10]

Hey Daren,

ok, here's my thought process to try and get this thing working (not sure of the sql syntax, but here's the flow I would start with)

1. Get a recordset of distinct last names
2. Get a recordset of all first names for each last name
3. Add hte first name to a string.
3a. If its not the last-first name, add a ','
3b. Else add a ' '
4. Once the last-first name has been written, add the
last name to the string
5. Loop to the next last name.

Thats how I'm going to tackle it. I'll let you know when I get the syntax worked out.


jack

 

[link9]

Rather than tackling this with programming, I really would post this question in the sql server forum. There are some super-sql'ers over there who have solved many problems that I would have resorting to code for with pure sql.

I've worked out something with a recursive join, but it has two problems:
(1)it won't work past two dupe lNames
(2)it shows both combos of fNames (a,b & b,a)

but I'm no sql guru, and I would bet money they could solve it over there. It's just too interesting a sql question to solve it w/ code.

Anyway, just my two cents, and here's my half-baked shot at it.

select a.fName + ',' + b.fName, a.lName 
from test a inner join test b on a.lName = b.lName
where a.fName <> b.fName
union
select fName, lName from test where lName not in
(
select a.lName 
from test a inner join test b on a.lName = b.lName
where a.fName <> b.fName
)
order by a.lName

 

[jfrost10]

Paul, you do realize that we're talking about a sql server function using T-SQL, not a vb.net function, right?

Also, I'm studying up to take my sql server 2k exam, so Daren's just helping out by throwing some probs my way to keep me fresh.



Jack

 

[link9]

No, i didn't realize that. It sounded to me like you were going for a coding solution.

Will said function return just a result set?

looking forward to seeing the solution, then.

 

[Alcar]

=) here is what I came up with (and works well) but I might have some resource issues:

CREATE FUNCTION fnGetParents
(
@AccountID int
)

RETURNS varchar (100)
AS
BEGIN
DECLARE @lastname1 as varchar(20)
DECLARE @lastname as varchar(20)
DECLARE @firstname as varchar(20)
DECLARE @result as varchar(100)

SELECT @result = ''

DECLARE curLastName CURSOR FOR
SELECT vLName FROM tblParents WHERE fkAccountID = @AccountID

OPEN curLastName

FETCH NEXT FROM curLastName INTO @lastname
SELECT @lastname1 = @lastname
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM curLastName INTO @lastname
END

CLOSE curLastName
DEALLOCATE curLastName

IF (@lastname not Like @lastname1)
BEGIN
DECLARE curParent CURSOR FOR
SELECT vFName FROM tblParents WHERE fkAccountID = @AccountID

OPEN curParent

FETCH NEXT FROM curParent INTO @firstname
SELECT @result = @result + ' and ' + @firstname + ' ' + @lastname1
FETCH NEXT FROM curParent INTO @firstname
SELECT @result = @result + ' and ' + @firstname + ' ' + @lastname

CLOSE curParent
DEALLOCATE curParent
END
ELSE
BEGIN
DECLARE curParent2 CURSOR FOR
SELECT vFName FROM tblParents WHERE fkAccountID = @AccountID

OPEN curParent2

FETCH NEXT FROM curParent2 INTO @firstname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @result = @result + ' and ' + @firstname
FETCH NEXT FROM curParent2 INTO @firstname
END

CLOSE curParent2
DEALLOCATE curParent2
END

SELECT @result = RIGHT(@result, LEN(@result) -5)


RETURN @result
END

What do you think Jack? Daren J. Lahey
Just another computer guy...

 

[Alcar]

Paul, now with this function I can create a view or a stored procedure that will have a select statement of this kind:

SELECT fnGetParents(fkAccountID) AS Parents FROM tblParents;

and the result would be:

Parents
---------------------
Mary and Daniel (case where the last name is the same)
John Grahm and Lisa Cornwell

why this? because I already have the last name of their child in my report, therefore I don't need to repeat it unless the last name is different =)

child parents
----------------------------------
Amy McDonald Mary and Daniel
Joe Grahm John Gram and Lisa Cornwell

=) Daren J. Lahey
Just another computer guy...

 

[jfrost10]

What do I think Darren? I think I need to study a bit more before I take this exam


I totally misunderstood what you were looking for: I thoght it was just a string you needed returned, but your solution is much much better than what i was going to gun for.

As for your comment on resource issues, you should be alright depending on when the users are going to run the report and how often. Ifyou could create a view, then you could set up a scheduler to update the view at like 1am every day or something, so they could have an updated report every morning. If they're running it a bit more often (like, they want to run the report every hour or something), depending on your sql server's traffic it may be an issue. How will they be using/running this report?

Jack

 

[jfrost10]

There's also another way to do this (now that I look at your table structure), but its assuming that you have a child table and a parent table (although from your code it looks like they're all part of the same table.)

Table:Child
-----------
ChildID (pk)
FName
LName
ParentID (fk)

Tablearent
------------
ParentID (pk)
FName
LName

If this were your table structure, then all you'd need to do for each Parent is

Select FName From Child Where ParentID = @ParentID

Then you wouldn't have to worry about doing all the function code at all. But again, it assumes that this fits into your schema.

Jack

 

[Alcar]

They are actually 2 tables:
tblStudents and tblParents
linked by another table (not viewed in this case): tblAccounts
I am creating a directory with these fields

student name, parents, etc...

in the case of the parents field I needed a function that would check the last names and create an adeguate string.
Sorry you miss-understood me =)

ps. Convinced my boss to get me certified in win2000.. (6 exams) details to follow =)
I do realiza .NET servers are coming out and the 2000 series will be soon outdated, but once I have that certification I can better press to get the .NET one. What do you think? Daren J. Lahey
Just another computer guy...

 

[link9]

Alcar, thanks for posting that up. Nice solution, although I can't help but to think that it did belong in the other forum -- which was my reasoning for my previous post.

Not to mention that that is their area of expertise, and you might very well have received an even better solution, which is why we have all the different topical forums.


paul

 

[Alcar]

*WHAAACKK*

ok.. ouch.. It was sent to Jack and for exercise purpouses (for both of us) but I understand. I will follow the rules =)

*rubbing his behind*

dang that hurts....

;-) Daren J. Lahey
Just another computer guy...

 

[jfrost10]

LOL!

Daren, you didn't break any 'rules'. If the title of hte post had been just "SQL Server Question", and been for everybody, I could see the whole "forum matching the question" thing. But you addressed it to me personally! Thats kewl man, and if you do come accross any other sql problems, let me know!


That said, Paul did have a point that posting in the sql server forum AS WELL as here would have given some other insights, but i don't see the big deal.

Hey, you got a star out of it though eh?


Jack

 

[link9]

I gave the star because I think this post is very helpful. Although I had some very limited knowledge of sql functions, I think what he posted is a great example of how to use one, and have actually been playing w/ some examples since he posted that (between working of course ;-))

That said, it is the desire of the site administrator that such posts be red-flagged as "off-topic". I didn't red-flag it because I know that Alcar is a helpful and productive member who wished to ask a question of Jack, and this is where Jack "lives", rather than a visitor who didn't know how to use the site.

There's no harm, and the only reason I posted that first message was because I personally know of at least one guy in the sql server forum that flat out blows me away w/ his command of the t-sql language. If I had this problem, that's who/where I would ask.

It's not that big of a deal, obviously, and I certainly meant nothing overbearing w/ the comment(s). You guys are very helpful to everyone here (including me ), just that I honestly think you could have gotten a better answer, and if it's me, that's what I'm looking for.

speaking of topics... let's change it.

**shuffles off**

 

[Alcar]

*chuckles*
I agree on all points.
---- to change subject
what do you both think about teh win2k certification? let me reports my doubts:

Alcar wrote:
Convinced my boss to get me certified in win2000.. (6 exams) details to follow =)
I do realiza .NET servers are coming out and the 2000 series will be soon outdated, but once I have that certification I can better press to get the .NET one. What do you think? Daren J. Lahey
Just another computer guy...

 

[jfrost10]

Oh man, if your boss will pay for it, get it!

Like all things microsoft, even when the new servers come out, it'll be awhile before they're widely adopted. Win2k cert will serve you well into the next few years.

Plus, from what we've seen of the new .NET server trial we installed, its still pretty much Win2k, with alot of tweaks (and probably some differences under the hood). So even if you end up taking .NEt server stuff in a few years, a good background in win2k will definately help you out.

jack