#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,572
    Rep Power
    171

    What is the best approach to find the latest value added to multiple tables?


    Hello;

    In brief: There are 2 listings: Galleries and Events.
    They both have photos stored in seperate tables(gallery_photos, event_photos). Admin can add photos to both galleries and events and it inserts into the appropriate table (gallry_photos or events_photos).

    How can I get the name of a gallery OR event that has the newset photo added to it. Is it even possible?

    (Please note: I am not looking for 1 gallery and 1 event. I am looking the latest gallery or event.)

    Code:
    CREATE TABLE IF NOT EXISTS `sincity_event_photos` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `event_id` int(11) NOT NULL,
      `photo` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=97 ;
    Code:
    CREATE TABLE IF NOT EXISTS `sincity_events` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `recurring` varchar(255) NOT NULL,
      `title` varchar(255) NOT NULL,
      `about` text NOT NULL,
      `photo` varchar(255) NOT NULL,
      `musicians` text NOT NULL,
      `type` varchar(255) NOT NULL,
      `date` date NOT NULL,
      `display_date` varchar(255) NOT NULL,
      `price` int(11) NOT NULL,
      `recurring_day` varchar(255) NOT NULL,
      `thumbnail_photo` varchar(255) NOT NULL,
      `main_photo` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1859 ;
    Code:
    CREATE TABLE IF NOT EXISTS `sincity_galleries` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) NOT NULL,
      `about` text NOT NULL,
      `date_added` date NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;
    Code:
    CREATE TABLE IF NOT EXISTS `sincity_galleries_photos` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `gallery_id` int(11) NOT NULL,
      `photo` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Something like

    Code:
    select <columnsOfInterest>
      from (select <columnsOfInterest>  
              from sincity_galleries
             union all
            select <columnsOfInterest>
              from sincity_events) dt
     order by date_added desc
     limit 1
    The number of columns in the select list of the select queries in the union must be the same. Tthe type for each column in the first select must be compatible with the corresponding column in the second select list.

IMN logo majestic logo threadwatch logo seochat tools logo