mysql - 从 "log"表获取数据到 mySQL 查询

Question

我需要检查日志表中的数据以查看用户之前是否阅读过帖子。我的日志表名为 foretag_kontaktervisadbrf2017，它包含有关谁阅读了帖子的信息。如何编辑我的查询，以便获得信息告诉我是否已阅读帖子？

到目前为止，这是我的查询:

SELECT 
    brfbolagsverket.BrfNamn
    , brfextra.BrfId
    , brfextra.Organisationsnr
    , brfextra.Gatuadress
    , brfextra.Ort
    , brfextra.Lagenheter
    , brfextra.ByggAr
    , BrfHarFastighet.Fangdatum    
FROM 
    (((((brfextra LEFT JOIN BrfHarFastighet ON brfextra.Organisationsnr = BrfHarFastighet.Organisationsnr)     
    LEFT JOIN brfkonkurs ON brfextra.Organisationsnr = brfkonkurs.Organisationsnr) 
    LEFT JOIN brfavford ON brfextra.Organisationsnr = brfavford.Organisationsnr) 
    LEFT JOIN brffusion ON brfextra.Organisationsnr = brffusion.Organisationsnr) 
    LEFT JOIN brflikvidation ON brfextra.Organisationsnr = brflikvidation.Organisationsnr) 
    LEFT JOIN brfbolagsverket ON brfextra.Organisationsnr = brfbolagsverket.Organisationsnr  
WHERE 
    (((brfkonkurs.BolagetsStatusKod) IS NULL) 
    AND ((brfavford.BolagetsStatusKod) IS NULL) 
    AND ((brffusion.BolagetsStatusKod) IS NULL) 
    AND ((brflikvidation.BolagetsStatusKod) IS NULL OR (brflikvidation.BolagetsStatusKod) = 34)) 

在结果中，我需要日志表中的以下附加列:

BrfId, BrfNamn, Organisationsnr, LogEntry
1,     Billy,   Organisationsnr, You have shown this post before
2,     Carl,    Organisationsnr, NULL
3,     Kent,    Organisationsnr, NULL
4,     Sara,    Organisationsnr, You have shown this post before

请帮助我添加到查询中的代码。我缺乏编码经验。

Answer 1

我会通过添加一个子选择来做到这一点，如下例所示。希望它能帮助您指明正确的方向。

SELECT 
    brfbolagsverket.BrfNamn
    , CASE WHEN (SELECT COUNT(*) FROM foretag_kontaktervisadbrf2017 AS L WHERE L.Organisationsnr = B.Organisationsnr AND L.Foretag_kontaktID = 1247) > 0 THEN 'Lest' ELSE 'Inte lest' END AS NotificationRead
    , brfextra.BrfId
    , brfextra.Organisationsnr
    , brfextra.Gatuadress
    , brfextra.Ort
    , brfextra.Lagenheter
    , brfextra.ByggAr
    , BrfHarFastighet.Fangdatum

FROM (((((
    brfextra AS B
    LEFT JOIN BrfHarFastighet ON brfextra.Organisationsnr = BrfHarFastighet.Organisationsnr) 
    LEFT JOIN brfkonkurs ON brfextra.Organisationsnr = brfkonkurs.Organisationsnr) 
    LEFT JOIN brfavford ON brfextra.Organisationsnr = brfavford.Organisationsnr) 
    LEFT JOIN brffusion ON brfextra.Organisationsnr = brffusion.Organisationsnr) 
    LEFT JOIN brflikvidation ON brfextra.Organisationsnr = brflikvidation.Organisationsnr) 
    LEFT JOIN brfbolagsverket ON brfextra.Organisationsnr = brfbolagsverket.Organisationsnr 

WHERE (((brfkonkurs.BolagetsStatusKod) Is Null) AND ((brfavford.BolagetsStatusKod) Is Null) AND ((brffusion.BolagetsStatusKod) Is Null) AND ((brflikvidation.BolagetsStatusKod) Is Null Or (brflikvidation.BolagetsStatusKod)=34))