mysql join help

tazgalsinh

JC_horse

HorseName

HorseStableName

HorseAge

HorseHeight

TrainerName

PRIMARY KEY(HorseName)

JC_horseowner
CourseName

MeetingDate

MeetingStartTime

RaceName

RaceTime

HorseName

OwnerName

PRIMARY KEY(CourseName, MeetingDate, MeetingStartTime, RaceName, RaceTime, HorseName, OwnerName)

I have the above two tables in my database. I need a facility where an owner can change the trainer of a horse he/she own's. In simple terms it firstly will be a drag down menu with all the owners names, then the next screen will display all the horses they own and the trainers for that horse.

As you can see the data is in two different tables. I need to do a join but I am having lot of trouble getting my head around it. I came up with the following but it still doesn't work

select h.HorseName, t.TrainerName
from JC_horseowner h
left join JC_horse t on h.HorseName = t.HorseName
where h.OwnerName='Lucinda V. Russell'

If any one could give me any help it would be much appreciated. As i cannot get anywhere with joins. thanks.

leatherback

hm.. hate to say it,but our whole database design seemsflawed to me:

You store horsename in two different tables (Store the name in a table horses, and refer to the horse ID in other tables)
You store the age of a horse (Every moment the horse gets older. Better would be to stored the date of birth, and calculate the age whenever you retrieve data from database)
You store meetings with owner details (These are two distinct propertiues, and should be stored in separate tables)

What I think you would be looking at from a database optimization point of view is a table with

Horse_details
- id
- Date_of_birth (Age changes all the time!)
- stable_nr
- Trainer_nr
- Owner_nr

Stables
- ID
- Name
- Location

People
- id
- Name
- Type (owner, trainer, ...)

Races
- Id
- Location
- Time

Horses_to_races
- horse_nr
- Race_nr

You do not need to make all the second table names.

You could just


select HorseName, TrainerName
from JC_horseowner
left join JC_horse on HorseName = HorseName
where OwnerName='Lucinda V. Russell'

However, you might want to consider using only non-caps in your field names, as it is easy to miss caps-issues.

tazgalsinh

hi, thanks for the reply. I would love to change the database as I myself have found a few errors with it but this is at this time out of the question.. the tables i Have shown you are just a couple from a list of 10.

I have tried the query you have given me at the bottom but it doesn;t seem to give me the results I need.

leatherback

tazgalsinh wrote:
I have tried the query you have given me at the bottom but it doesn;t seem to give me the results I need.

That doesn't give us much to help you solve the problem...

What does it give?
What are you expecting to get?
What errors are you getting, if you do not get any results?

tomhath

The original query looks like it should work. If it isn't giving the results you expect I suggest looking at the data in the two tables to be sure what you expect is really what you should be getting.

tazgalsinh

yeah the data seems to be ok in tables.

here some structure detail if anyone has time to set it up.

CREATE TABLE JC_racehorses (
CourseName CHAR(30) NOT NULL,
MeetingDate DATE NOT NULL,
MeetingStartTime TIME NOT NULL,
RaceName CHAR(45) NOT NULL,
RaceTime TIME NOT NULL,
HorseName CHAR(20) NOT NULL,
RaceHandicap TINYINT UNSIGNED,
JockeyName CHAR(25),
RaceStartPrice CHAR(5),
RaceAmountBet DECIMAL(10,2),
FinishPosition CHAR(2),
PRIMARY KEY(CourseName, MeetingDate, MeetingStartTime, RaceName, RaceTime, HorseName)
);

INSERT INTO JC_racehorses VALUES
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'DREAM CATCHER', 102,'James Doyle', '50/1', 1687432, '03'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'CONSONANT', 102, 'D. Nolan', '16/1', 2589160,'08'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'GLENBUCK', 100, 'Frances Norton', '20/1', 856700, '05'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'REGENT''S SECRET', 100, 'Daniel Tudhope', '16/1', 992364,'02'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'MAGGIES FARM', 93, 'Joe Fanning', '20/1', 2100753,'04'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'KILDARE SUN', 99, 'Dale Gibson', '8/1', 5506980, '01'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'ECCOLLO', 98, 'John Egan', '11/8', 1548627,'07'),
('Wolverhampton', '2006-12-09', '06:45', 'THE IBETX.COM HANDICAP', '21:00',
'SPAINNASH', 92, 'Tony Culhane', '66/1', 968526, '06'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'SIENA STAR', 88, 'Micky Fenton', '8/1', 856734, '02'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'FIRST FRIEND', 87, 'George Baker', '10/1', 945998, '03'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'COOL SANDS', 84, 'Patrick Mathers', '33/1', 1765298, '10'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'EKTISHAAF', 84, 'Nelson De Souza', '16/1', 1978456, '07'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'BORDER EDGE', 83, 'James Millman', '10/1', 789345, '04'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'JORDAN''S LIGHT', 82, 'John Egan', '11/8', 2008584, '01'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'BLUE QUIVER', 81, 'Paul Eddery', '20/1', 1736789, '09'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'PAGANO', 75, 'Adam Kirby', '20/1', 972522, '05'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'BEAUTIFUL MOVER', 56, 'Natalia Gemelova', '50/1', 1450285, '06'),
('Kempton Park', '2006-12-09', '03:30', 'THE PANORAMIC BAR & RESTAURANT HANDICAP','15:50',
'DANELOR', 54, 'Dane O''Neill', '25/1', 2587677, '08'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'HUGO DE GREZ', 119, 'Paul Eddery', '5/1',3867294, '02'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'ABZUSON', 118, 'John Egan', '10/1', 3765976, '04'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'MAGGIES FARM', 93, 'Joe Fanning', '8/1', 2394847, '03'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'PAGANO', 75, 'Adam Kirby', '15/1', 1828228, '05'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'DANELOR', 54, 'Dane O''Neill', '20/1', 998225, 'NS'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'SPAINNASH', 94, 'Tony Culhane', '50/1', 135357, '06'),
('Newcastle', '2006-12-09', '12:00', 'FREECLAIM IDC HANDICAP CHASE', '12:20',
'DREAM CATCHER', 103,'James Doyle', '8/5', 1002885, '01'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'KILDARE SUN', 99, 'Dale Gibson', '5/1', 887446, '01'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'COOL SANDS', 84, 'Patrick Mathers', '30/1', 1025345, '06'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'BORDER EDGE', 83, 'James Millman', 'evens', 2984477, '02'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'MAGGIES FARM', 93, 'Joe Fanning', '25/1', 1500395, '05'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'SPAINNASH', 90, 'Tony Culhane', '12/1', 1336449, '03'),
('Lingfield Park', '2006-12-06', '12:00', 'THE lingfieldpark.co.uk MAIDEN AUCTION STAKES', '12:25',
'FIRST FRIEND', 86, 'George Baker', ' ', 999222, '04'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'BAY ISLAND', 122, 'Robert Lucey-Butler', '14/2', 663558, 'NF'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'WHAT''SONYOURMIND', 116, 'J. W. Farrelly', '14/1', 1255792, '04'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'HERECOMESTANLEY', 117, 'Dave Crosse', '10/1', 1250936, '03'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'HUGO DE GREZ', 119, 'Paul Eddery', '20/1', 850337, '05'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'DREAM CATCHER', 102,'James Doyle', 'evens', 2776554, '01'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'KILDARE SUN', 99, 'Dale Gibson', '5/1', 2447658, '02'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'BLUE QUIVER', 81, 'Paul Eddery', '', 0, 'NS'),
('Towcester', '2006-12-09', '11:45', 'JOHN GRONOW IS 60 TODAY HANDICAP CHASE', '13:45',
'CONSONANT', 102, 'D. Nolan', '25/1', 1288003,'06'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'SPAINNASH', 110, 'Tony Culhane', '50/1', 1450664, '04'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'KILDARE SUN', 105, 'Dale Gibson', '10/1', 1200600, '05'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'BORDER EDGE', 83, 'James Millman', '20/1', 1450775, 'NF'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'DREAM CATCHER', 115,'James Doyle', 'evens', 2600339, '02'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'BEAUTIFUL MOVER', 68, 'Natalia Gemelova', '30/1', 1500700, '06'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'ECCOLLO', 98, 'John Egan', '11/8', 1990850,'07'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'MAGGIES FARM', 97, 'Joe Fanning', '15/1', 980500,'03'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'PAGANO', 80, 'Adam Kirby', '20/1', 1600542, '08'),
('Newbury', '2006-11-18', '12:00', 'HENNESSY COGNAC GOLD CUP CHASE', '14:40',
'WHAT''SONYOURMIND', 110, 'J. W. Farrelly', '4/6', 1200600, '01');

CREATE TABLE JC_horse (
HorseName CHAR(20) NOT NULL,
HorseStableName VARCHAR(255),
HorseAge TINYINT UNSIGNED,
HorseHeight DECIMAL(3,1),
TrainerName CHAR(20),
PRIMARY KEY(HorseName)
);

INSERT INTO JC_horse VALUES
('DREAM CATCHER','DREAMY',3,16.2,'R. A. Kvisla'),
('CONSONANT','LETTER',9,15.0,'D. G. Bridgwater'),
('GLENBUCK', 'BUCK', 3,16.1, 'A. Bailey'),
('REGENT''S SECRET', 'GENT', 6,16.0,'J. S. Goldie'),
('MAGGIES FARM','MAGS',3,15.0,'M. Johnston'),
('SPAINNASH', 'NASH', 6,15.2,'J. J. Lambe'),
('PRETTILINI', 'PRETTY', 3,15.1,'R. Brotherton'),
('WHISTLEUPTHEWIND', 'WINDY', 3,15.3,'J. M. P. Eustace'),
('MISKINA', 'MISS', 5,14.2,'W. M. Brisbourne'),
('KILDARE SUN', 'SUNNY', 4, 15.3, 'R. Brotherton'),
('ECCOLLO', 'COLLY', 4, 16.0, 'R. A. Kvisla'),
('SIENA STAR', 'STAR', 5, 15.2, 'M. Johnston'),
('FIRST FRIEND', 'PALLY', 6, 16.2, 'J. J. Lambe'),
('COOL SANDS', 'SANDY', 6, 14.3, 'J. S. Goldie'),
('EKTISHAAF', 'SHAAF', 6, 15.0, 'J. M. P. Eustace'),
('BORDER EDGE', 'BLAZE', 6, 15.1, 'J. M. P. Eustace'),
('JORDAN''S LIGHT', 'JORDY', 8, 16.1, 'R. A. Kvisla'),
('BLUE QUIVER', 'BLUE', 8, 16.2, 'G. G. Margarson'),
('PAGANO', 'PAGE', 8, 15.2, 'D. Haydn Jones'),
('BEAUTIFUL MOVER', 'BEAUTY', 8, 15.3, 'J. J. Lambe'),
('DANELOR', 'DANNY', 4, 14.2, 'P. A. Blockley'),
('HUGO DE GREZ', 'HUGO', 4, 15.0, 'D. Haydn Jones'),
('ABZUSON', 'ABBY', 4, 15.2, 'R. Brotherton'),
('BAY ISLAND', 'BAY', 5, 16.0, 'R. A. Kvisla'),
('WHAT''SONYOURMIND', 'SONNY', 5, 16.0, 'P. A. Blockley'),
('HERECOMESTANLEY', 'STAN', 5, 16.1, 'J. S. Goldie');

leatherback

My suggestion had a silly mistake (I use unique colum names in my applications, and never need to use the table names)

select HorseName, TrainerName
from JC_horseowner
left join JC_horse on JC_horseowner.HorseName = JC_horse.HorseName
where OwnerName='Lucinda V. Russell'

tazgalsinh

hi, i tired you suggestion but still get duplicate entrys.

I get

HorseName TrainerName
kildare Sun R.Botherton
Dream catcher R.A. Kvisla
kildare Sun R.Botherton
Dream catcher R.A. Kvisla
kildare Sun R.Botherton
Dream catcher R.A. Kvisla
kildare Sun R.Botherton
Dream catcher R.A. Kvisla

I should just get
HorseName TrainerName
kildare Sun R.Botherton
Dream catcher R.A. Kvisla

this would then let me change the trainer for the horse at anytime.

leatherback

And that is exactly why people ask you WHAT you get and what you EXPECT.

Use the distinct keyword.

select HorseName, distinct TrainerName
from JC_horseowner
left join JC_horse on JC_horseowner.HorseName = JC_horse.HorseName
where OwnerName='Lucinda V. Russell'

tazgalsinh

thanks for the reply, so now when I run this query then change the trainer name of a horse it will be ok?

tomhath

I think the way your tables are set up you will have lots of other problems.

Each row in JC_horseowner has the horse and owner on the date of the race. What if the horse is sold? What owner(s) will see the horse and trainer in their list? How will you know who was the owner on the day of the race?