CODEWITHSUNDEEP
mysqlsqljoin
Ahsan Naseem
Ahsan Naseem

Reputation: 1106

MySQL join tables without repeating each table values

Hey I am trying to join 2 tables but left side is mapping to all entries. Which I am avoiding as I want tables to be merge but with NULL values where it should be

testtable00:

ownership membership
Johnny    Active

testtable01:

pet petname
dog John
cat timmy
parrot Johns

when I join them like this:

SELECT * FROM (select * from testtable00 inner join testtable01) as u where u.ownership like "%John%";

ownership membership pet petname
Johnny   Active      dog John
Johnny   Active      parrot Johns

what i was trying to achieve was 

ownership membership pet petname
Johnny   Active      NULL NULL
NULL       NULL      dog John
NULL       NULL      parrot Johns

Upvotes: 0

Views: 82

Answers (2)

krokodilko
krokodilko

Reputation: 36107

If there is more than 1 matching record in the first table, then this query gives you a correct order:

SELECT ownership, membership, pet, petname
FROM (
  SELECT ownership as x, membership as y, 1 as z,
         ownership, membership, null as pet, null as petname
  FROM testtable00
  UNION ALL
  SELECT ownership as x, membership as y, 2 as z,
         null, null, pet, petname
  FROM testtable00 u inner join testtable01
  where u.ownership like "%John%"
) x
ORDER BY x, y, z

Demo: http://sqlfiddle.com/#!9/4eb63e/1

| ownership | membership |    pet | petname |
|-----------|------------|--------|---------|
|      John |     Active | (null) |  (null) |
|    (null) |     (null) |    dog |    John |
|    (null) |     (null) |    cat |   timmy |
|    (null) |     (null) | parrot |   Johns |
|    Johnny |     Active | (null) |  (null) |
|    (null) |     (null) |    dog |    John |
|    (null) |     (null) |    cat |   timmy |
|    (null) |     (null) | parrot |   Johns |

Upvotes: 0

Barmar
Barmar

Reputation: 781058

Since there's no relationship between the tables, you shouldn't be doing a JOIN. The result you want looks like a UNION:

SELECT ownership, membership, NULL AS pet, NULL as petname
FROM testtable00
WHERE ownership like '%John%'
UNION ALL
SELECT NULL AS ownership, NULL AS membership, pet, petname
FROM testtable01
WHERE petname like '%John%'

Upvotes: 3

Related Questions