There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it … Read more
Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it’s in the older definitions, just wasn’t particularly widely used before then). The use of the JOIN syntax is … Read more
You are probably looking for select_related, which is the natural way to achieve this: pubs = publication.objects.select_related(‘country’, ‘country_state’, ‘city’) You can check the resulting SQL via str(pubs.query), which should result in output along the following lines (the example is from a postgres backend): SELECT “publication”.”id”, “publication”.”title”, …, “country”.”country_name”, … FROM “publication” INNER JOIN “country” ON … Read more
SELECT * FROM table1 INNER JOIN table2 ON table1.primaryKey=table2.table1Id INNER JOIN table3 ON table1.primaryKey=table3.table1Id
You need to have a subquery to get their latest date per user ID. SELECT a.*, c.* FROM users a INNER JOIN payments c ON a.id = c.user_ID INNER JOIN ( SELECT user_ID, MAX(date) maxDate FROM payments GROUP BY user_ID ) b ON c.user_ID = b.user_ID AND c.date = b.maxDate WHERE a.package = 1
As pulled from the accepted answer in question 44917: Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating the implicit outer join syntax. In MySql the results are the same. I would personally stick with joining tables explicitly… that is the “socialy acceptable” way of … Read more
I don’t know how reduce would help here, but you could use a Map to accomplish the same task in O(n): const a = [ {id: 4, name: ‘Greg’}, {id: 1, name: ‘David’}, {id: 2, name: ‘John’}, {id: 3, name: ‘Matt’}]; const b = [ {id: 5, name: ‘Mathew’, position: ‘1’}, {id: 6, name: ‘Gracia’, … Read more
This is where the window function row_number() comes in handy: SELECT s.siteName, s.siteIP, h.date FROM sites s INNER JOIN (select h.*, row_number() over (partition by siteName order by date desc) as seqnum from history h ) h ON s.siteName = h.siteName and seqnum = 1 ORDER BY s.siteName, h.date
A subsequent inner join will only “essentially invalidate” an outer join if the inner join’s ON clause requires should-be-optional rows to be present. In such a case, reordering the join either won’t work or won’t help; rather, the only fix is to change the inner join to an appropriate outer join. So, for example, this … Read more
SELECT mt1.ID, mt1.Kilometers, mt1.date, mt1.Kilometers – IFNULL(mt2.Kilometers, 0) AS number_km_since_last_date FROM myTable mt1 LEFT JOIN myTable mt2 ON mt2.Date = ( SELECT MAX(Date) FROM myTable mt3 WHERE mt3.Date < mt1.Date ) ORDER BY mt1.date Sql Fiddle Or, by emulating a lag() function through MySql hackiness… SET @kilo=0; SELECT mt1.ID, mt1.Kilometers – @kilo AS number_km_since_last_date, @kilo … Read more