Querying multple tables

[PHP_Idiot]

Hi Firstly thanks for even reading this, secondly I'm new to mysql so this might sem a dumb question to you pros out there.

 

I have 4 tables

Players

MembershipNo, FirstName, LastName, Town, Email

Venue

VenueID, VenueName, Address1, Address2, Town, County, Postcode, TelNo, FaxNo, Email, Web, GameNight1, GameTime1, GameNight2, GameTime2, ContactFirstName, ContactLastName, ContactMobile

Results

ResultID, VenueID, MembershipNo, Position

Position

Position, Points

 

The first Column Names in each above is the PrimaryKey.

 

What I need to do is create queries that will:

[*]Search all points earnt for a given MembershipNo

[*]View Finishing poisitions for all players (membershipNo, FirstName, LastName) for a given Venue on a given Date

[*]Search total Points earnt for all Players (creating a leader Board)

 

I understand how to do a basic Select to pull all or specific data from a table and sort it, but these cross tables, and although I have search for examples and found some I can't get any to actually work.

 

If anyone could show me how I wold be eternally grateful.

Many Thanks

[shlumph]

You'll need to look up sql JOIN, and that should help a lot 

[PHP_Idiot]

Hi shlumph,

I've been looking at both UNION and JOIN, but I think your right JOIN is the way to go,

I've never used it before and although I can get some success in joining 2 tables I'm struggling to join more than 2.

 

I'm especially having difficulty with creating a SUM(Points) to total a players points across a particular venue.

 

I would be really gratefull If you could give me an example based on my tables, it would help me see where I'm going wrong and also help me learn how to use it in other situations I need to solve.

(I know it's a big ask, but this is a giant leap outside my comfort zone in mysql!)

 

Many Thanks

[shlumph]

Sure- this is something that should push you in the right direction:

SELECT p.*, v.*, r.*, pos.* FROM Players p
LEFT JOIN Results r ON p.MembershipNo = r.MembershipNo
LEFT JOIN Venue v ON r.VenuID = v.VenueID
LEFT JOIN Position pos ON pos.Position = r.Position 
WHERE MembershipNo = 112

 

This will return all the results, venues, and position rows from whoever has membership number 112. Notice how I connect the tables with primary/foreign keys.

[PHP_Idiot]

Thats awesome thanks, it gives me a much clearer idea as to what I need to try to do, unfortunately you example throws up the following error:

#1052 - Column 'MembershipNo' in where clause is ambiguous

Is that because both collumn names are identical? I might try changing them fractionally to see if that helps.

 

Some home work for me to do tomorrow :-)

Thanks a million shlumph

[shlumph]

Whoops - since we named the Players table p, we need to do this:

WHERE p.MembershipNo = 112

 

That makes sense, right? Without the p., SQL has no idea what table MembershipNo belongs to.

[PHP_Idiot]

LOL

 

That makes perfect sense, but I changed the name, which also worked, however, I prefer the 'correct' method as you described so will change it back

 

My next job it to work out how to put a sum() in there as well!!

 

Thanks a lot for all you help, it's really appreciated.