CODEWITHSUNDEEP
mysqlsql
Hasitha Shan
Hasitha Shan

Reputation: 2980

Matching columns

I was wondering if there is a way to display a set of matching or distinct data.. like, say i have three tables,

tbl_1-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
2   |bbbb   |malware
3   |cccc   |ddos
3   |cccc   |trojan
4   |dddd   |ddos

tbl_2-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
3   |cccc   |ddos
4   |dddd   |trojan
5   |eeee   |trojan
6   |ffff   |other

tbl_3-
ip  |isp    |infection
----------------------
1   |aaaa   |ddos
6   |ffff   |
2   |bbbb   |other

and to get a result as follows,

Result i need-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
1   |aaaa   |ddos
2   |bbbb   |malware
2   |bbbb   |other
3   |cccc   |ddos
3   |cccc   |trojan
4   |dddd   |ddos
4   |dddd   |trojan
5   |eeee   |trojan
6   |ffff   |other
6   |ffff   |

The problem is i dont know what methodology to use for this scenario, i used this query a full join ,

SELECT a. * , b. * , c. *
FROM tbl_1 a
LEFT OUTER JOIN tbl_2 b
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_3 c
USING ( ip, isp, infection )
UNION
SELECT a. * , b. * , c. *
FROM tbl_2 b
LEFT OUTER JOIN tbl_1 a
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_3 c
USING ( ip, isp, infection )
UNION
SELECT a. * , b. * , c. *
FROM tbl_3 c
LEFT OUTER JOIN tbl_1 a
USING ( ip, isp, infection )
LEFT OUTER JOIN tbl_2 b
USING ( ip, isp, infection )

but this gives the result in a different way,

Result i get-
ip  |isp    |infection  ip  |isp    |infection  ip  |isp    |infection
----------------------  ----------------------  ----------------------
1   |aaaa   |malware    1   |aaaa   |malware    NULL|NULL   |NULL
NULL|NULL   |NULL       NULL|NULL   |NULL       1   |aaaa   |ddos   
2   |bbbb   |malware    NULL|NULL   |NULL       NULL|NULL   |NULL   
NULL|NULL   |NULL       NULL|NULL   |NULL       2   |bbbb   |other
3   |cccc   |ddos       3   |cccc   |ddos       NULL|NULL   |NULL
3   |cccc   |trojan     NULL|NULL   |NULL       NULL|NULL   |NULL
4   |dddd   |ddos       NULL|NULL   |NULL       NULL|NULL   |NULL
NULL|NULL   |NULL       4   |dddd   |trojan     NULL|NULL   |NULL
NULL|NULL   |NULL       5   |eeee   |trojan     NULL|NULL   |NULL       
NULL|NULL   |NULL       6   |ffff   |other      NULL|NULL   |NULL       
NULL|NULL   |NULL       NULL|NULL   |NULL       6   |ffff   |

PLEASE help me on this one, i want to display the data in 3 columns only..

Thank you very much :)

Upvotes: 2

Views: 90

Answers (4)

addiedx44
addiedx44

Reputation: 2743

It's not the most elegant solution, but would something like this work? (assuming I have the syntax correct...can't test it at the moment):

SELECT * FROM ((SELECT * FROM tbl_1) UNION (SELECT * FROM tbl_2) UNION (SELECT * FROM tbl_3)) ORDER BY isp DESC, infection DESC;

Upvotes: 1

RGO
RGO

Reputation: 4737

You should UNION the three tables and do GROUP BY to remove the duplicates:

SELECT ip, isp, infection 
FROM tbl_1

UNION

SELECT ip, isp, infection 
FROM tbl_2

UNION

SELECT  ip, isp, infection 
FROM tbl_3

GROUP BY ip, isp, infection

ORDER BY IP

Upvotes: 1

nawfal
nawfal

Reputation: 73183

SELECT * 
FROM   (
        SELECT * FROM tbl_1 
        UNION 
        SELECT * FROM tbl_2 
        UNION 
        SELECT * FROM tbl_3
       ) AS t
ORDER BY ip, isp

Upvotes: 1

John Woo
John Woo

Reputation: 263733

This is simple UNION

SELECT ip, isp, infection FROM tbl_1
UNION
SELECT ip, isp, infection FROM tbl_2
UNION
SELECT  ip, isp, infection FROM tbl_3
ORDER BY IP

SQLFiddle Demo

Upvotes: 3

Related Questions