How to show join 2 mysql tables?-CodePudding

I have 2 tables on MySQL: phy and VM.

Here is the phy table:

and here is the VM table:

I want to get a result like an image below:

I type the command below like this:

select ip, name, brand, vm.mem, vm.mem FROM phy JOIN vm ON phy.ip = vm.ip

but the result is like this:

what command do I have to type in order for me to get the result I want?

CodePudding user response：

SELECT *
FROM phy
JOIN vm USING (ip)

The query assumes that ip is defined as primary/unique key in both tables.

The query won't return the data for ip if it is present in one of these tables only. If you need such data then you'd use

SELECT *
FROM ( SELECT ip FROM phy
       UNION 
       SELECT ip FROM vm ) total
LEFT JOIN phy USING (ip)
LEFT JOIN vm USING (ip)

Or, if the presence is optional in one table only, use (for optional presence in vm, for example)

SELECT *
FROM phy
LEFT JOIN vm USING (ip)

CodePudding user response：

SELECT phy.id, phy.name, phy.brand, vm.mem, vm.hdd FROM phy INNER JOIN vm ON phy.id=vm.id;