lornevs
Member
I am (re)building a Collectors Show listing application using 4 tables, each in a one-to-many relationship. There's promoter_info, show_info (basically the names of shows they promote), venue_info and finally event_info has details of specific events. All the lists are connected with databasejoin elements. To show all the info together I joined them cascading backwards from event_info so the most important info (to the viewer) shows first. (I did it all in the events_info list, maybe that's what I did wrong?). I used a LEFT JOIN from events to venues, then from venues to shows (all repeatable) and it works fine but when I add a LEFT JOIN from shows to promoters and try to look at the data I get the error "Unknown column 'vib3_fabrik_show_info.promoter_id' in 'on clause'". It then goes on to list the distinct rows it did find which I won't list here as I think the issue is with the joins, (in my original post on the Community forum I stated I used an INNER JOIN, but that was only because the LEFT JOIN didn't work, both get the same result). The join part of the error shows as:
FROM `vib3_fabrik_event_info`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info` ON `vib3_fabrik_venue_info`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info_0` ON `vib3_fabrik_venue_info_0`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info_0` ON `vib3_fabrik_show_info_0`.`id` = `vib3_fabrik_venue_info`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info_0` ON `vib3_fabrik_promoter_info_0`.`id` = `vib3_fabrik_show_info`.`promoter_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info` ON `vib3_fabrik_show_info`.`id` = `vib3_fabrik_venue_info_0`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info` ON `vib3_fabrik_promoter_info`.`id` = `vib3_fabrik_show_info_0`.`promoter_id`
LEFT JOIN `vib3_users` AS `vib3_users` ON `vib3_users`.`id` = `vib3_fabrik_promoter_info_0`.`user`
WHERE ( vib3_fabrik_event_info.event_show_until_date >= '2014-12-31 00:00:00' )
ORDER BY `vib3_fabrik_event_info`.`event_start_date` ASC LIMIT 0, 100
This is a very small database, in the past I put the information in myself using a single table. I want the promoters to input their own show dates so I need a form that is user-friendly and does not require them to input the same info over and over again. I know using one table is bad design so I normalized the data I already have and split it into four tables, seemed like a good idea at the time.
I am using Fabrik 3.1rc2 on a J!3.2.2 site (migrated from 2.5 - don't let them tell you it's easy). The page can be seen at http://www.vibrations.ca/vibe3/record-shows/2014-shows. It's a test site, I've left the four tables joined so you can see the error. With three tables joined it works fine but you can't see the promoter contact info.
Can anybody help?
FROM `vib3_fabrik_event_info`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info` ON `vib3_fabrik_venue_info`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_venue_info` AS `vib3_fabrik_venue_info_0` ON `vib3_fabrik_venue_info_0`.`id` = `vib3_fabrik_event_info`.`venue_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info_0` ON `vib3_fabrik_show_info_0`.`id` = `vib3_fabrik_venue_info`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info_0` ON `vib3_fabrik_promoter_info_0`.`id` = `vib3_fabrik_show_info`.`promoter_id`
LEFT JOIN `vib3_fabrik_show_info` AS `vib3_fabrik_show_info` ON `vib3_fabrik_show_info`.`id` = `vib3_fabrik_venue_info_0`.`show_id`
LEFT JOIN `vib3_fabrik_promoter_info` AS `vib3_fabrik_promoter_info` ON `vib3_fabrik_promoter_info`.`id` = `vib3_fabrik_show_info_0`.`promoter_id`
LEFT JOIN `vib3_users` AS `vib3_users` ON `vib3_users`.`id` = `vib3_fabrik_promoter_info_0`.`user`
WHERE ( vib3_fabrik_event_info.event_show_until_date >= '2014-12-31 00:00:00' )
ORDER BY `vib3_fabrik_event_info`.`event_start_date` ASC LIMIT 0, 100
This is a very small database, in the past I put the information in myself using a single table. I want the promoters to input their own show dates so I need a form that is user-friendly and does not require them to input the same info over and over again. I know using one table is bad design so I normalized the data I already have and split it into four tables, seemed like a good idea at the time.
I am using Fabrik 3.1rc2 on a J!3.2.2 site (migrated from 2.5 - don't let them tell you it's easy). The page can be seen at http://www.vibrations.ca/vibe3/record-shows/2014-shows. It's a test site, I've left the four tables joined so you can see the error. With three tables joined it works fine but you can't see the promoter contact info.
Can anybody help?