添加到LEFT JOIN查询

Hi I need help adding a new CLAUSE, this one is pretty advanced for me and I can't figure it out. //using the ect.author_id query the exp_channel_titles table author_id column and scan those rows for the final result. To be determined by IF ect.status = 'Members-id5' AND ect.status = 'complete'... then use the row with Members-id5 for ect.entry_id. My question is how would I do that? I marked where I assume the new CLAUSE or maybe even query would go, but I could be wrong.

嗨，我需要帮助添加一个新的CLAUSE，这个对我来说非常先进，我无法弄明白。 //使用ect.author_id查询exp_channel_titles表author_id列并扫描这些行以获得最终结果。由IF ect.status ='Members-id5'和ect.status ='complete'确定...然后使用Members-id5的行作为ect.entry_id。我的问题是我该怎么做？我标记了我认为新CLAUSE或甚至可能查询的地方，但我可能是错的。

My problem is there is up to 5 different ect.entry_id so i'm trying to isolate the one in the row with status = Members_id5 but only if status = complete exist as well. This seems like something I would have to do in PHP with array's any thoughts?

我的问题是最多有5个不同的ect.entry_id，所以我试图用status = Members_id5来隔离行中的那个，但只有在status = complete的情况下才存在。这似乎是我必须在PHP中使用数组的任何想法吗？

    SELECT
             ect.entry_id
        ,    ect.author_id
        ,    ect.title
        ,    ect.status
        ,    ecd.field_id_13
        ,    ecd.field_id_14
        ,    ecd.field_id_15
        ,    ecd.field_id_25
        ,    ecd.field_id_27
        ,    ecd.field_id_28
        ,    ecd.field_id_29
        ,    ecd.field_id_30
        ,    ecd.field_id_31
        ,    ecd.field_id_32
        ,    ecd.field_id_33
        ,    ecd.field_id_34
        ,    ecd.field_id_35
        ,    exm.email
    FROM exp_channel_titles as ect
    LEFT JOIN exp_channel_data as ecd
      ON ecd.entry_id = ect.entry_id
    LEFT JOIN exp_members as exm
      ON exm.member_id = ect.author_id

// new CLAUSE

    WHERE ect.title LIKE '%Member%'
    AND ect.status = 'Members-id5'

2 个解决方案

#1

Based on your comment, it sounds like you want to find individuals who have one record where ect.status = "Members-id5" and another where ect.status = "complete" . You then want the SELECT query to apply just to those individuals. You can do that using two subqueries within the WHERE clause. Something like this:

根据您的评论，听起来您想找到一个记录，其中ect.status =“Members-id5”，另一个记录ect.status =“complete”。然后，您希望SELECT查询仅适用于这些个人。您可以使用WHERE子句中的两个子查询来执行此操作。像这样的东西：

WHERE ect.title LIKE '%Member%'
AND ect.author_id IN 
     (select author_id from exp_channel_titles where ect.status = "Members-id5")
AND ect.author_id IN
     (select author_id from exp_channel_titles where ect.status = "complete")

The first subquery generates a list of all author IDs that have at least one record with "Members-id5" in it, and the IN clause checks to see if the author ID currently under consideration is one of those. The second one does the same with a list of all author IDs that have a record with "complete". If both conditions are true, then that means that individual is one whose records you want to return.

第一个子查询生成所有作者ID的列表，其中至少有一个记录中包含“Members-id5”的记录，IN子句检查当前正在考虑的作者ID是否是其中之一。第二个与具有“完整”记录的所有作者ID的列表相同。如果两个条件都为真，则表示个人是您想要返回的记录。

I'm not familiar with your data structure so this may not be the exact code you need (in particular, ect.author_id may not actually be the field to compare on), but hopefully this gives you the idea.

我不熟悉您的数据结构，因此这可能不是您需要的确切代码（特别是，ect.author_id实际上可能不是要比较的字段），但希望这能为您提供想法。

2 个解决方案

#1

更多相关文章

随机推荐