extracting data from a multifield column

cwebbe2 · #1 (**permalink**) 02-09-09, 16:02

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

gvee · #2 (**permalink**) 02-09-09, 16:20

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

cwebbe2 · #3 (**permalink**) 02-10-09, 08:23

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!

r937 · #4 (**permalink**) 02-10-09, 09:33

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

gvee · #5 (**permalink**) 02-10-09, 09:59

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?

r937 · #6 (**permalink**) 02-10-09, 10:37

Quote:

Originally Posted by georgev

what's your preference?

using identifiers (column and table names and aliases) that do not require escaping

gvee · #7 (**permalink**) 02-10-09, 10:42

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.

r937 · #8 (**permalink**) 02-10-09, 10:51

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

r937 · #9 (**permalink**) 02-10-09, 10:57

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?

gvee · #10 (**permalink**) 02-10-09, 11:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech