+ Post New Thread
Results 1 to 9 of 9
Coding Thread, SQL Magic Needed in Coding and Web Development; Hi All, I am in need of some sql magic, i have a table with the follow columns teacher , ...
  1. #1
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    967
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67

    SQL Magic Needed

    Hi All,

    I am in need of some sql magic,
    i have a table with the follow columns

    teacher , subject ,english, maths, science
    mr smith, science ,27 , 28 , 30

    i would like to return the following
    mr smith, science,30

    is this possible with sql in access?

    i would prefer doing this in sql and not vba.

  2. #2

    Join Date
    Mar 2008
    Location
    Surrey
    Posts
    2,168
    Thank Post
    98
    Thanked 319 Times in 261 Posts
    Blog Entries
    4
    Rep Power
    113
    Code:
    SELECT teacher, subject, CASE subject
      WHEN 'science' THEN science
      WHEN 'maths' THEN maths
      WHEN 'english' THEN english
      END
    FROM table
    That might do it. I assumed you didn't want a simple 'SELECT teacher, subject, science FROM table' query.
    Last edited by jamesb; 11th June 2009 at 03:55 PM.

  3. #3
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    967
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67
    thanks but there will be upto 20 subjects and i would like only one query.

  4. #4

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,892
    Thank Post
    518
    Thanked 2,494 Times in 1,935 Posts
    Blog Entries
    24
    Rep Power
    839
    You need to normalise your database, storing the reference to a column within the same table as that column is not good practice from what I know.

    Would it not be better to store the column with science/maths etc in it with an id linking it to the original table not be better? Then you can do a simple nested SELECT?

  5. #5

    Join Date
    Mar 2008
    Location
    Surrey
    Posts
    2,168
    Thank Post
    98
    Thanked 319 Times in 261 Posts
    Blog Entries
    4
    Rep Power
    113
    Well, doing it that way would give you a list of teacher, subject, subject_mark for each teacher quite happily, but it would involve typing out each subject in the CASE statement. Can't find anything to simply pull the CASE statement out of the table itself but I'll keep looking.

    (Note: Started typing this before reading localzuk's reply, so it has no bearing on that)
    Last edited by jamesb; 11th June 2009 at 04:29 PM.

  6. #6
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    967
    Thank Post
    58
    Thanked 163 Times in 115 Posts
    Rep Power
    67
    hi thanks for your ideas.

    it wont be possible to normalise the database as im pulling most of the data from anthor database which i dont have control and trying to make the best of a crap database.

    i thought about a case statement but would be hard to keep upto date.

    I think i will have to do it via vba.

  7. #7

    Join Date
    Jan 2008
    Posts
    104
    Thank Post
    6
    Thanked 18 Times in 14 Posts
    Rep Power
    17
    I'll start by saying I don't know the answer (!)

    However, I wonder if it's possible to get the list of current subjects in the database using a SELECT DISTINCT query, then loop through the results of that running a second query for each subject returned. If so, that wouldn't need updating if the subjects changed.

    I've never ever used them, but would stored procedures be able to handle this?

    Article here that has the basics on stored procedures.

    Just a thought.

  8. #8
    monkeyx's Avatar
    Join Date
    Nov 2006
    Posts
    364
    Thank Post
    8
    Thanked 52 Times in 41 Posts
    Rep Power
    25
    I have been putting of sorting out something like this in teh Serco Facility DB.

    Anyhow this great article gives some pointers to how you might do something like this

    Arrays and Lists in SQL Server 2005

  9. #9

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    255
    Thank Post
    23
    Thanked 38 Times in 29 Posts
    Rep Power
    20
    Is your source table stored in Access or MS-SQL? Probably the best way is to have a bit of VB code that will read in the un-normalised data and just populate a more normalised table (i.e. with the output in the form you're after) in code.

SHARE:
+ Post New Thread

Similar Threads

  1. [Video] Over 4 minutes of magic
    By laserblazer in forum Jokes/Interweb Things
    Replies: 0
    Last Post: 28th March 2009, 04:36 PM
  2. Did Smoothwall Tom star in "The Colour of Magic"? =)
    By ZeroHour in forum General Chat
    Replies: 3
    Last Post: 28th March 2008, 07:18 PM
  3. Replies: 12
    Last Post: 22nd October 2007, 07:15 PM
  4. Magic Wand time...!
    By tech_guy in forum General Chat
    Replies: 19
    Last Post: 11th October 2007, 09:20 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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