select s.id,s.name, max(case when e.course_id = 55 then complete else null end) as c55, max(case when e.course_id = 66 then complete else null end) as c66, max(case when e.course_id = 77 then complete else null end) as c77 from student as s left join enrollment as e on s.id = e.student_id group by s.id @Chris. … Read more
You’re so close! Since you say you’re displaying the country and year from A and limiting by A. Country of Turkey, Turkey is all you’re going to see. You either need to change the selects to be B.country and B.year or change the where clause to be B.country. This is using a cross join which … Read more
T as t1 inner join T as t2 on t1.field < t2.field FWIW, you can just use INNER JOIN for this, it’s not strictly a CROSS JOIN. MySQL (and perhaps some other RDBMS) treats these two types of join as identical, but in ANSI SQL, a cross join has no join condition — it’s a … Read more
Here are some resources which may be helpful: RailsCasts episode #163 Self-Referential Association regarding self-referential many-to-many relationships RailsCasts episode #47 Two Many-to-Many. This might be more relevant to what you’re attempting to accomplish A gist someone created for self-referential relationships using HABTM I’ll summarize the information found in those links: Given that you’re describing a … Read more
The reason why the employee-manager example is so common, is because it hits the nail on the head. A self join on a table looks for pairs of rows, like any join, but with both rows coming from the same table. Nothing special really.
You use a self join when a table references data in itself. E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee. To query the data and get information for both people in one row, you could self join like this: select e1.EmployeeID, … Read more
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join. Suppose you have two tables: Table emp1 Id Name Boss_id 1 ABC 3 2 DEF 1 3 XYZ 2 Table emp2 Id Name Boss_id 1 ABC 3 … Read more
(The following applies when every row is SQL DISTINCT, and outside SQL code similarly treats NULL like just another value.) Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out. We can use a (standard predicate logic) shorthand for the predicate … Read more
WITH q AS ( SELECT * FROM mytable WHERE ParentID IS NULL — this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM mytable m JOIN q ON m.parentID = q.PersonID ) SELECT * FROM q By adding the ordering condition, you can preserve the tree order: … Read more
I imagine you want this: select Manager_Name, count(Name) from Employee group by Manager_Name