du5114 2014-09-19 00:52
浏览 35
已采纳

从PHP MySQL的SELECT语句中获取重复值

My database has two tables: ips, oips

ips.public field contains values: 1, 2, 3, 4, 5, 6, 7, 8

oips.public field contains values: 1, 3, 7

I want to select all values in ips.public that do not appear in oips.public

I'm using the following MySQL query within PHP:

SELECT * FROM ips, oips WHERE ips_ips.public != oips.public

This is returning the following:

1, 1, 2, 2, 2, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 8, 8, 8

As you can see, the values that exist in both tables are only shown twice, whilst everything else is displayed three times (presumably due to this iterating on both tables).

Could anybody please shed some light on how to have this code so that it'd only return the values that are not in both tables (aka: 2, 4, 5, 6, 8)

Thanks!

  • 写回答

2条回答 默认 最新

  • dthp96899 2014-09-19 00:54
    关注

    There are several ways to do this.

    I prefer to use NOT EXISTS:

    SELECT *
    FROM ips i
    WHERE NOT EXISTS (
        SELECT 1
        FROM oips o
        WHERE i.ipublic = o.opublic)
    

    Here's NOT IN:

    SELECT *
    FROM ips
    WHERE ipublic NOT IN (SELECT opublic FROM oips)
    

    And here's LEFT JOIN/NULL:

    SELECT i.*
    FROM ips i
        LEFT JOIN opublic o ON  i.ipublic = o.opublic
    WHERE o.opublic IS NULL
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?