locked
SQL Confused about ON and WHERE in multiple joins where order of joins matters RRS feed

  • Question

  • I need to compose some SQL which will join 3 tables, all of these joins will involve WHERE clauses.

    I'm confused about how to arrange the joins and where the WHERE clauses go and any parentheses that are needed

    Table A is related to Table B via SITE_CD

    Table A is related to Table C via SITE_CD

    Table B is related to Table C via SITE_CD

    Table B and C are only being joined to get a column PR_ID in Table C

    So I want to join B and C BEFORE I join A

    I've tried innumerable versions of this query and all are rejected with syntax errors

    How should this be done?

    As I've said all joins involve ON and WHERE clauses so please include those in your response otherwise it will be useless to me.

    All tables have a status column which must be 'A'.

    Friday, April 21, 2017 5:50 PM

All replies

  • Hi,

    Have you tried composing the query using the grid? Just curious ...

    Friday, April 21, 2017 6:11 PM
  • No, I never ever use the grid. I always compose my SQL manually.

    Since my last post, I discovered my issue is actually a little trickier since the column I need from Table C (PR_ID) needs to be joined to either Table D or Table E (it will be in one table or the other).

    If I include two more LEFT joins, one for each table, would that do it?

    Yes I know its not normalized, but I am dealing with a database and tables I did not create or design and have no influence over.


    Friday, April 21, 2017 7:54 PM
  • Try composing your inner query (the "before" query) with B and C and save it as Query1.  Then build your outer query joining table A to Query1 and save that as Query2.  

    If you're trying to see how to do this with a single query, open Query2 in SQL view and replace "Query1" in your FROM clause with "(" followed by all of the SQL from Query1 followed by ") as Query1"

    So SELECT A.Field1 FROM A INNER JOIN Query1 ON A.SITE_CD = Query1.SITE_CD

    becomes

    SELECT A.Field1 FROM A INNER JOIN (SELECT...) AS Query1 ON A.SITE_CD = Query1.SITE_CD

    Or alternately you can post one of your attempts so we can look at it.

    -Bruce

    Friday, April 21, 2017 8:12 PM
  • No, I never ever use the grid. I always compose my SQL manually.

    I wish I could say the same but my eyes get crossed looking at the SQL statement and trying to figure it out.

    Cheers!

    Friday, April 21, 2017 9:07 PM
  • Well I also use SQL Server and mainframe databases quite heavily, so I'm not going to learn a separate SQL build tool for each, I'm more comfortable coding it from scratch. One thing I miss from SQL Server, is being able to highlight part of the SQL and just run the highlighted portion.
    Friday, April 21, 2017 11:54 PM
  • I share your preference for the SQL Server sql editor, but Access insists on parentheses when using multiple join conditions in the From clause. I find it difficult to get the parentheses correct when editing Access sql text, so I use the query design view at least to get the joins implemented. Then I switch to sql text editing for any complicated Select or Where clauses.

    I'm not sure about Access but most sql query processing implementations ignore any join order with inner joins and optimize the full query expression.


    Paul

    Sunday, April 23, 2017 8:16 PM
  • Well I also use SQL Server and mainframe databases quite heavily, so I'm not going to learn a separate SQL build tool for each, I'm more comfortable coding it from scratch. One thing I miss from SQL Server, is being able to highlight part of the SQL and just run the highlighted portion.

    Hi ATGNW,

    All the queries (only Access) that I use, are generated automatically depending on the context in which they are needed. Parentheses are added automatically.

    You could say: Generalization goes down to string manipulation.

    Imb.

    Sunday, April 23, 2017 9:40 PM
  • Hi AllTheGoodNamesWereTaken,

    if you still have an issue then you can try to create sample database. that contains the necessary tables with dummy data and a query created by you.

    then you can try to share that database here.

    then we can see that how your query looks exactly.

    we can try to solve the issue and can provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 24, 2017 8:54 AM
  • Not sure I fully understand your problem but maybe:

    SELECT a1..., b1..., c1..., c2.PR_ID 

    FROM TableA AS a1 

    LEFT JOIN TableB AS b1 ON a1.SITE_CD = b1.SITE_CD 

    LEFT JOIN TableC AS c1 ON a1.SITE_CD = c1.SITE_CD 

    LEFT JOIN TableC AS c2 ON b.SITE_CD = c2.SITE_CD

    Otherwise if you REALLY need to get one result set BEFORE another, then try a sub-query.

    HTH

    Wednesday, April 26, 2017 5:33 PM