select t.Topic, t.Title, count(distinct s.starID) as StarCount, count(distinct m.User) as UserCount, count(distinct m.messageID) as MessageCount from Topics t left join Messages m ON m.Topic = t.Topic left join Stars_Given s ON s.Topic = t.Topic group by t.Topic, t.Title Sql Fiddle Or, you can perform the aggregation in sub-queries, which will likely be more efficient if … Read more
While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly, “Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query.” Consequently, you must select the calculated field using … Read more
You need to use multiple LEFT JOINs: SELECT ticket.ticket_id, a1.attr_val AS attr_val1, a2.attr_val AS attr_val2, a3.attr_val AS attr_val3 FROM ticket LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1 LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2 LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3 Here is an example: SQL Fiddle.
There is no difference between RIGHT JOIN and RIGHT OUTER JOIN. Both are the same. That means that LEFT JOIN and LEFT OUTER JOIN are the same. Visual Representation of SQL Joins
A great resource on calculating running totals in SQL Server is this document by Itzik Ben Gan that was submitted to the SQL Server Team as part of his campaign to have the OVER clause extended further from its initial SQL Server 2005 implementation. In it he shows how once you get into tens of … Read more
Effectively, WHERE conditions and JOIN conditions for [INNER] JOIN are 100 % equivalent in PostgreSQL. (It’s good practice to use explicit JOIN conditions to make queries easier to read and maintain, though). The same is not true for a LEFT JOIN combined with a WHERE condition on a table to the right of the join. … Read more
If you can assume that artist IDs increment over time, then the MIN(artist_id) will be the earliest. So try something like this (untested…) SELECT * FROM feeds f LEFT JOIN artists a ON a.artist_id = ( SELECT MIN(fa.artist_id) a_id FROM feeds_artists fa WHERE fa.feed_id = f.feed_id ) a
I think if you want to use extension methods you need to use the GroupJoin var query = people.GroupJoin(pets, person => person, pet => pet.Owner, (person, petCollection) => new { OwnerName = person.Name, Pet = PetCollection.Select( p => p.Name ) .DefaultIfEmpty() } ).ToList(); You may have to play around with the selection expression. I’m not … Read more
I thought that by not specifying a type of join it was assumed to be a LEFT JOIN. Is this not the case? No, the default join is an INNER JOIN. Here is a visual explanation of SQL joins. Inner join Left join
The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done. An example with Disney toons voting for president: declare @candidates table (name varchar(50)); insert @candidates values (‘Obama’), (‘Romney’); declare @votes table (voter varchar(50), voted_for varchar(50)); insert @votes values … Read more