SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Collating data from three tables (part deux)

    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:
    Code:
    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 ;
    Code:
    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 ;
    Code:
    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:
    Code:
    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:
    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Feb 2011
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    add the code lines when you retrieve tables and place the distance in numerals after the lines code
    the tables will be separated

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    fyi, instead of this --
    Code:
    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 --
    Code:
    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



    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) --
    Code:
       AND venues.options LIKE '%" . $array['client_id'] . "%'
    ...
        ON clients_owners.owned_client_id = " . $this->db->escape($this->session->userdata('owner')) . ")
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •