Help creating recordset

[barney0o0]

Hi folks...sorry its been i while since ive done a  database...so please be patient!!

 

Tables are;

 

events

eventsdates - will contain various dates and times of an event, the eventdates.eventid is primary and refers to events.id via foreign key

producer

eventproducer- primarykeys being eventid and producerid, both are foreign keys and refer to event and producer tables.

 

What i cant do is pull them all together to form a recordset, so that i can retrieve relative information on one event, such as all the info in its table, plus the date/s, and producer/s.....everything i try turns out to be complete nonsense!

 

Notes:Each event can have one or more producer. Therefore, the producer contains all the info whereas the eventproducer table will bring it all together. Its all separate as producer/s vary between 1 - 5 . So for example an event with the id of 1 will be listed in the 'eventproducer.eventid' x times and the 'producerid' will be linked and pull together the full info of the producer table.

 

The table structures as as follows;

 

CREATE TABLE `eventdates` (\n  `eventid` int(11) NOT NULL,
  `eventdate` datetime NOT NULL,
`price` decimal(5,2) NOT NULL,
PRIMARY KEY  (`eventid`,`eventdate`),
CONSTRAINT `eventdates_ibfk_1` FOREIGN KEY (`eventid`) REFERENCES `events` (`id`)
ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `events` (\n  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
`imageS` varchar(200) default NULL,
`imageL` varchar(200) default NULL,
`cat` varchar(50) NOT NULL,\n  `cheapchips` varchar(20) default NULL,
  `text` text NOT NULL,
`reco1` varchar(50) default NULL,
`reco2` varchar(50) default NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `producer` (\n  `producerID` int(11) NOT NULL auto_increment,
  `company` varchar(50) NOT NULL,
  `text` text NOT NULL,\n  `weblink1` varchar(50) NOT NULL,
  `weblink2` varchar(50) NOT NULL,
  `contactnm` varchar(50) NOT NULL,
  `contacttel` varchar(50) NOT NULL,
  PRIMARY KEY  (`producerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE eventproducers
( eventid INTEGER NOT NULL
, producerid INTEGER NOT NULL
, PRIMARY KEY ( eventid , producerid )
, FOREIGN KEY ( eventid ) REFERENCES events ( id )
, FOREIGN KEY ( producerid ) REFERENCES producers ( producerid )
) ENGINE=InnoDB

 

i hope that someone can help as im loosing the will to live.......similary i hope you can understand what i want to do!

 

Thanks in advance

 

[Barand]

This should help

 

http://www.w3schools.com/sql/sql_join.asp

[barney0o0]

Thanks Barand

 

Ive had a look before, but im a right mess with trying to bring togther the dates for the particular event.

 

What ive got at the moment is;

 

SELECT events.id, events.title, events.imageS, events.imageL, events.cat, 
events.cheapchips, events.text, events.reco1, events.reco2, 
eventproducers.eventid, eventproducers.producerid,  eventdates.eventid, 
eventdates.eventdate, eventdates.price

FROM events LEFT JOIN eventproducers ON  eventproducers.eventid = events.id  
LEFT JOIN eventdates ON eventdates.eventid = events.id

 

 

....am i completly going in the wrong direction?

 

Many thanks

[Barand]

No point in selecting the event id from eventdates and eventproducers, they will be same value as events.id.

 

Normal join to get matching records is an INNER JOIN. Use left join if you want events with no dates or no producers

 

Note when joining a structure like this - if an event has 3 dates and 2 producers you get 6 rows returned ( as with event 1 in my sample data below)

SELECT e.id as Event, e.title,
      ed.eventdate, ed.price,
      p.company
FROM events e
      INNER JOIN eventdates ed ON ed.eventid = e.id
      INNER JOIN eventproducers ep ON e.id = ep.eventid
      INNER JOIN producer p ON ep.producerid = p.producerID
ORDER BY e.title, ed.eventdate

->

[pre]

1, 'County show', '2007-09-06 00:00:00', '15.00', 'Production Co B'

1, 'County show', '2007-09-06 00:00:00', '15.00', 'Production Co A'

1, 'County show', '2007-09-07 00:00:00', '12.00', 'Production Co B'

1, 'County show', '2007-09-07 00:00:00', '12.00', 'Production Co A'

3, 'Jazz Fest',  '2007-09-20 00:00:00', '10.00', 'Production Co B'

2, 'Pop concert', '2007-09-13 00:00:00', ' 5.00', 'Production Co A'

[barney0o0]

Thanks Barand

 

Your example makes some sence...(believe me i looked as some scripts that i cant make head nor tail of!)

 

Im a little unsure how you form the 'e', 'p' values..and later refre to the 'ep'....sorry, could you give me a little explanation so that i can understand fully

 

many thanks for your help

[Barand]

e and ed are aliases for the events and eventdates tables

 

SELECT e.id as Event, e.title,

      ed.eventdate, ed.price ...

FROM events e

      INNER JOIN eventdates ed ON ed.eventid = e.id ....

 

Sometimes they are necessary, in this case it's just easier than repeating the tablenames