mysql Wraping my head around > left join

[eluser]srpurdy[/eluser]
Hi guys,

I'm currently messing around with left join, and well I'm not normally good at sql stuff, so maybe someone can help me wrap my around it so I understand it better.

I'll start off explaining what I am trying to do. I know the function I am working on is a bit complex so bare with me, it can get a bit confusing

This I am doing for a racing results and statistic database.

Basic SQL Structure
-------------------
Series - Mostly Complete
->Events - Mostly Complete
->Results File Information (CSV) - Complete
->Results Data - Complete
->Points System - In Development (partly working)
->Championship Standing - Barely started

So basically

Series - is like for example (Formula One, Indycar so on)

Events - is all the different races in that specific series.

Results Data - is the actual results information which is inserted into the database via the results file upload system.

Points System - Is the series specific points structure per finishing position.

Championship Standing - Points standing in order of most points to lowest only counting events that are specific to a specific series. Including stats like Starts, Poles, Wins, LapsLed, FastestLaps so on (this is where my head explodes)

I got for example this piece of code to load the results information

Code:

//This function displays the specifically requested results of a race.
function results_section()
{
$results_section = $this->db
        ->where('series.id = events.series_id')
        ->where('events.id = results_data.event_id')
        ->where('points.series_id = series.id')
        ->select('
        series.id as series_id,
        events.id as events_id,
        results_data.event_id,
        results_data.Fin_Pos,
        results_data.Car,
        results_data.Driver,
        results_data.Start_Pos,
        results_data.CarNumber,
        results_data.Out,
        results_data.Interval,
        results_data.LapsLed,
        results_data.AvgLap,
        results_data.FastLap,
        results_data.FLNumber,
        results_data.LapsComp,
        results_data.Inc,
        points.fin_position,
        points.fin_points
        ')
        ->from('
            series,
            events,
            results_data')
        ->join('points', 'points.fin_position = results_data.Fin_Pos', 'left')
     ->get();

return $results_section;
}

This comes up with a tabled layout of a race result in order of finishing position.

I'm trying to wrap my head around how I can turn a function like that into something that can grab all the specific events that are apart of a specific series add the points together as well as any other stats like wins poles so on. This is where I am stuck lol.

Or would it be easier to save that data when a result is inserted into the database? Which way would be more efficient?

Thanks for any insight anyone can provide.
Merry Christmas to all.
Shawn

[eluser]ggoforth[/eluser]
I'm still a little confused as to what your trying to accomplish, but I can explain how a left join works, and when you would use it. Lets say you had two tables, "parents" and "kids".

Every record in the parents table would represent one person, and every record in the kids table would represent one kid that's tied to a parent through the parent id. Lets say you wanted one query that selected a parent and their kids. So you write a query like:

SELECT * FROM parents JOIN kids ON parents.parent_id = kids.parent_id WHERE parents.parent_id = 1;

This would select a parent and their kids. The only problem is, what if this parent didn't have any kids (for some reason, I know they wouldn't be a parent if they didn't have kids, but just roll with me . This query would not return any results, because there were no kids to join with the parent.

So, if you want to join two or more tables, but your not sure that the field your joining on exists in your other tables, you use left join. This in a round about way says, join these tables, and return the result, even if there is nothing to join with. We could rewrite the query above as such:

SELECT * FROM parents LEFT JOIN kids ON parents.parent_id = kids.parent_id WHERE parents.parent_id = 1;

So basically, give me everything from the table on the left (parents) even if it can not join on anything in the kids table.

I hope I didn't over complicate this, but that's basically how the LEFT JOIN works.

Greg

[eluser]srpurdy[/eluser]
[quote author="ggoforth" date="1261796255"]I'm still a little confused as to what your trying to accomplish, but I can explain how a left join works, and when you would use it. Lets say you had two tables, "parents" and "kids".

Every record in the parents table would represent one person, and every record in the kids table would represent one kid that's tied to a parent through the parent id. Lets say you wanted one query that selected a parent and their kids. So you write a query like:

SELECT * FROM parents JOIN kids ON parents.parent_id = kids.parent_id WHERE parents.parent_id = 1;

This would select a parent and their kids. The only problem is, what if this parent didn't have any kids (for some reason, I know they wouldn't be a parent if they didn't have kids, but just roll with me . This query would not return any results, because there were no kids to join with the parent.

So, if you want to join two or more tables, but your not sure that the field your joining on exists in your other tables, you use left join. This in a round about way says, join these tables, and return the result, even if there is nothing to join with. We could rewrite the query above as such:

SELECT * FROM parents LEFT JOIN kids ON parents.parent_id = kids.parent_id WHERE parents.parent_id = 1;

So basically, give me everything from the table on the left (parents) even if it can not join on anything in the kids table.

I hope I didn't over complicate this, but that's basically how the LEFT JOIN works.

Greg[/quote]

Hi Greg,

Thanks for your reply. Way you explained it was good.

Using your above example I guess what I'm trying to do is the following.
Championship_Standing - Parent
- Series - Child of Standings
- Events - Child of Series
- Results_data - Child of Events
- Points Table - Child of Results_data
Its a big family lol

The key field in this case would be series_id that links them all together. In the case of Championship standings it needs to figure out what events are a part of whatever series and calculate the points from each event in a nutshell.

There could be 100 series, with thousands of events. but one series could have say 10 events. So the Championship Standings should display the totals based on those 10 events only.

Hope thats a little more clear. But your description of left join has helped me a bit. Hopefully I can come up with something in the meantime lol.

Shawn

[eluser]vickel[/eluser]
to create complex queries, I use to make a copy of my SQL database in MS access, construct and test the query there and then "translate" it to my CI model