Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    15

    Unanswered: extracting data from a multifield column

    Hi,

    This is my first post and I think I know all the rules... and possibly have a good question.

    I am trying to extract some data from a SQL database at work and have run into a multifield column that is like what follows:

    pkid action userID multifield_1 Multifield_2

    12 1 22 233 4
    13 1 22 234 4
    14 2 23 12 Blue


    In this case, if action is set at 1 I get information on pkid 12 about multifield 1 being 233 and multifield 2 being 4, etc I understand this.

    If I select for action 2, I get a separate group of data to include multifield2 showing blue, etc. Again, no problems.


    Where my tiny head cannot comprehend and gets confused is if I wanted to get data on pkid 14, action 2, but instead of getting "12" or "blue" instead have the "12 link back to the pkid two lines up and return results "233" and "4."

    Is this possible?


    Thank you for any help.

    Chris

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT a.pkid
         , a.action
         , a.userid
         , b.multifield_1
         , b.multifield_2
    FROM   your_table As [a]
     LEFT
      JOIN your_table As [b]
        ON a.multifield_1 = b.pkid
    WHERE  a.pkid = 14
    AND    a.action = 2
    ...I think I got the right end of the stick...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    15
    This worked perfectly. And makes so much sense (as a lot of SQL does, when you think about it... but apparently not all of it).

    Thank you very much!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, you simply must slow down and take the time to learn mysql syntax

    otherwise you be always holding da brown sticky end of da stick

    those square brackets are valid only in microsoft (~ptui~) databases

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry Mr Limeback

    Old habits and all that! What do the standards suggest as identifiers? I know mysql has backticks (~ptui~) for objects... what's your preference?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    what's your preference?
    using identifiers (column and table names and aliases) that do not require escaping

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well I know they are superfluous in my usage because I do not use names that require escaping, but it's a habit I got in to because I found that it makes it easier to "spot" aliases. I previosuly used single-quotes, but found that this could be misleading, hence the switch.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    easier to "spot" aliases?

    there is a much better way... let me show u it!!
    Code:
    SELECT a.pkid
         , a.action
         , a.userid
         , b.multifield_1
         , b.multifield_2
      FROM your_table                  AS a
    LEFT OUTER
      JOIN your_table                  AS b
        ON b.pkid = a.multifield_1
     WHERE a.pkid = 14
       AND a.action = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, you could also use the standard ANSI doublequote escape characters, but you need to make sure that the ANSI_QUOTES SQL mode is enabled

    does microsoft Squeal Server give you that option?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, I did a similar thing to that before too, but found that it got rather verbose in simple text-editors (word-wrap for long lines etc)...

    yes SQL Server does allow double quotes, I think it might even be by default, but even if it's not then it's definately a setting (SET QUOTED_IDENTIFIER if memory serves).
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •