Gtjkyu

Inner joining multiple columns to one ID [duplicate] This question already has an answer here: I'm not in charge of the database so I can't change the format, I realize it is a horrible database. I have two tables I want to join: Table1: address_book id | name | address | phone number | email Table2: team id | person1_id | person2_id | person3_id | person4_id | person5_id | person6_id I would like to join all the personX_id with the name from address_book. I can't seem to figure out how to join more than one column. Hoping someone here could help! Thanks This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question. 1 Answer 1 if you have only 6 colums for person_id you could use a multiple join select a1.name from team t left join address_book a1 on a1.name = t.person1_id left join address_book a2...

Sample DVD Rental database, find a customers favorite actor I've been trying to improve my SQL join skills. I'm using the classic sample DVD Rental Database (can be found here ). I am trying to determine a customers favorite actor, by counting up all appearances the actor has appeared in all the movies the customer has rented. Right now I have this monster query that has 3 sub queries. SELECT email, actor.last_name, count(actor.last_name) FROM (SELECT email, actor_id FROM (SELECT email, film_id FROM (SELECT email, inventory_id FROM customer as cu JOIN rental ON cu.customer_id = rental.customer_id ORDER BY email) as sq JOIN inventory ON sq.inventory_id = inventory.inventory_id) as sq2 JOIN film_actor ON sq2.film_id = film_actor.film_id) as sq3 JOIN actor ON sq3.actor_id = actor.actor_id GROUP BY email, actor.last_name ORDER BY COUNT(actor.last_name) DESC; And what I end up getting is the full list of email...

Joining tables and returning the correct aggregation I'm using Sean Lahman's Baseball database to aggregate runs, hits and 'at bats', wins & losses by team between the year 2010 and 2015. I want to join Teams and Batting table and use the group by function on teamID to return total runs, hits, at bats as well as wins and losses by team from the Teams table. For instance, from the teams table I want to return wins and losses year wise team ID Name Wins Losses Year ARI Arizona Diamondbacks 65 97 2010 ARI Arizona Diamondbacks 94 68 2011 And from the Batting Table this is the output I want year teamID Runs Hits At Bats 2012 ARI 734 1416 5462 2015 ARI 720 1494 5649 I tried the following query but it is returning inflated values for wins and losses columns: select b.yearID, b.teamID, SUM(b.R) as Runs, SUM(b.H) as Hits, SUM(b.AB) as At_Bats, t.name as Team_Name, SUM(t.W) as Wins, SUM(t.L) as Losses from Batting b, Teams t where b.teamI...

LEFT JOIN - Return empty results with left join for sum of payment_method This is my first question on stackoverflow so I try my best to not screw up. I am scratching my head on this query for a couple of hours and can't make any progress. I use a view which has several bookings in it. All those bookings have a payment_method e.g. paypal or creditcard. Customer Name | Price | payment_method John Doe | 20 | creditcard Susan Soe | 10 | paypal With my sql query I am trying to get the sums for all payment_methods. Unfortunately payment_methods which were not used this month don't show up. So I created an additional table called PaymentMethods with the column Methods. Methods creditcard premium sofort bank paypal I then tried to use a LEFT JOIN to get a result like this: LEFT JOIN payment_method | TotalQuantity (->sum(price)) creditcard | 20 premium | 0 sofort | 0 bank | 0 paypal | 10 But instead I only get those sums r...