Use a dependent subquery with max() function in a join condition. Something like in this example: SELECT * FROM companies c LEFT JOIN relationship r ON c.company_id = r.company_id AND r.”begin” = ( SELECT max(“begin”) FROM relationship r1 WHERE c.company_id = r1.company_id ) INNER JOIN addresses a ON a.address_id = r.address_id demo: http://sqlfiddle.com/#!15/f80c6/2
No, since it’s a join, it can produce cardinalities greater than 1 for the rows in A. That is, if there are multiple matching rows from B then a row in A will show up multiple times. Example: Table A: id name — ——- 1 Alice 2 Malcolm 3 Kelly Table B: id_a preferred_food —- … Read more
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 always come across this question when searching for how to make LEFT JOIN depend on a further INNER JOIN. Here is an example for what I am searching when I am searching for “using LEFT JOIN and INNER JOIN in the same query”: SELECT * FROM foo f1 LEFT JOIN (bar b1 INNER JOIN … Read more
Use: ISNULL(count(*), 0)
Example with tables A and B: A (parent) B (child) ============ ============= id | name pid | name ———— ————- 1 | Alex 1 | Kate 2 | Bill 1 | Lia 3 | Cath 3 | Mary 4 | Dale NULL | Pan 5 | Evan If you want to find parents and their … Read more
@florian gave you the correct answer but let me try to explain it on example: In sql, joins are done like this: SELECT * FROM category LEFT JOIN product ON product.category_id = category.id (or something like this) Now in Doctrine, you don’t need to use ON clause because doctrine knows that from relations annotations in … Read more
Given how little of the query is being exposed; a very rough rule of thumb is to replace an Or with a Union to avoid table scanning. Select.. LEFT JOIN Child c ON c.ParentAId = a.ParentAId union Select.. left Join Child c ON c.ParentBId = b.ParentBId
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
Fix the LEFT JOIN This should work: SELECT o.name AS organisation_name, count(e.id) AS total_used FROM organisations o LEFT JOIN exam_items e ON e.organisation_id = o.id AND e.item_template_id = #{sanitize(item_template_id)} AND e.used GROUP BY o.name ORDER BY o.name; You had a LEFT [OUTER] JOIN but the later WHERE conditions made it act like a plain [INNER] … Read more