6 Replies - 291 Views - Last Post: 02 December 2018 - 10:26 PM

#1 Santosh_520   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-December 18

Understading when to open & close parethesis of Access Joins

Posted 01 December 2018 - 10:21 AM

Need some help to understand the Query

SELECT MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID  FROM Tools AS MasterTool
LEFT JOIN  (Toolsets LEFT JOIN Tools ON Toolsets.SlaveToolID = Tools.ID)
ON MasterTool.ID = Toolsets.MasterToolID
GROUP BY MasterTool.Name,Toolsets.SlaveToolID,Tools.MachineID
Having (((Toolsets.SlaveToolID) = 2) And ((Tools.MachineID) = -1))
ORDER BY MasterTool.Name

Is This A Good Question/Topic? 0
  • +

Replies To: Understading when to open & close parethesis of Access Joins

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,480
  • Joined: 12-June 08

Re: Understading when to open & close parethesis of Access Joins

Posted 01 December 2018 - 10:43 AM

What's your question?
Was This Post Helpful? 0
  • +
  • -

#3 Santosh_520   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-December 18

Re: Understading when to open & close parethesis of Access Joins

Posted 02 December 2018 - 05:35 AM

View Postmodi123_1, on 01 December 2018 - 10:43 AM, said:

What's your question?


I am working on development of Database abstraction layer a kind of query builder in C++ to generate code for SQL and Access accordingly to option selected by user but in query above i am unable to understand we need enclose certain join condition in access.so i can build the query at run time accordingly.
SELECT MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID  
FROM Tools AS MasterTool
LEFT JOIN  [b](Toolsets LEFT JOIN Tools ON Toolsets.SlaveToolID = Tools.ID)[/b]
ON MasterTool.ID = Toolsets.MasterToolID
GROUP BY MasterTool.Name,Toolsets.SlaveToolID,Tools.MachineID
Having (((Toolsets.SlaveToolID) = 2) And ((Tools.MachineID) = -1))
ORDER BY MasterTool.Name

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,480
  • Joined: 12-June 08

Re: Understading when to open & close parethesis of Access Joins

Posted 02 December 2018 - 10:46 AM

I don't get what your joints are doing. You shouldn't need parentheses.
SELECT *
FROM table1 a
JOIN table2 b on a.id = b.id
where.. conditions.. blah..blah..blah

Was This Post Helpful? 0
  • +
  • -

#5 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7449
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: Understading when to open & close parethesis of Access Joins

Posted 02 December 2018 - 12:59 PM

Step one, never use access. Its SQL can be non standard and the generated stuff is generally awful. The plethora of parens really just demonstrates a lazy code generator: better to put in parentheses when not needed than risk missing them when they are needed. MS Access join generator is particularly eccentric.

This:
SELECT MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID  FROM Tools AS MasterTool
LEFT JOIN  (Toolsets LEFT JOIN Tools ON Toolsets.SlaveToolID = Tools.ID)
ON MasterTool.ID = Toolsets.MasterToolID
GROUP BY MasterTool.Name,Toolsets.SlaveToolID,Tools.MachineID
Having (((Toolsets.SlaveToolID) = 2) And ((Tools.MachineID) = -1))
ORDER BY MasterTool.Name




Could be written as:
SELECT MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID
    FROM Tools AS MasterTool
        LEFT JOIN Toolsets
            ON MasterTool.ID = Toolsets.MasterToolID 
        LEFT JOIN Tools 
            ON Toolsets.SlaveToolID = Tools.ID 
    GROUP BY MasterTool.Name,Toolsets.SlaveToolID,Tools.MachineID
    Having Toolsets.SlaveToolID = 2 And Tools.MachineID = -1
    ORDER BY MasterTool.Name



And now, looking at it, there seems to be some code smell. Why the group by? Having is not being used on an aggregate, so is being used wrong. As the having requires both joins to return results, left seems invalid.

Logically, it could be written as:
SELECT DISTINCT MasterTool.Name, Toolsets.SlaveToolID, Tools.MachineID
    FROM Tools AS MasterTool
        INNER JOIN Toolsets
            ON MasterTool.ID = Toolsets.MasterToolID 
                AND Toolsets.SlaveToolID = 2 
        INNER JOIN Tools 
            ON Toolsets.SlaveToolID = Tools.ID 
                AND Tools.MachineID = -1
    ORDER BY MasterTool.Name


I suspect the GROUP BY is being used instead of DISTINCT, both of which will obfuscate issues.

Of course, Toolsets.SlaveToolID and Tools.MachineID seem pointless to display, as they've been fixed.

Hmm... maybe:
SELECT MasterTool.Name, 2 as SlaveToolID, -1 as MachineID
    FROM Tools a
        JOIN (
            SELECT distinct a.MasterToolID as ID
                FROM Toolsets a
                    JOIN Tools b ON a.SlaveToolID = b.ID AND b.MachineID = -1
                    WHERE a.SlaveToolID = 2 
            ) b on a.ID = b.ID



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#6 Santosh_520   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 01-December 18

Re: Understading when to open & close parethesis of Access Joins

Posted 02 December 2018 - 08:11 PM

View Postmodi123_1, on 02 December 2018 - 10:46 AM, said:

I don't get what your joints are doing. You shouldn't need parentheses.
SELECT *
FROM table1 a
JOIN table2 b on a.id = b.id
where.. conditions.. blah..blah..blah


Can you just give a way to write this similar query for Ms-access in a different way so that i proceed with the changes.
Its a legacy code written 20 years ago and queries exist in VB6 Modules and i am porting the VB6 to C++ and writing the queries using the Querybuilder class written in c++ to generate queries for access and SQLServer according to User option.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,480
  • Joined: 12-June 08

Re: Understading when to open & close parethesis of Access Joins

Posted 02 December 2018 - 10:26 PM

Again.. I have never had to use parentheses in a join. I showed you the format, and you can adapt it. I am not going to do the work for you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1