無法讓此查詢正常工作

我一直試圖讓以下查詢工作好幾天，但我似乎無法解決我的問題。我正在使用 MySQL 5.7.9 版

我有以下數據庫：

CREATE TABLE user (
   id INT NOT NULL AUTO_INCREMENT,
   avatar VARCHAR(64) NOT NULL,
   prename VARCHAR(64) NOT NULL,
   name VARCHAR(64) NOT NULL,
   username VARCHAR(64) NOT NULL,
   password VARCHAR(256) NOT NULL,
   ts TIMESTAMP NOT NULL,
   PRIMARY KEY(id)
);

CREATE TABLE address (
   id INT NOT NULL AUTO_INCREMENT,
   street VARCHAR(128) NOT NULL,
   zip VARCHAR(10) NOT NULL,
   place VARCHAR(128) NOT NULL,
   PRIMARY KEY(id)
);

CREATE TABLE personUpload (
   id INT NOT NULL AUTO_INCREMENT,
   aid INT NOT NULL,
   name VARCHAR(128) NOT NULL,
   prename VARCHAR(128) NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(aid) REFERENCES address(id)
   ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE INDEX pAndName ON personUpload(name, prename);

CREATE TABLE imageUpload (
   id INT NOT NULL AUTO_INCREMENT,
   pid INT NOT NULL,
   sujet VARCHAR(128) NOT NULL,
   path VARCHAR(200) NOT NULL,
   takenOn DATE NOT NULL,
   takenAt VARCHAR(64) NOT NULL,
   deleted TINYINT NOT NULL,
   ts TIMESTAMP NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(pid) REFERENCES personUpload(id)
   ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE favorites (
   id INT AUTO_INCREMENT NOT NULL,
   imgid INT NOT NULL,
   uid INT NOT NULL,
   active TINYINT NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(imgid) REFERENCES imageUpload(id)
   ON DELETE RESTRICT ON UPDATE CASCADE,
   FOREIGN KEY(uid) REFERENCES user(id)
   ON DELETE RESTRICT ON UPDATE CASCADE
);

以下是一些範例數據：

-- User table
INSERT INTO `user` VALUES (1,'/img/userpics/default.jpg','Hans','Meier','admin','$argon2i$v=19$m=1024,t=2,p=2$Qk1VbC9xc3BwM0ZSOW4xcw$vScQmts5pydI5xW18qQkiRHaxrdeDLNbBvFYXJ8bI8M','2019-11-11 09:31:29'),(2,'/img/userpics/b0749ece77da027125f201fa23e28244.gif','Huerden','Sohn','huerdensohn','$argon2i$v=19$m=1024,t=2,p=2$aXhSYzU1TnVnRklxNlNPaA$88G2zS5VqEDtom87ZCGqR2YlEhyJnLDZM0dDK72Qka4','2019-11-13 09:13:00'),(3,'/img/userpics/b8ce4c3e73696e9c4bc4b3933301e107.jpg','John','Doe','johndoe69','$argon2i$v=19$m=1024,t=2,p=2$QWFyN1hGRTFWQlJDMHdQQg$eZFY4L0rPMmVnK57kp3RZbkd5t2oFWOJ0goqlAc01Vs','2019-11-15 10:29:26');

-- Address table
INSERT INTO `address` VALUES (1,'Testavenue 6','1234','Townname'),(2,'Halloweg 1','4012','afdfdaafafd'),(3,'Halloweg 1','4012','dfaadfsafdadfs'),(4,'dfafdafds 5','4444','dfdsfafsdfas');

-- personUpload table
INSERT INTO `personupload` VALUES (1,1,'Parker','Peter'),(2,2,'Johnson','Joe'),(3,3,'Name','Prename'),(4,4,'fddfadafs','dfsfdsadfa');

-- imageUpload table
INSERT INTO `imageupload` VALUES (1,1,'JA','/img/upload/80s-retro-city-background-footage-072549572_prevstill.jpeg','2019-11-24','Place',0,'2019-11-15 10:33:18'),(2,1,'JA','/img/upload/909663.png','2019-11-24','Place',0,'2019-11-15 10:33:18'),(3,1,'JA','/img/upload/1486835476509.jpg','2019-11-24','Place',0,'2019-11-15 10:33:18'),(4,2,'fsxfsfgssgfsgf','/img/upload/1573627770324.jpg','2019-11-08','Place',0,'2019-11-18 11:22:16'),(5,2,'fsxfsfgssgfsgf','/img/upload/1573633928517.png','2019-11-08','Place',0,'2019-11-18 11:22:16'),(6,2,'fsxfsfgssgfsgf','/img/upload/1573551081995.gif','2019-11-08','Place',0,'2019-11-18 11:22:16'),(7,3,'adfdfdfa','/img/upload/1568367191324.png','2019-11-03','Place',0,'2019-11-18 11:22:32'),(8,3,'adfdfdfa','/img/upload/70362754_129736035063450_1737794598899548160_n.jpg','2019-11-03','Place',0,'2019-11-18 11:22:32'),(9,3,'adfdfdfa','/img/upload/ZomboMeme_31082019200224.jpg','2019-11-03','Place',0,'2019-11-18 11:22:32'),(10,4,'sfddfdfs','/img/upload/65043948_1807204376092277_6361774892492259328_n.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18'),(11,4,'sfddfdfs','/img/upload/1560196429338.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18'),(12,4,'sfddfdfs','/img/upload/1559793227717.jpg','2019-11-09','sfddfsdfsa',0,'2019-11-18 11:23:18');

-- favorites table
INSERT INTO `favorites` VALUES (1,1,1,1),(2,2,3,0),(3,11,3,1),(4,5,3,1),(5,12,1,1);

現在我要做的是獲取每張圖片，其中 favorites.id 要麼為 null，要麼為目前正在使用系統的使用者的 user.id。我已經嘗試過以下查詢：

SELECT personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM favorites
RIGHT JOIN user ON favorites.uid = user.id
RIGHT JOIN imageUpload ON favorites.imgid = imageUpload.id
RIGHT JOIN personUpload ON imageUpload.pid = personUpload.id
RIGHT JOIN address ON personUpload.aid = address.id
WHERE favorites.uid IS NULL OR favorites.uid = 2;

但是如果其他使用者將其保存為收藏，則此查詢不會顯示該圖像。

以下查詢也不起作用，因為它顯示了重複的條目：

SELECT personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM imageUpload 
JOIN personUpload ON imageUpload.pid = personUpload.id 
JOIN address ON personUpload.aid = address.id 
LEFT JOIN favorites ON imageUpload.id = favorites.imgid;

我希望輸出將 favorites.uid 顯示為 null 或者如果它是使用者的收藏夾，則顯示其相應的使用者 ID。目前，如果它是其他使用者的最愛，它不會出現在結果中。

我希望我已經制定了我想要做得足夠好的事情，以便有人理解。

編輯：感謝@eagle275，以下查詢似乎有效

SELECT personupload.name, 
      personupload.prename, 
      imageupload.sujet, 
      imageupload.path, 
      imageupload.id, 
      favorites.uid, 
      Date_format(imageupload.takenon, '%d.%m.%Y') AS takenOn, 
      imageupload.takenat, 
      address.street, 
      address.zip, 
      address.place 
FROM   imageupload 
      JOIN personupload 
        ON imageupload.pid = personupload.id 
      JOIN address 
        ON personupload.aid = address.id 
      LEFT JOIN favorites 
             ON imageupload.id = favorites.imgid 
                AND favorites.uid = 2 
ORDER  BY imageupload.id DESC 
LIMIT  10 offset 0 

您的第二個選擇查詢可以滿足您的要求… - 提供所有圖像上傳條目 - 連同誰拿走了它以及在哪裡/何時 - 以及誰將其標記為收藏… 我建議您為 favorites.uid 設置一個適當的別名以使其更多著名的

http://sqlfiddle.com/#!9/b04a03/4

SELECT 
 CASE 
    WHEN favorites.uid=1 THEN 1
    ELSE 0
 END AS `my_favorite`,
 personUpload.name, personUpload.prename, imageUpload.sujet, imageUpload.path, imageUpload.id, favorites.imgid, favorites.active, favorites.uid as favorite, DATE_FORMAT(imageUpload.takenOn, '%d.%m.%Y') AS takenOn, imageUpload.takenAt, address.street, address.zip, address.place 
FROM imageUpload 
JOIN personUpload ON imageUpload.pid = personUpload.id 
JOIN address ON personUpload.aid = address.id 
LEFT JOIN favorites ON imageUpload.id = favorites.imgid;

如果圖像是使用者的最愛，則在前面給出一列，如果圖像是使用者的最愛，則為 0 - 或在其他情況下為 0 .. - 例如，使用者 1 標記了 2 個最愛

要針對使用者進行調整 - 將有問題的 user_id 放在 case-when 語句中代替 1

引用自：https://dba.stackexchange.com/questions/253528