Mysql – Selecting distinct records having multiple properties

If you were designing from scratch, the structure you have is not ideal... however, it can easily be made to work as it is, with one small change to the structure and a more thorough understanding of what SQL can accomplish through joins.

You need to relax the unique constraint on `article_asset`.

UNIQUE KEY `asset_article` (`asset_id`,`article_id`)

This restricts you to not reusing the numeric asset_id of two different types of assets with the same article, which isn't correct, since asset_id is a value that can come from three different information domains (the primary keys of the three tables). The correct constraint would be this:

UNIQUE KEY `asset_article` (`asset_type`,`asset_id`,`article_id`)

With this, you are only restricted from duplicating the asset_id with the same asset_type for the same article, so this is the correct constraint for that unique index. That's the only actual change you have to make to your schema.

Then, modify your query structure to effectively treat asset_article as if it were multiple different tables, "taa" (topic article assets) and "caa" (celebrity article assets), each containing a distinct subset of the rows in asset_article.

SELECT a.*
  FROM articles a
  JOIN article_assets taa ON taa.article_id = a.id AND taa.asset_type = 'topic' 
  JOIN topics t ON t.id = taa.asset_id 
  JOIN article_assets caa ON caa.article_id = a.id AND caa.asset_type = 'celebrity'  
  JOIN celebrities c ON c.id = caa.asset_id 
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A logically-equivalent way of expressing this query (above) looks like this (below), which might make it clearer how we are joining both the "taa" and "caa" subsets of article_asset to the articles on the article_id ... or it may make everything much less clear. :)

SELECT a.*
  FROM articles a
  JOIN (article_assets taa JOIN topics t 
                           ON t.id = taa.asset_id 
                           AND taa.asset_type = 'topic') ON taa.article_id = a.id
  JOIN (article_assets caa JOIN celebrities c 
                           ON c.id = caa.asset_id 
                           AND caa.asset_type = 'celebrity') ON caa.article_id = a.id
 WHERE t.name = 'Family'
   AND c.name = 'Ashton Kutcher';

A similar structure could be used to find articles about two different celebrities, two different topics, or 1 celeb + 2 topics + 1 brand or however many you felt like stringing together.

With your sample data, my example queries return nothing, because there is no overlap among the different types of assets associated with each story, but after changing the UNIQUE constraint, you can modify the data so that there are overlaps and the query will find the correct results.

http://sqlfiddle.com/#!2/99e4d/4

On the other hand, if you do decide to go with a different design, I would suggest that the more correct approach involves fewer tables -- not more tables. If you step back and look at the three kinds of "things," you may see that they are all, in fact, one single kind of thing, or at least subtypes of a single kind of thing. There is no reason why celebrities, topics, and brands should be stored separately -- they are all "subjects" of the articles. When "things" in a database are the same "kind of thing," they should all be in one table.

For example, if you had a database of contact information, you wouldn't have an "office phone numbers" table and a "home phone numbers" table, and then a table matching people to phone numbers telling you which table to look in for the phone number... right? That makes no sense at all. This is the same scenario, just slightly less obvious because you didn't model it based on the common thread among these apparently different (but, arguably, actually the same) kinds of things.

I can't give you a textbook phrase to describe what I'm attempting to say, but if you ever need to look at an attribute in a row in order to determine what information domain another attribute in the row represents, you are definitely "doing it wrong."

One important reason for this assertion is that you cannot enforce referential integrity via foreign key constraints when the "foreign key" could reference the primary key of any one of several tables.

Consider this structure:

CREATE TABLE subject (
  id INT NOT NULL AUTO_INCREMENT,
  subject_type ENUM('brand','celebrity','topic') NOT NULL,
  name VARCHAR(255) not null,
  PRIMARY KEY(id)
);

CREATE TABLE article_has_subject (
  article_id INT NOT NULL,
  subect_id INT NOT NULL,
  PRIMARY KEY(article_id, subject_id),
  KEY(subject_id, article_id) /* to optimize joins in either direction */
);

You could replace that ENUM with a subject_type_id and a corresponding subject_type table, and then, if you wish one day that you wish you had created an "places" table so you could find all of the stories about 'Family' and 'Rhode Island' there's no need to change your schema... you just add another subject_type record.

In your current structure, you're also storing a redundant piece of information in every row of article_asset -- the type of the asset -- because you need that to find the table where you should look. Redundant data in a database is rarely a good thing.

First solution

Well, I tried a solution. It works but it is pretty ugly. But it works...

SELECT count(*)
FROM (
    SELECT code, dates.selected_date
    FROM appartments
    INNER JOIN (select * from 
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates
    WHERE (code, selected_date) NOT IN (
        SELECT code, dates.selected_date
        FROM appartments
        INNER JOIN (select * from 
          (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
          ) dates ON dates.selected_date between date_arrival and date_departure)
    GROUP BY code, dates.selected_date) available_dates_by_code

Change the date period in the line WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates.

Remove the first level SELECT FROM to get all dates of unoccupied apartments for dates between '2015-04-16' AND '2015-04-28'.

You may want to change the '2015-01-01' dates to something earlier (ie. CURDATE() if you're only working with future dates). This query will only return next 30 years dates past '2015-01-01', so change it to something like CURDATE() - '1 YEAR'

I'm very curious to see if someone have a better solution...

How it works

From the bottom to the top :

• The first SELECT gets all occupied dates for all appartments.
• The second SELECT gets all dates wanted and removes all appartment/date couples that are occupied.
• The third select count the number of appartement/date couple available between the provided dates.

Second solution

SELECT
  (
    SELECT adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
    FROM apartments
    INNER JOIN (SELECT * FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) -
  (
    SELECT count (code, dates.selected_date)
    FROM apartments
    INNER JOIN (SELECT * FROM
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
      ) dates ON dates.selected_date between date_arrival and date_departure
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) AS 'days_of_availability'

This one is much more simpler. The second SELECT counts the number of days existing for the two dates multiplied by the number of apartments. The third SELECT counts the number of occupied days for all the apartments. The top SELECT does (number of days) minus (number of occupied days).

Fun fact: it took me almost 30 minutes to get this query working. That's a shame.