SQL question (1 Viewer)

[BlueJacket]

I'm trying to get a better grasp of SQL and JOINS. One of the things I'm doing to help the process is looking at queries I've created in SQL view, and I'm a bit confused at how Access is writing the JOINS.

I have 5 tables:
tblPropertyDetails
tblQuietTitle_PropertyDetails
tblQuietTitle
tblAttorneys
tblJudges

In Design View, the tables are organized like so:

tblPropertyDetails --->tblQuietTitle_PropertyDetails--->tblQuietTitle----tblJudges
                                                              |----tblAttorneys
[COLOR="Blue"]*both tblJudges and tblAttorneys have INNER JOINS on tblQuietTitle. All other joins are LEFT JOINS.[/COLOR]

So it seems like the SQL code should read with a lot of LEFT JOINS and some INNER JOINS, but instead Access has it written like so:

FROM tblPropertyDetails LEFT JOIN [COLOR="GREEN"](tblJudges RIGHT JOIN[/COLOR] [COLOR="Blue"](tblAttorneys RIGHT JOIN[/COLOR] 
[COLOR="Red"](tblQuietTitle_PropertDetails LEFT JOIN tblQuietTitle ON tblQuietTitle_PropertDetails.CaseID = 
tblQuietTitle.CaseID)[/COLOR] [COLOR="blue"]ON tblAttorneys.AttorneyID = tblQuietTitle.AttorneyID)[/COLOR] [COLOR="GREEN"]ON tblJudges.JudgeID = 
tblQuietTitle.JudgeID)[/COLOR] ON tblPropertyDetails.PropertyID = tblQuietTitle_PropertDetails.PropertyID;

I'm having a hard time understanding why. It seems like it's doing RED, then BLUE, then GREEN, then BLACK in that order, but I don't understand the Green and Blue parts specifically and why they're written as they are.

If any of that was followable, help in understanding and breaking down what's going on would be most helpful

Thanks in advance.

 

[plog]

That's super unkosher and I have no desire to figure out what is happening inside of it. I can venture a guess as to why: I think it has to do with the order in which you added the tables to the design view. From there it created RIGHT/LEFT joins based on the order the table was added. I bet if you started from scratch and added your tables in a different order you would get different SQL that would function the same.

SQL generated by Access is hardly ever the most human readable. There's really no reason to have both LEFT and RIGHT JOINS in the same query. Actually, I'm all for eliminating the RIGHT JOIN keyword entirely since it serves no real distinct purpose.

 

[BlueJacket]

Ok. So it's not just that I'm dumb, the code is also wonky. Good to know.

So how should that be written out? How do you join multiple tables to one table? Would it be something like...

FROM tblPropertyDetails LEFT JOIN (tblQuietTitle_PropertyDetails LEFT JOIN (tblQuietTitle INNER JOIN 
tblJudges (tblQuietTitle INNER JOIN tblAttorneys ON tblQuietTitle.AttorneyID = tblAttorney.AttorneyID) 
ON tblQuietTitle.JudgeID = tblJudges.JudgeID) ON tblQuietTitle_PropertyDetails.CaseID = tblQuietTitle.CaseID) 
ON tblPropertyDetails.PropertyID = tblQuietTitle_PropertyDetails.PropertyID;

?

 

[plog]

If I'm writing actual SQL first and foremost I write it so a human can read it. That means each new clause on its own line, no unnecessary parenthesis, each new JOIN must connect to something that came before it and I run the query after each new JOIN to make sure I did it right. I wouldn't nest JOINS like you have, I write one completely, then move to the next one.

I'm going to use the initial diagram of how your tables relate to write my SQL:

SELECT tblPropertyDetails.PropertyID
FROM tblPropertyDetails
LEFT JOIN tblQuietTitle_PropertyDetails
    ON tblQuietTitle_PropertyDetails.PropertyID = tblPropertyDetails.PropertyID
LEFT JOIN tblQuietTitle
    ON tblQuietTitle.CaseID = tblQuietTitle.CaseID
LEFT JOIN tblAttorneys
    ON tblAttorneys.AttorneyID = tblQuietTitle.AttorneyID
LEFT JOIN tblJudges
    ON tblJudges.JudgeID = tblQuietTitle.JudgeID;

Again, I would run that 5 times as I built it. Once after I add the FROM, then once after every LEFT JOIN I added. This lets me make sure I wrote it right and gives me an idea of how the total number of records are affected. Since I am only using LEFT JOINS the number of records can only increase as add more LEFT JOINS. I may or may not want that, but by running it after each new LEFT JOIN I can determine if that specific JOIN is causing duplicates.

 

[BlueJacket]

Ok. Seeing that and how it relates to my data specifically helped make a lot of things click.

Thank you.

 

[Galaxiom]

If I'm writing actual SQL first and foremost I write it so a human can read it. That means each new clause on its own line, no unnecessary parenthesis, ...

SELECT tblPropertyDetails.PropertyID
FROM tblPropertyDetails
LEFT JOIN tblQuietTitle_PropertyDetails
    ON tblQuietTitle_PropertyDetails.PropertyID = tblPropertyDetails.PropertyID
LEFT JOIN tblQuietTitle
    ON tblQuietTitle.CaseID = tblQuietTitle.CaseID
LEFT JOIN tblAttorneys
    ON tblAttorneys.AttorneyID = tblQuietTitle.AttorneyID
LEFT JOIN tblJudges
    ON tblJudges.JudgeID = tblQuietTitle.JudgeID;

Except that doesn't work in Access. Access demands the joins are nested with parentheses.

 

[BlueJacket]

@Galaxoim,

I noticed. Though, that's fine. Access seems to have its own way of writing SQL, so it doesn't seem like it would help much in learning SQL in an applicable way if I want to move beyond Access.

 

[plog]

I'm so sorry. I use the design viewer for Access (and tweak the SQL when necessary) and write Oracle and MySQL SQL by hand. I know each flavor of SQL is different, didn't realize Access required that.

 

[Galaxiom]

Access seems to have its own way of writing SQL, so it doesn't seem like it would help much in learning SQL in an applicable way if I want to move beyond Access.

It is still useful to learn the Access SQL dialect. It forces you to really come to terms with laying out the structure, though given half a chance it will trash your careful formatting.

Other versions are easy to use after Access SQL. MSSQL Management Studio is a joy. It has Intellisense that prompts with database, schema, table and field names.

Quite opposite to Access's propensity to obsessively clutter, it automatically removes unnecessary parentheses.