Collating data from three tables (part deux)

Databases
1

Hi guys!

Despite having been helped with a similar problem a while ago, I’m stuck within spitting distance of solving this problem.

I’m trying to combine records from 3 tables.

  1. venues.
  2. venues_clients.
  3. venues_rates.

Firstly, I need to establish the relationship between user (owner) and the actual venue, via the tables venues and venues_clients, which is working.

Secondly, I need to extract the rates for each venue, from venues_rates, and this is where things are going wrong.

So, the tables are:

CREATE TABLE IF NOT EXISTS `venues` (
  `venue_id` mediumint(11) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `modification_date` timestamp NOT NULL default '0000-00-00 00:00:00',
  `client_id` int(11) NOT NULL,
  `name` varchar(256) NOT NULL,
  `telephone` varchar(32) NOT NULL,
  `fax` varchar(32) NOT NULL,
  `email` varchar(256) NOT NULL,
  `street` varchar(256) NOT NULL,
  `town_city` varchar(256) NOT NULL,
  `county` varchar(128) NOT NULL,
  `post_code` varchar(8) NOT NULL,
  `website` varchar(256) NOT NULL,
  `commission` int(4) NOT NULL,
  `notes` text NOT NULL,
  `options` text NOT NULL COMMENT 'Venues associated with which Clients',
  `mode` enum('public','private','closed') NOT NULL default 'public',
  PRIMARY KEY  (`venue_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `venues_clients` (
  `venue_client_id` int(11) NOT NULL auto_increment,
  `venue_id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL COMMENT 'Client using the Venue',
  PRIMARY KEY  (`venue_client_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Clients using Venues' AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `venues_rates` (
  `venue_rate_id` mediumint(11) NOT NULL auto_increment,
  `creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `venue_id` mediumint(11) NOT NULL,
  `venue_label` varchar(32) NOT NULL,
  `venue_rate` int(4) NOT NULL,
  PRIMARY KEY  (`venue_rate_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `venues_rates` (`venue_rate_id`, `creation_date`, `venue_id`, `venue_label`, `venue_rate`) VALUES
(1, '2011-06-28 14:50:05', 1, 'Basic Rate', 120),
(2, '2011-06-28 14:50:05', 2, 'Basic Rate', 95),
(3, '2011-06-28 14:50:26', 3, 'Basic Rate', 77),
(4, '2011-06-28 14:50:26', 4, 'Basic Rate', 100),
(5, '2011-06-28 15:44:26', 5, 'Basic Rate', 142),
(6, '2011-06-28 16:14:26', 5, 'Weekend Rate', 150),
(7, '2011-06-28 16:33:55', 5, 'Double Occupancy Rate', 190);

And the query I have is:

SELECT

	venues.venue_id AS id,
	DATE_FORMAT(venues.creation_date, '%W %M %Y') AS creation_date,
	venues.town_city AS name,
	GROUP_CONCAT(rates.rates) AS rates

FROM
	venues
INNER JOIN
	venues_clients
ON
	(venues.client_id = venues_clients.client_id)
AND
	(venues.options LIKE '%" . $array['client_id'] . "%')
INNER JOIN
	clients_owners
ON
	(clients_owners.owned_client_id = " . $this->db->escape($this->session->userdata('owner')) . ")
AND
	(venues.client_id = clients_owners.owner_client_id)
INNER JOIN (

	SELECT
		venues_rates.venue_rate_id AS rates_id,
		venues_rates.venue_rate AS rates,
		venues_rates.venue_id
	FROM
		venues_rates
	INNER JOIN
		venues
	ON
		(venues_rates.venue_id = venues.venue_id)
	GROUP BY venue_rate_id

) AS rates
ON
	(rates.venue_id = venues.venue_id)
GROUP BY id

The results I’m getting are:

Array
(
    [0] => Array
        (
            [id] => 1
            [creation_date] => Thursday May 2011
            [name] => Barnsley
            [rates] => 120,120,120,120,120
        )

    [1] => Array
        (
            [id] => 2
            [creation_date] => Monday June 2011
            [name] => Leeds
            [rates] => 95,95,95,95,95
        )

    [2] => Array
        (
            [id] => 3
            [creation_date] => Thursday May 2011
            [name] => Sheffield
            [rates] => 77,77,77,77,77
        )

    [3] => Array
        (
            [id] => 5
            [creation_date] => Tuesday June 2011
            [name] => London
            [rates] => 142,150,150,190,142,190,142,150,150,190,142,190,150,142,190
        )

)

As you can see, the grouped values for the rates should not be repeating, but I have no idea how to stop that from happening.

I’ve completely lost my way with this one, so any ideas would be much appreciated!

2

add the code lines when you retrieve tables and place the distance in numerals after the lines code
the tables will be separated

3

fyi, instead of this –


CREATE TABLE venues_clients 
( venue_client_id INTEGER NOT NULL AUTO_INCREMENT
, venue_id        INTEGER NOT NULL
, client_id       INTEGER NOT NULL 
, PRIMARY KEY (venue_client_id)
);

CREATE TABLE venues_rates 
( venue_rate_id MEDIUMINT NOT NULL AUTO_INCREMENT
, creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
, venue_id      MEDIUMINT) NOT NULL
, venue_label   VARCHAR(32) NOT NULL
, venue_rate    INTEGER NOT NULL
, PRIMARY KEY (venue_rate_id)
);

you should do it like this –


CREATE TABLE venues_clients 
( venue_id   INTEGER NOT NULL
, client_id  INTEGER NOT NULL 
, PRIMARY KEY ( venue_id , client_id )
);

CREATE TABLE venues_rates 
( venue_id      MEDIUMINT) NOT NULL
, venue_label   VARCHAR(32) NOT NULL
, venue_rate    INTEGER NOT NULL
, creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
, PRIMARY KEY ( venue_id , venue_label )
);

see the difference?

note that in the venues_rates table, you may want to store historical or future rates, in which case the creation date would be part of the primary key (and better called effective date)

if you make these changes, your tables will be more efficient, and i’m pretty sure you aren’t going to miss the columns i removed

:slight_smile:

as for your query, could you please explain these two values, where they come from, and why there are two variables for the query (usually there’s only one) –


   AND venues.options LIKE '%" . [COLOR="red"]$array['client_id'][/COLOR] . "%'
...
    ON clients_owners.owned_client_id = " . [COLOR="Red"]$this->db->escape($this->session->userdata('owner'))[/COLOR] . ")