Tips or tricks for translating sql joins from literal language to SQL syntax?

Question

I often know exactly what I want, and know how tables are related in order to get it, but I have a real hard time translating that literal language knowledge to SQL syntax when it comes to joins. Do you have any tips or tricks you can share that have worked for you in the past?

This is a basic, but poor example:

"I have Categories, which have one-to-many Products, which have one-to-many Variants, which have one-to-many Sources. I need all Sources that belong to Category XYZ."

I imagine doing something where you cross out certain language terms and replace them with SQL syntax. Can you share how you formulate your queries based upon some concept similar to that? Thanks!

Answer 1

One word: Practice.

Open up the query manager and start running queries until you get what you want. Look up similar examples and adapt them to your situation. You will always have to do some trial and error with the queries to get them right.

Answer 2

• Use SQL Query Designer to easily buid Join queries from the visual table collection right there, then if you want to learn how it works, simply investigate it, that's how I learned it. You won't notice how charming it is till you try it.

• Visual Representation of SQL Joins - A walkthrough explaining SQL JOINs.

• Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005 (View snapshot bellow).

• ToTraceString of Entity Frameork' ObjectQuery (that you add Include shapings to it) is also a good way to learn it.

• SQL-Server Join types (with detailed examples for each join type):
  INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data. Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity.
  Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries. Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application. As such, please take the time to understand the data being requested then select the proper join option.
  Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.
  LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table. On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table.
  Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another. So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.
  RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.
  Self Join - In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.
  CROSS JOIN - Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows. The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table. Please heed caution when using a CROSS JOIN.
  FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

Answer 3

I think most people approach its:

• Look for substantives, as they can point to potential tables
• Look for adjectives, cause they probably are fields
• Relationships between substantives gives JOIN rules

Nothing better than to draw these structures in a paper sheet.

Answer 4

1. Write and debug a query which returns the fields from the table having the majority of—or the most important—data. Add constraints which depend only on that table, or which are independent of all tables.
2. Add a new where term which relates another table.
3. Repeat 2 until done.

I've yet to use the join operator in a query, even after 20+ years of writing SQL queries. One can almost always write them in the form

 select field, field2, field3, <etc.>
 from table
 where field in (select whatever from table2 where whatever) and
       field2 in (select whatever from table2 where whatever) and ...

or

 select field, field2, field3, <etc.>
 from table1, table2, ...
 where table1.field = table2.somefield and
       table1.field2 = table3.someotherfield and ...

Like someone else wrote, just be bold and practice. It will be like riding a bicycle after 4 or 5 times creating such a query.

Answer 5

SQL is very different from imperative programming.

1) To design tables, consider Entities (the real THINGS in the world of concern), Relationships (between the Entities), and Attributes (the values associated with an Entity).

2) to write a Select statement consider a plastic extrusion press:
  a) you put in raw records From tables, Where conditions exist in the records
  b) you may have to join tables to get at the data you need
  c) you craft extrusion nozzles to make the plastic into the shapes you want. These are the individual expressions of the select List.
  d) you may want the n-ary sets of list data to come to you in a certain order, you can apply an Order By clause.

3) crafting the List expressions is the most like imperative programming after you discover the if(exp,true-exp,false-exp) function.

Answer 6

Look at the ERD.
Logical or physical version, it will show what tables are related to one another. This way, you can see what table(s) you need to join to in order to get from point/table a to point/table b, and what criteria.