Post reply

SQL Server JOINS

  • August 25, 2014 at 8:00 am

    #1740717

    Oh, an important question regarding the join

    I learnt about using CTE instead of a view so that I can do it all as sql queries

    However , adding the With part doesn't work with Access.

    Is there anything that can be similarly done in Access (or should I be moving to a new thread?)

    Thanks a lot in advance!

  • August 25, 2014 at 8:06 am

    #1740718

    Alright thank you both...I should seriously consider

  • August 25, 2014 at 8:06 am

    #1740719

    shaimaa.tarekelshoeiby (8/25/2014)

    Oh, an important question regarding the join

    I learnt about using CTE instead of a view so that I can do it all as sql queries

    However , adding the With part doesn't work with Access.

    Is there anything that can be similarly done in Access (or should I be moving to a new thread?)

    Thanks a lot in advance!

    I'm afraid that in Access you will have to stick with views.

    😎

  • August 25, 2014 at 8:16 am

    #1740727

    Nope. Access doesn't use Transact SQL and the full T-SQL stack supported by SQL Server.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 25, 2014 at 8:32 am

    #1740734

    The main problem which made me think of detaching from views is that update cannot be done (getting database exception : modification affects multiple base tables)

  • August 25, 2014 at 8:36 am

    #1740738

    shaimaa.tarekelshoeiby (8/25/2014)

    Thank you ...

    But with so limited time , what can I do to help me go through until I can have more time to read the book(s) you suggested and use pen/paper?

    Any recommended set of articles or a small daily exercise ; something of the sort? 🙂

    For this, one simply has to make time, in my case I donated the TV set to a charity:-)

    Using the simples of tools such as pen/paper helps build the understanding of the subject as it filters out the complexity of the tools and applications.

    😎

  • September 1, 2014 at 6:52 am

    #1742225

    I got help from one of the forums to write the statement in access and after a little modification it slowly works as follow:

    SELECT some columns

    FROM (Trial LEFT JOIN Culture ON Trial.CultureID = Culture.cultureID) LEFT JOIN (Select cs.CultureID, cs.WeekNr FROM (SELECT CultureID, MIN(CultureStepID) as MinCS FROM CultureStep GROUP BY CultureID ) Fcs INNER JOIN CultureStep cs ON fcs.MinCS=cs.CultureStepID) AS T ON Culture.cultureID = T.cultureID;

    However, I no longer am able to update , add or delete since the now 3rd created table T has no primary key right?

    So how can I rewrite the above statement such that I end up with tables Culture,Trial and CultureStep

    Can you please provide help on how you come up with your solution to be able to learn how to think when approaching this and similar statements 🙂

  • September 1, 2014 at 7:33 am

    #1742232

    That is , do something about this part

    Select c.CultureID,c.WeekNr FROM (SELECT CultureID,MIN(CultureStepID) CSS

    FROM CultureStep Group By CultureID)

    A inner join CultureStep c on A.CSS=c.CultureStepID

    Such that I can add it as a CultureStep table instead of a new table without primary key.

Viewing 8 posts - 16 through 22 (of 22 total)

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