Mysql Help

[wayne]

I have been reading about left and right joins but cannot figure out how to do the following.

 

I have 2 tables to capture data about a swimming team entry:

 

 

TEAM TABLE

 

ID TeamName Category Department

1 Dogs male open Sales

2 Hosers male open IT

 

 

 

NAMES TABLE

 

ID TeamName Fname Lname

1 Dogs John Brown

2 Dogs Bob Smith

3 Hosers Davey Jones

4 Hosers Mike Smith

 

 

 

If I have either of the following statements:

SELECT *

From team left join names on team.TeamName = names.TeamName

 

or

 

SELECT *

FROM team,names

WHERE team.TeamName= names.TeamName

 

 

I get the following recored set

 

Team Category Dept Fname Lname

Dogs male open Sales John Brown

Dogs male open Sales Bob Smith

Hosers male open IT Davey Jones

Hosers male open IT Mike Smith

 

 

What I want is to list them with one team per row like this:

Team Category Dept Fname Lname Fname2 Lname2 ....

Dogs male open Sales John Brown Bob Smith

Hosers male open IT Davey Jones Mike Smith

 

I cannot figure out a select statement that would allow me to do this.

 

Any help, well you know, it would make my day.

 

Thanks all, Wayne

[TweezerMan]

I cannot figure out a select statement that would allow me to do this.

<{POST_SNAPBACK}>

To my knowledge, there isn't one. What you're asking for just doesn't mesh with how databases work.

 

If you really needed the results formatted in the way you describe, the application making the query (such as a PHP script) would need to do whatever transposing and reformatting of rows and columns that you require.

[borfast]

First of all, a word of advice: do not use the team name as a column for the names table, because, for example, if some day you need to change a team name, you will have to change it in every row of the names table that mentions it.

 

Instead, you should use the team id, which, even if you change the team name in the teams table, will always be the same. In this case your tables would look like this:

 

TEAM TABLE

ID TeamName Category Department

1 Dogs male open Sales

2 Hosers male open IT

 

NAMES TABLE

ID TeamName Fname Lname

1 1 John Brown

2 1 Bob Smith

3 2 Davey Jones

4 2 Mike Smith

 

As for the query, you can probably use the GROUP BY clause but it doesn't make any sense, because you're asking MySQL for all the fields in both tables, being that the NAMES table has more than one entry per each TEAM table entry, and you're telling MySQL to supress every result from the NAMES table except for one and return that one to you. MySQL can't decide which result to return, because you didn't give it any basis to decide on.

 

Do you really want the NAMES table results in there?

[wayne]

Thanks to both of you for your responses.

Good point re the team name field. I will make the change.

 

The reason I want each team in 1 row is because each team pays once and the event director can download as a csv file with one team per row. The last column of the row is the entry fee paid and all is nice and neat.

 

I also have a page where I list all entries along with their team names. Therefore I use the second table for this listing.

 

I will have to rethink how I am doing this.

 

Thanks again.

 

Wayne

[borfast]

Well, it seems to me you only need the team data, not the team names. If that's the case, and supposing the entry fee column is in the TEAM table, you can easily change that query into a much simpler one:

 

SELECT * FROM team

 

and there. MySQL will return you one row per team with all the information available.

 

This looks a bit too simple to be the answer but who knows...

[wayne]

Maybe I can approach this from another angle.

 

One table with the following fields:

ID

TeamName

Fname1

Lname1

Fname2

Lname2

Fname3

Lname3

Fname4

Lname4

Address

EntryFee

 

Note that teams can have 2-4 members so sometimes the 3rd and 4th name fields will be blank

 

I can then create the simple select statement to display each record in one row as I need.

 

However I like to display on a PHP page a list of all entries in a table with the columns:

Fname

Lname

TeamName

 

sorted by last name. This way people can see who else is registered and this also server as a confirmation of their registration.

 

I cannot figure out how to do this unless I create the second table. The problem with a second table is that if the event director wants to edit a name, then they have to do it twice (once in each table).

 

Wayne

[borfast]

Wayne, do not mix things into one table only. A team is a team, a team member is a completely different thing. You'll need a table for teams and a table for team members.

 

Create two tables with the following fields

 

TEAMS TABLE

TeamID TeamName Category Department

1 Dogs male open Sales

2 Hosers male open IT

 

MEMBERS TABLE

ID TeamID Fname Lname Address EntryFee

1 1 John Brown

2 1 Bob Smith

3 2 Davey Jones

4 2 Mike Smith

 

This way you can tell which team each member belongs to simply by storing the team ID in the members table.