如何将两个独立的sql查询合并在一起，只获得不同的值

How can I get only the distinct values out of these two queries of mine in PHP:

我如何才能从这两个PHP查询中获得不同的值:

$query1 = "SELECT g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga
                    WHERE g.category >= 592 AND g.category <= 622
                        AND ga.affiliated_groups_hash = g.hash
                        AND LOWER(g.name) LIKE LOWER('%$school_name%')
                        ";
        $query2 = "SELECT g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga, c7_affiliations a
                    WHERE ga.affiliations_id = a.id
                        AND ga.groups_hash = g.hash
                        AND LOWER(a.name) LIKE LOWER('%$school_name%')";

I want to get only the distinct "g.hash" from both these queries. Its even better if i could have one sql query that would do both the tasks.

我只想得到唯一的g。从这两个查询中“散列”。如果我可以有一个可以同时执行两个任务的sql查询，那就更好了。

The details about my database table are as: c7_groups: id int(10)
name varchar(255)
hash varchar(32) groups_categories_id int(10) views int(10)

关于我的数据库表的详细信息如下:c7_groups: id int(10) name varchar(255) hash varchar(32) groups_classies_id int(10) views int(10)

c7_affiliations id int(10) name varchar(100)
groups_categories_id int(10)

c7_affiliates id int(10) name varchar(100) groups_classies_id int(10)

c7_groups_affiliations groups_hash varchar(32) affiliated_groups_hash varchar(32)--------FKey to c7_groups.hash
affiliations_id int(10)--------------------FKey to c7_affiliations.id
id int(10)

隶属关系组(32)从属关系组(32)从属关系组(32)哈希affiliations_id int(10)- - - - - - - - - - - - - - - - - - - FKey c7_affiliations。int id id(10)

both affiliated_groups_hash & affiliations_id could be null, or i should say one or them would always be null. If a groups is affiliated to another group, affiliations_id will be null and if the group is affiliated to to another affiliation, affiliated_groups_hash will be null.

从属关系_groups_hash &从属关系s_id可以是null，或者我应该说一个或它们总是null。如果一个组隶属于另一个组，从属关系s_id将为null，如果该组隶属于另一个隶属关系，从属_groups_hash将为空。

Let me know if you need any more details about anything.

如果你需要更多的细节，请告诉我。

Regards Zeeshan

问候Zeeshan

3 个解决方案

#1

Try this

试试这个

SELECT Distinct 
           g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga
                    WHERE g.category >= 592 AND g.category <= 622
                        AND ga.affiliated_groups_hash = g.hash
                        AND LOWER(g.name) LIKE LOWER('%$school_name%')
UNION
SELECT 
           g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga, c7_affiliations a
                    WHERE ga.affiliations_id = a.id
                        AND ga.groups_hash = g.hash
                        AND LOWER(a.name) LIKE LOWER('%$school_name%')

#2

Just do the union in a sub-select, then group by the derived table.

只需在子select中执行union，然后根据派生表进行分组。

select name,count(*) from
(
               SELECT g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga
                    WHERE g.category >= 592 AND g.category <= 622
                        AND ga.affiliated_groups_hash = g.hash
                        AND LOWER(g.name) LIKE LOWER('%$school_name%')
     UNION
               SELECT g.name, g.hash, g.views
                    FROM c7_groups g, c7_groups_affiliations ga, c7_affiliations a
                    WHERE ga.affiliations_id = a.id
                        AND ga.groups_hash = g.hash
                        AND LOWER(a.name) LIKE LOWER('%$school_name%')";
) as myTbl
group by name

#3

It seems like the only difference between the first query and the second is there's a join. Perhaps if you used ANSI-92 joins it would have been like this and there would be no need for UNION.

看起来第一个查询和第二个查询之间唯一的区别是有一个连接。如果您使用ANSI-92连接，可能会是这样的，并且不需要联合。

          SELECT DISTINCT 
                   g.name, 
                   g.hash,
                   g.views
            FROM 
                    c7_groups g
                  INNER JOIN  c7_groups_affiliations ga
                  ON ga.affiliated_groups_hash = g.hash
                  LEFT JOIN c7_affiliations a
                   on ga.affiliations_id = a.id

            WHERE 
                (LOWER(g.name) LIKE LOWER('%$school_name%')
                 AND g.category >= 592 AND g.category <= 622 
                 AND  a.ID is null)
                OR 
                 (LOWER(a.name) LIKE LOWER('%$school_name%')
                 and a.ID is NOT null)

Technically the a.ID is NOT null in the second part isn't required since LOWER(a.name) LIKE LOWER('%$school_name%') does this but it makes it a little clearer what's happening

技术上的。第二部分不需要ID为null，因为LOWER(a.o name)(比如LOWER('%$school_name%))会这样做，但是这样做会让事情变得更清楚一些

#1