table join help

[dreams4000]

Hey all,

I have two mysql tables one has events and the other has dates related to these events.(each date is a different row) So a relation would look like this

Table 1__________Table 2

Event A______ -(event a) Date 1
_________ -(event a) Date 2
____________ -(event a) Date 3

the first sql call is basically a

• select * from table_1

This grabs all events and then I loop through the output to show things like event names.

The second sql call is
-select * from table_2 where event_id = $event id

$event_id comes from the first sql call.
I then loop through all the dates that match each event id and show them. This way I get a table that looks like:

Event 1: Date 1 | Date 2 | Date 3 |

This all works fine, but the issue im running into is that I am trying to put sorting in. I have column headers which I am allowing the user to click on to sort and it switches between ASC/DESC and the database column name to sort by. Sorting works fine eg ORDER BY table_1.name ASC if it is all in one table, however I would like to allow sorting by each of the dates as well, but everytime I do a join on the two tables, the same event will loop the number of times there are dates.. eg 8 dates loops my event 8 times instead of looping once and allowing me to access all 8 dates. Any ideas?

[inerte]

That's probably because you are not telling the database what rows are supposed to be the same on the tables.

Put this on your WHERE clause:

WHERE table_1.id = table_2.event_id

[dreams4000]

$tmpquery = "SELECT * FROM events, event_dates WHERE events.id = event_dates.event_id ORDER BY $sorting_value LIMIT $from, $max_results";

ok, that is the sql I was using to hook the two together. What this does is loop through each event 8 times (each event has 8 dates) so I end up with:

Event 1: Date 1 | Date 2 | Date 3 |
Event 1: Date 1 | Date 2 | Date 3 |
Event 1: Date 1 | Date 2 | Date 3 |
Event 1: Date 1 | Date 2 | Date 3 |

Event 2: Date 1 | Date 2 | Date 3 |
Event 2: Date 1 | Date 2 | Date 3 |
Event 2: Date 1 | Date 2 | Date 3 |
Event 2: Date 1 | Date 2 | Date 3 |

instead of:

Event 1: Date 1 | Date 2 | Date 3 |
Event 2: Date 1 | Date 2 | Date 3 |

[konsu]

use "select distinct * from ..."

[newprogramer1]

Did you get this working correctly?

[NogDog]

Seeing as this is a more that 2-1/2 year old thread and the OP has not posted in about 1-1/2 years, don't hold your breath waiting for an answer. 😉 If you have a specific question in this area for which you need help, you'll probably be better off just opening a new thread (referencing this one if you think it will help explain your problem).