October 13, 2006 at 7:43 am
I'm trying to write a query from two tables below:
Table 1: users (user_ky int, firstName varchar(50), lastName varchar(50)...)
Sample Data: user_ky lastName
1 user1
2 user2
Table 2: forms (frm_ky int, FK_vendors varchar(50), projName varchar(50)...)
Sample Data: frm_ky FK_vendors projName
1 1,2 project1
My original SQL statement: Select frm_ky, FK_vendors, projName From forms
What I need is to link FK_vendors value to users table so I can get result like this:
frm_ky FK_vendors projName
1 user1, user2 project1
Please advise and thanks,
Jay
October 13, 2006 at 10:30 am
I would posit that what you need to do is change the input of your data from:
Sample Data: frm_ky FK_vendors projName
1 1,2 project1
To:
Sample Data: frm_ky FK_vendors projName
1 1 project1
1 2 project1 [ not sure if frm_ky is unique... or if is actually needs to be displayed in the output... ]
Otherwise, I would look for an UDF that will split out the multiple values within FK_vendors and do your selection based upon that... This structure looks as if will make for many "painful" retrievals...
Just my $0.02. Good luck.
I wasn't born stupid - I had to study.
October 13, 2006 at 11:07 am
Thanks, Farrell. The reason that I stored multiple user IDs in one field was trying to save some database space since the form was really huge. I haven't received any solutions on SQL side, so I've decided to create a second set of recordset in my web application to show vendor information.
October 13, 2006 at 4:36 pm
What are you trying to save?
Database space or form space?
Empty value in nvarchar cell takes 3 bytes. Every charachter adds 2 bytes.
So, value '1,2' will take 9 bytes, smallint 1 abd 2 will take 4 bytes.
Value '1234,5678' will take 21 byte, smallint 1234 and 5678 will take same 4 bytes.
Not to mention enourmous overheads for hash joins, huge log file because of cursoring, etc.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply