MySQL join tables without repeating each table values

Question

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

There's no ownership column in either table, did you mean ownername? — Barmar
– Barmar, Commented Aug 8, 2017 at 16:22
I don't understand what you're trying to do. What is the joining condition between the two tables? If you leave out the ON clause, you get a cross-product between all the rows. — Barmar
– Barmar, Commented Aug 8, 2017 at 16:24

Barmar · Accepted Answer · 2017-08-08 16:26:15Z

3

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%'

answered Aug 8, 2017 at 16:26

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krokodilko · Accepted Answer · 2017-08-08 16:38:36Z

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 |

Collectives™ on Stack Overflow

MySQL join tables without repeating each table values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related