WebSQL Join Issues

example.md

To show the true results of a join, we'd have to break SQL convention and not repeat column names across multiple tables. Rows are expressed as Objects, so if you are trying to join the following tables with the following columns:

orders_table
id  customer_id  stock_id

customers_table
id  address      state

stocks_table
id  symbol       name

with the following conventional join:

SELECT * from orders_table
JOIN customers_table
ON orders_table.customer_id =
   customers_table.id
JOIN stocks_table
ON orders_table.stock_id =
   stocks_table.id
WHERE state = 'FL';

you'd end up with an object that looks like:

const row = {
  id: 56,
  customer_id: 161,
  stock_id: 56 ,
  address: "4604 Sullivan Point",
  state: "FL",
  symbol: "DEST",
  name: "Destination Maternity Corporation"
}

problem being, the id key only appears once, and we get an incomplete picture of the data, AND, this is the order returned by Object.keys(), so you would expect id to represent the id from the orders table, but its really the id from the stocks table (the last one to be added to the object - overwrites the previous one). This is crucial, because the whole point of teaching joins is to show the relationships of data between tables.

The complete data might look like this:

const completeRow = {
  id: 22,
  customer_id: 161,
  stock_id: 56,
  id: 161,
  address: "4604 Sullivan Point",
  state: "FL",
  id: 56,
  symbol: "DEST",
  name: "Destination Maternity Corporation"
}

This, however, is impossible. But this way, you can see that the orders_table was joined with customers_table based on the match of orders_table.customer_id and customers_table.id, and orders_table was joined with stocks_table based on the match of orders_table.stock_id and stocks_table.id

Since we want to render a complete picture of the data when we render the tables in our UI, the only way I see around this is to come up with funky, non-repeating names for columns across tables that represent the same piece of data (which would of course be unnaceptable in a real world setting, so I have concerns also about enforicing bad practices with this).

The result might be a schema, query, and result that would look like this:

orders_table
order_id     c_id      s_id

customers_table
customer_id  address   state

stocks_table
stock_id     symbol    name

really bad practice:

SELECT * from orders_table
JOIN customers_table
ON orders_table.c_id =
   customers_table.customer_id
JOIN stocks_table
ON orders_table.s_id =
   stocks_table.stock_id
WHERE state = 'FL';

possible, but not ideal:

const completeBadRow = {
  order_id: 22,
  c_id: 161,
  s_id: 56,
  customer_id: 161,
  address: "4604 Sullivan Point",
  state: "FL",
  stock_id: 56,
  symbol: "DEST",
  name: "Destination Maternity Corporation"
}