Question 112900 in Mysql Question posted in
The official MySQL documentation can be found here.

How to show join 2 mysql tables?

I have 2 tables on MySQL: phy and VM.

Here is the phy table:

phy table

and here is the VM table:

The VM table

I want to get a result like an image below:

expected result

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:

results obtained

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

Tags:

2

Answers


  1. 0 
    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)
    Login or Signup to reply.
  2. 0 
    SELECT phy.id, phy.name, phy.brand, vm.mem, vm.hdd
FROM phy
INNER JOIN vm ON phy.id=vm.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search