-1

I have a base table and many query results

first is the base list:

a, b
name1, type1
name2, type2
name3, type3
name4, type4

b is an ID

others are SELECT result like this (count the types in the other table)

null, 5
type1, 3
type4, 3

null is 5 because in the other table many rows haven't any type

if I use LEFT JOIN

type1, 3
type4, 3

and not null row, because null not included in a base list

I did a trick:

SELECT a, b 
FROM table1 
UNION
SELECT "none", null 
FROM table1

result:

name1, type1 
name2, type2
...
name4, type4
none, null

BUT if I tried the LEFT JOIN, no change

name1, type1, 3
name2, type2, null
...
name4, type4, 3
none, null, null

how can I put the null, 5 value in the joined result? (ok I can use the RIGHT and LEFT OUTER JOIN, but I have many result tables, which I want to join the base list)

Thank you so much, and sorry for my poor english :)

Mike
  • 31
  • 6
  • I think you're trying to achieve a `full outer join`, which unfortunately isn't supported in in mySQL. The behavior can be mimic'd by using LEFT/RIGHT and union as you suggested. [example](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) – xQbert Aug 28 '15 at 14:41
  • show us real data sample of both tables and post expected result set. and post full query string not just *others are SELECT* or *BUT if I tried the LEFT JOIN,* – Alex Aug 28 '15 at 15:50

1 Answers1

1

I solved the problem. I read in mysql docs, working with nulls https://dev.mysql.com/doc/refman/5.0/en/working-with-null.html "You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL." So I changed the ON clause:

SELECT * FROM
(SELECT... ) a
LEFT JOIN
(SELECT... ) b
ON a.id = b.id OR (a.id IS NULL AND b.id IS NULL)

and b select joined to a select with null value

Mike
  • 31
  • 6