Hot answers tagged join
This is a simple "filtered" outer join: select a.col1, b.col2 from table_a a left join table_b b on a.col1 = b.col1 and b.col2 = 1
One of the nice things about INNER JOIN syntax is that it actually allows you to separate filter criteria from join criteria. The two statements in your question could be collapsed into a single query as follows: UPDATE t1 SET t1.foo = temp.newFoo, t1.bar = temp.newBar FROM dbo.Table1 t1 -- please always use schema qualifiers INNER JOIN @TempTable TEMP ...
If I understand your problem correctly, you have to join the other table to historical_adresses based on the unique ID and that the visit time falls between startdate and enddate. If this is so, the query could look like: SELECT [address_elements] FROM historical_adresses AS ha JOIN visits AS v ON ha.user_id = v.user_id AND v.visit_time BETWEEN ...
You can use ADDTIME() function: tableA AS a JOIN tableB AS b ON a.datetime_column = ADDTIME(CAST(b.date_column AS DATETIME), b.time_column) This might use an index on tableA (datetime_column) but not an index on tableB. The reverse might use an index on tableB (date_column, time_column) but not on A: tableA AS a JOIN tableB AS b ON ...
That is not a GROUP BY, but UNION: select * from cant union select * from col order by id, cant_val;
Hash joins require an "equijoin predicate". So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key < B.key +1.
Only top voted, non community-wiki answers of a minimum length are eligible