How to link to and retrieve from multiple values

  • 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

     

  • 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.

  • 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.

  • 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