CODEWITHSUNDEEP
mysql
wenchiching
wenchiching

Reputation: 485

how to select many different data"s" from two table?

I have two tables tableA, tableB
Two of them have col "ip",like this...

tableA-ip
1.1.1.1
1.1.1.2
1.1.1.3
1.1.1.4
1.1.1.5
1.1.1.6
1.1.1.7
1.1.1.8

tableB-ip
1.1.1.3
1.1.1.4
1.1.1.5

And what I want is
1.1.1.1
1.1.1.2
1.1.1.6
1.1.1.7
1.1.1.8

How to select it?

Upvotes: 1

Views: 111

Answers (5)

Matt Ball
Matt Ball

Reputation: 359846

It looks like you want the set difference (that is, IPs in A that are not also in B), soooooo:

SELECT a.ip FROM tableA a WHERE tableA.ip NOT IN (SELECT b.ip FROM tableB)

Upvotes: 3

user1122414
user1122414

Reputation: 

SELECT col1, col2, .. , Ip from TableA
UNION 
SELECT col, col2, ...., Ip from TableB

To get the differences you can use the MINUS Operator instead of UNION

Upvotes: -1

SlavaNov
SlavaNov

Reputation: 2485

Use NOT IN:

SELECT ip FROM TableA WHERE TableA.ip NOT IN (SELECT ip FROM TableB)

Upvotes: 2

Pragalathan M
Pragalathan M

Reputation: 1781

select a.id from a minus select b.id from b

or

select a.id from a where a.id not in (select b.id from b)

Upvotes: -1

Henrik
Henrik

Reputation: 3704

You can combine two result sets with UNION.

select ip from tableA
union 
select ip from tableB;

http://dev.mysql.com/doc/refman/5.0/en/union.html

The default behavior is to remove duplicate rows. If you want duplicate rows use UNION ALL.

Upvotes: -1

Related Questions