Referencing a table twice in a row with 2 different ID numbers

[VoodooJai]

Hi all

I am creating a db that holds info about horse racing table 1 contains the details abaout the horses and their finish positions and if they were a favourite in the race. Table 2 describes the favourite ie "Not a fav" "fav" "Join fav" "2nd fav" "Joint 2nd Fav".

How could I display two different types of fav detail in the same row of a table.

 

tbl 1

ID int

Horse 1 varchar

horse1_odds varchar

horse1_fav int        (Foreign key to fav table)

 

Horse 2 varchar

horse2_odds varchar

horse2_fav int        (Foreign key to fav table)

 

Horse 3 varchar

horse3_odds varchar

horse3_fav int        (Foreign key to fav table)

 

table 2

ID int

Fav varchar (fav description)

 

Is it possible to reference table2 three times with different values in row 1 of table 1. A little help would be appreciated.

 

VoodooJai

[ManiacDan]

You can alias a single table however many times you like:

SELECT * 
FROM Table1 
JOIN Table2 AS alias1 ON table1.id1 = alias1.id
JOIN Table2 AS alias2 ON table1.id2 = alias2.id'
//etc.

-Dan

[VoodooJai]

You can alias a single table however many times you like:

SELECT * 
FROM Table1 
JOIN Table2 AS alias1 ON table1.id1 = alias1.id
JOIN Table2 AS alias2 ON table1.id2 = alias2.id'
//etc.

-Dan

 

Thanks i will try this I was nearly on the right track, thanks.

 

VoodooJai

[VoodooJai]

You can alias a single table however many times you like:

SELECT * 
FROM Table1 
JOIN Table2 AS alias1 ON table1.id1 = alias1.id
JOIN Table2 AS alias2 ON table1.id2 = alias2.id'
//etc.

-Dan

I have tried the solution but dont understand why it does not work and gives this result from this code

mysql> SELECT results_id, alias1.fav, alias2.fav, alias3.fav, alias4.fav, alias5.fav, alias6.fav
    -> FROM race_results
    -> LEFT JOIN favourites AS alias1 ON race_results. position_fav_1 = alias1.fav
    -> LEFT JOIN favourites AS alias2 ON race_results. position_fav_2 = alias2.fav
    -> LEFT JOIN favourites AS alias3 ON race_results. position_fav_3 = alias3.fav
    -> LEFT JOIN favourites AS alias4 ON race_results. position_fav_4 = alias4.fav
    -> LEFT JOIN favourites AS alias5 ON race_results. position_fav_5 = alias5.fav
    -> LEFT JOIN favourites AS alias6 ON race_results. position_fav_6 = alias6.fav;
+-------------+----------+ ----------+--------+--------+--------+---------+
| results_id     | fav         | fav           | fav      | fav      | fav       | fav        |
+-------------+----------+ ----------+--------+--------+--------+---------+
|          1        | 2nd-fav | 3rd-fav     | NULL    | NULL   | NULL    | NULL     |
|          2        | 2nd-fav | NULL        | NULL    | NULL    | NULL    | NULL    |
|          3        | 2nd-fav | NULL        | NULL    | NULL    | NULL    | NULL    |
|          4        | 2nd-fav | 3rd-fav     | NULL    | NULL    | NULL    | NULL    |
+-------------+----------+ ----------+--------+--------+--------+---------+

The NULL cells are supposed to show the type of favourite the horse is.

Can anyone enlighten or show me whats wrong with the query.

 

VoodooJai

[ManiacDan]

You don't need to join these tables at ALL if you already have all the values of favourites.fav in the race_results table. 

[VoodooJai]

You don't need to join these tables at ALL if you already have all the values of favourites.fav in the race_results table.

All values applicable in the results table have values in the favourites table.

So how would I actually do this query then, I just cannt see the wood for the trees.

Do I define an "AS" for each of the position_fav_# columns in the results table and display it for each of the horses.

Something like:

results.position_fav_1 = favourite.fav_id AS DisplayFav.

 

Thanks again VoodooJai

[ManiacDan]

One of us isn't understanding here...

 

You have:

 

    -> LEFT JOIN favourites AS alias2 ON race_results. position_fav_2 = alias2.fav

 

That implies that favourites.fav is the same as race_results.position_fav_2.

 

But you try to DISPLAY favourites.fav.

 

So just drop your joins entirely and display race_results.position_fav_2. 

 

See?  Your query doesn't need the joins or this other table AT ALL.

 

Unless you're trying to do something else that you haven't mentioned.

[mikosiko]

seems that you are looking for something like this (replace table and columns name for the real ones)

 

SELECT tbl1.horse1,
       tbl1.horse1_odds,
       b.fav  AS Horse1_fav,
       tbl1.horse2,
       tbl1.horse2_odds,
       c.fav  AS Horse2_fav,
       tbl1.horse3,
       tbl1.horse3_odds,
       d.fav  AS Horse3_fav
  FROM tbl1
    LEFT JOIN table2 AS b ON tbl1.horse1_fav = b.id
    LEFT JOIN table2 AS c ON tbl1.horse2_fav = c.id
    LEFT JOIN table2 AS d ON tbl1.horse3_fav = d.id

 

in a side note; you should normalize your tabla 1 to eliminate the repeating groups (like horse*_odds and horse*_fav) (1NF violation)... examples & some further simple explanation here:

http://www.troubleshooters.com/littstip/ltnorm.html

[VoodooJai]

One of us isn't understanding here...

 

You have:

 

    -> LEFT JOIN favourites AS alias2 ON race_results. position_fav_2 = alias2.fav

 

That implies that favourites.fav is the same as race_results.position_fav_2.

 

But you try to DISPLAY favourites.fav.

 

So just drop your joins entirely and display race_results.position_fav_2. 

 

See?  Your query doesn't need the joins or this other table AT ALL.

 

Unless you're trying to do something else that you haven't mentioned.

Hi again I am stil struggling with the query,

if I display "race_results.position_fav_2" it just gives me the row ID of table2 and not the value assigned to that ID.

I need to show 6 of these values from table2.

SELECT

race_results.race_date, race_results.meeting, race_results.`time`,

race_results.`number_1`, race_results.`number_2`, race_results.`number_3`, race_results.`number_4`, race_results.`number_5`, race_results.`number_6`,

race_results.horse_1, race_results.horse_2, race_results.horse_3, race_results.unplaced_4, race_results.unplaced_5, race_results.unplaced_6,

race_results.sp_numerator_1, race_results.sp_numerator_2, race_results.sp_numerator_3, race_results.sp_numerator_4, race_results.sp_numerator_5, race_results.sp_numerator_6,

race_results.sp_denominator_1, race_results.sp_denominator_2, race_results.sp_denominator_3, race_results.sp_denominator_4, race_results.sp_denominator_5, race_results.sp_denominator_6,

race_results.position_fav_1, race_results.position_fav_2, race_results.position_fav_3, race_results.position_fav_4, race_results.position_fav_5, race_results.position_fav_6,

race_results.number_runners,

meetings_details.location_name,

positions.position,

favourites.fav

FROM race_results

LEFT JOIN meetings_details

ON race_results.meeting = meetings_details.meeting_id

LEFT JOIN positions

ON race_results.meeting = positions.position_id

 

// This next bit shows the value and not the ID of table2

LEFT JOIN favourites

ON race_results.position_fav_1 = favourites.favourite_id

 

LEFT JOIN favourites AS alias1

ON race_results.position_fav_1 = alias1.fav

LEFT JOIN favourites AS alias2

ON race_results.position_fav_2 = alias2.fav

LEFT JOIN favourites AS alias3

ON race_results.position_fav_3 = alias3.fav

LEFT JOIN favourites AS alias4

ON race_results.position_fav_4 = alias4.fav

LEFT JOIN favourites AS alias5

ON race_results.position_fav_5 = alias5.fav

LEFT JOIN favourites AS alias6

ON race_results.position_fav_6 = alias6.fav

";

These bits of the code "alias6.fav" etc are the ones causing me problems.

 

Hope you can help thanks

 

VoodooJai

[ManiacDan]

Ok, I'm going to explain this once more and then I'm done:

 

You are trying to join race_results.position_fav_1 = alias1.fav.

 

Now, think very hard: Are race_results.position_fav_1 and alias1.fav ever equal to each other?

 

Ok, that's the problem.  You have repeatedly insisted that those values are both equal and different.  Are you trying to join on the wrong fields? 

 

Post the SHOW CREATE TABLE statements for both race_results and favourites.

 

The query you gave as not working joins 7 tables but only selects from one of them. 

[VoodooJai]

Many thanks for your patience I know it must be annoying for you all. I have been researching further with the info you supplied and I think I now have it working the way I wanted.

 

I really only as for help when Im stuck and need a little nudge in the right direction.

 

Thanks again I really appreciate the help.

 

VoodooJai