CODEWITHSUNDEEP
mysqlmariadb
arieffirdaus
arieffirdaus

Reputation: 63

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?

Upvotes: 0

Views: 38

Answers (2)

beyzanur
beyzanur

Reputation: 38

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

Upvotes: 1

Akina
Akina

Reputation: 42632

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)

Upvotes: 3

Related Questions