+ 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
    896
    Thank Post
    54
    Thanked 152 Times in 105 Posts
    Rep Power
    65

    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,161
    Thank Post
    95
    Thanked 318 Times in 260 Posts
    Blog Entries
    4
    Rep Power
    111
    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
    896
    Thank Post
    54
    Thanked 152 Times in 105 Posts
    Rep Power
    65
    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,090
    Thank Post
    511
    Thanked 2,308 Times in 1,784 Posts
    Blog Entries
    24
    Rep Power
    803
    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,161
    Thank Post
    95
    Thanked 318 Times in 260 Posts
    Blog Entries
    4
    Rep Power
    111
    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
    896
    Thank Post
    54
    Thanked 152 Times in 105 Posts
    Rep Power
    65
    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
    78
    Thank Post
    6
    Thanked 15 Times in 11 Posts
    Rep Power
    16
    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
    234
    Thank Post
    17
    Thanked 32 Times in 24 Posts
    Rep Power
    19
    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
  •