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

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Count Left Outer Joint


    Hi;

    I have 2 quesitons about this situation.

    1 - I want to count the number of photos per ad. Is this query well written and correct by MySQL rules?
    mysql Code:
     SELECT ad_have.id,
           members.email,
           Count(ad_have_photo.photo) AS PHOTOS
    FROM   ad_have
           LEFT OUTER JOIN ad_have_photo
                        ON ad_have.id = ad_have_photo.ad_id
           INNER JOIN members
                   ON members.id = ad_have.member_id
    WHERE  ad_have.active_admin = 'y'
           AND ad_have.active = 'y'
           AND ad_have.date_added > Now() - INTERVAL 50 day
    GROUP  BY ad_have.id
    ORDER  BY photos DESC
    The reason I ask is that I don't understand this quote
    So the first rule for using the GROUP BY clause is that the result set can contain only columns specified in the GROUP BY clause, or aggregate functions, or any combinations of these.

    2 - How can I show "only the ads that do not have any photo"? I can't say WHERE PHOTOS > 0,,,,what can I say instead?

    Thank you




    Code:
    CREATE TABLE `ad_have` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `emailed_to_wants` int(11) NOT NULL DEFAULT '0',
     `place_name2` varchar(255) NOT NULL,
     `member_ID` int(11) NOT NULL,
     `date_added` date NOT NULL,
     `comments` text NOT NULL,
     `weekly_rent` int(255) NOT NULL,
     `sex` varchar(255) CHARACTER SET latin1 NOT NULL,
     `smoke` varchar(255) CHARACTER SET latin1 NOT NULL,
     `available_date` date NOT NULL,
     `suburb` int(11) NOT NULL,
     `building_type` varchar(255) CHARACTER SET latin1 NOT NULL,
     `pet` varchar(255) CHARACTER SET latin1 NOT NULL,
     `active` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT 'y',
     `title` varchar(255) CHARACTER SET latin1 NOT NULL,
     `views` int(11) NOT NULL DEFAULT '0',
     `active_admin` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT 'n',
     `security_bond` varchar(255) NOT NULL,
     `furnished` varchar(255) CHARACTER SET latin1 NOT NULL,
     `washer_dryer` varchar(255) CHARACTER SET latin1 NOT NULL,
     `tv` varchar(255) CHARACTER SET latin1 NOT NULL,
     `carpark` varchar(255) CHARACTER SET latin1 NOT NULL,
     `internet` varchar(255) CHARACTER SET latin1 NOT NULL,
     `own_bathroom` varchar(255) CHARACTER SET latin1 NOT NULL,
     `aircondition` varchar(255) CHARACTER SET latin1 NOT NULL,
     `address` text CHARACTER SET latin1 NOT NULL,
     `age_max` int(11) NOT NULL,
     `age_min` int(11) NOT NULL,
     `default_photo` int(11) NOT NULL,
     `rank` int(11) NOT NULL DEFAULT '0',
     `city` int(11) NOT NULL,
     `d_photo` int(11) NOT NULL,
     `sent_in_newsletter_date` date NOT NULL,
     `sent_in_newsletter` varchar(2) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `member_ID` (`member_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=42070 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `ad_have_photo` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `ad_ID` int(11) NOT NULL,
     `photo` varchar(255) NOT NULL,
     `member_ID` int(11) NOT NULL,
     `photo_number` int(11) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4025 DEFAULT CHARSET=latin1
    Code:
    CREATE TABLE `members` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `ip` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `admin` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `membership_exp` date NOT NULL,
     `last_activity` date NOT NULL,
     `newsletter` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'y',
     `password` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `first_name` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `last_name` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `email` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `phone` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `confirmation_key` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `membership_confirmed` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'y',
     `username` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `date_joined` date NOT NULL DEFAULT '0000-00-00',
     `sex` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
     `d_dob` int(11) NOT NULL,
     `about` text COLLATE latin1_general_ci NOT NULL,
     `smoke` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `raw_password` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `has_profile` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'n',
     `m_dob` int(11) NOT NULL,
     `y_dob` int(11) NOT NULL,
     `profile_visible` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'y',
     `contact_visible` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'y',
     `website` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `fax` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `is_agency` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `agency_name` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `m_online` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT 'n',
     `last_activity_min` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `signed_by` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `membership_type` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `payment_option` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `m_comments` text COLLATE latin1_general_ci NOT NULL,
     `date_edited` date NOT NULL,
     `edited_by` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `temp_pass_key` varchar(255) COLLATE latin1_general_ci NOT NULL,
     `temp_pass_key_expiry` bigint(20) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4652 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
    Last edited by English Breakfast Tea; January 4th, 2014 at 02:38 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by English Breakfast Tea
    1 - I want to count the number of photos per ad. Is this query well written and correct by MySQL rules?
    correct for mysql, yes, but violates standard sql because members.email should also be in the GROUP BY clause

    Originally Posted by English Breakfast Tea
    2 - How can I show "only the ads that do not have any photo"?
    add this to the WHERE clause --
    Code:
    AND ad_have_photo.ad_id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by r937
    correct for mysql, yes, but violates standard sql because members.email should also be in the GROUP BY clause

    add this to the WHERE clause --
    Code:
    AND ad_have_photo.ad_id IS NULL
    Hi Rudy,

    Glad to see your still around
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by English Breakfast Tea
    Glad to see your still around
    glad to see you reading my book
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo