MySQL Update Query – How to Use Multiple WHERE and SELECT Conditions

This is a "relational division" problem and there are several ways to solve.

One way is with two EXISTS subqueries:

SELECT p.id 
FROM photos AS p
WHERE EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cute'
      )
 AND  EXISTS
      ( SELECT 1
        FROM photos_tags AS pt
        WHERE p.id = pt.photo_id 
          AND pt.tag = 'cool'
      ) ;

Another with two joins (assuming that the photo_tags table has a unique constraint on (photo_id, tag_id), i.e. no photo can been tagged with the same tag twice):

SELECT p.id 
FROM photos AS p
  JOIN photos_tags AS pt1
    ON  p.id = pt1.photo_id 
    AND pt1.tag = 'cute'
  JOIN photos_tags AS pt2
    ON  p.id = pt2.photo_id 
    AND pt2.tag = 'cool' ;

One more way is @Abhik's answer, with one join and GROUP BY.

You can see a few more ways (more than 13) in this awesome answer: How to filter SQL results in a has-many-through relation. Not all of them will work in MySQL as the question is for Postgres but the problem is identical.

Strictly speaking - and if there is a FOREIGN KEY from photo_tags to photos - the photos table can be removed from all the above queries. You can return the photo_id instead. But I guess you may want more columns from the photo table in the result set.

This is doable. What you need is 5 SELECTs with one INSERT

SET @schema_name = 'foo1';
SET @lookup_table = 'mytable';
SET @user_id = '1';

SET @proc = CONCAT('INSERT INTO ' ,@schema_name, '.tbl_name ');
SET @proc = CONCAT(@proc,'(tbl_id, shortlist_id, year, created_at, updated_at) ');
SET @proc = CONCAT(@proc,'SELECT       "2", "1", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "2", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "7", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "9", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "8", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '"');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;