CODEWITHSUNDEEP
mysqljoin
John MacKenzie
John MacKenzie

Reputation: 65

Performing joins

So this my first run into mysql databases,

I got a lot of help from my first question : MYSQL - First Database Structure help Please

and built my database as pitchinnate recommended

I have :

Table structure for table club

Column      Type            Null    Default
id          int(11)         No  
clubname    varchar(100)    No  
address     longtext        No  
phone       varchar(12)     No  
website     varchar(255)    No  
email       varchar(100)    No

Table structure for table club_county

Column      Type    Null    Default
club_id     int(11) No  
county_id   int(11) No

Table structure for table county

Column      Type        Null    Default
id          int(11)     No  
state_id    tinyint(4)  No  
name        varchar(50) No

Table structure for table states

Column      Type        Null    Default
id          tinyint(4)  No  
longstate   varchar(20) No  
shortstate  char(2)     No

I set up foreign key relationships for everything above that looks that way.... states.id -> county.state_id for example

What I tried to run :

SELECT *
FROM club
JOIN states
JOIN county
  ON county.state_id=states.id
JOIN club_county
  ON club_county.club_id=club.id
club_county.county_id=county.id

This didn't work... I'm sure the reason is obvious to those of you who know what SHOULD be done.

What I'm trying to do is

get a listing of all clubs, with their associated state and county(ies)

Upvotes: 1

Views: 91

Answers (3)

ChrisC
ChrisC

Reputation: 2469

You need to specify a JOIN condition for each of your joins. It should look something like the following:

SELECT *
FROM club
JOIN club_county ON club.id = club_county.club_id
JOIN county ON club_county.county_id = county.id
JOIN states ON county.state_id = state.id

Your version omitted an ON clause on the line that reads JOIN states.

One thing regarding your table names: It's advisable to stick to either singular or plural table names and not to mix them (notice you have club (singular) and states (plural) tables). This makes things easier to remember when you're developing and you're less likely to make mistakes.

EDIT:

If you want to limit which columns appear in your result, you just need to modify the SELECT clause. Instead if "SELECT *", you comma separate just the fields you want.

E.g.

SELECT club.id, club.name, county.name, states.name
FROM club
JOIN club_county ON club.id = club_county.club_id
JOIN county ON club_county.county_id = county.id
JOIN states ON county.state_id = state.id

Upvotes: 1

SSC
SSC

Reputation: 3008

So after you have modified the question, now the answer would be more like:

SELECT club.id,club.clubname,county.name,states.longstate,states.shortstate 
FROM club,club_county,county,states
WHERE club.id=club_county.club_id
AND county.id=club_county.county_id
AND states.id = county.state_id

Please let me know if you need more help...

Thanks... Mr.777

Upvotes: 1

SSC
SSC

Reputation: 3008

The query you have written will not even execute as it has syntax error. Please see this link for more details on JOINS: 13.2.8.2. JOIN Syntax

Also, `

SELECT * 
FROM club a, county b, states c, club_county d 
WHERE a.id = d.county_id
AND b.id = d.county_id
AND b.state_id = c.id

`

I hope this will help... If you still need help, please let us know...

Thanks... Mr.777

Upvotes: 1

Related Questions