I'm newbie on SQL, I have 2 simple tables

ip_track

And ip

AND I want to get current_ip_id and old_ip_id with its ip in SELECT Statement like

But if ids are different I got duplicates For example, for
SELECT ip_track.id, current_ip_id, ip AS current_ip, old_ip_id, ip AS old_ip FROM ip_track INNER JOIN ip ON current_ip_id = ip.id OR old_ip_id = ip.id

But expect

How can it be solved?

CodePudding user response：

Join the ip table to the ip_track table twice:

SELECT
    ipt.id,
    ipt.current_ip_id,
    ip1.ip AS current_ip,
    ipt.old_ip_id,
    ip2.ip AS old_ip
FROM ip_track ipt
LEFT JOIN ip ip1
    ON ip1.id = ipt.current_ip_id
LEFT JOIN ip ip2
    ON ip2.id = ipt.old_ip_id
ORDER BY ipt.id;