Post reply

Create a table and join it with another existing table

  • August 2, 2017 at 8:14 am

    #324731

    Hi guys,

    I have a query that executes certain columns, let's say something like that :

    Select ID, TransactionTime, Points  From Members Where Points >= 20
     Result : 

    ID          TransactionTime             Points
    1         2017-07-01  23:45:45           22
    2         2017-07-01  22:55:55           20
    3         2017-07-01  21:41:42           24

    Now I want that table that i executed through a query - to be named as a new table , so i can Inner Join it with another table and pull out the 'Points' field

    Any suggestions?

    Thanks

  • August 2, 2017 at 8:18 am

    #1953777

    I'm not entirely sure what your goal is here. Why can you not filter your result set when doing your JOIN? If, for whatever reason, you don't want to filter whn you JOIN the 2 tables (again, why?0 then you could create a VIEW instead, and then JOIN to that.

    Storing the data again in a new table would mean that if the data in the original table was updated, the "new table" would be incorrect, thus providing incorrect results.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • August 2, 2017 at 9:32 am

    #1953794

    You could use a CTE

    With myCTE
    as
    (Select ID, TransactionTime, Points From Members Where Points >= 20)
    select *
    from myCTE
     inner join someothertable a
    on myCTE.ID = a.ID

    Keep in mind, a query doesn't create a table. This isn't a structure. The query is run and a result returned. The result is in memory on the client, not in the database.

    However,  as  Thom A mentioned,  you could just


    Select ID, TransactionTime, Points
    From Members m
      innner join someothertable a
      on m.ID = a.ID
     Where m.Points >= 20

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 2, 2017 at 9:43 am

    #1953801

    ok I see,
    I have some other limitations which deny me from using  a regular join
    Anyway I think i'll skip it and try to export it to excel and do something manually

    Thanks though for your efforts and explanations

  • August 2, 2017 at 9:50 am

    #1953804

    JohnDoe564 - Wednesday, August 2, 2017 9:43 AM

    ok I see,
    I have some other limitations which deny me from using  a regular join
    Anyway I think i'll skip it and try to export it to excel and do something manually

    Thanks though for your efforts and explanations

    Huh....skip it and export to excel?
    maybe if you gave us more details then perhaps we could help ....exporting to excel seems like a "fudge"...more work, less automation ...yadda yadda

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply