Union SQL query in php

I have a problem while trying to link 5 tables using the UNION SQL query. One of the problems is the number of elements I require from each SQL query separated by UNION is 开发者_运维百科different. And when I use '' in the query to make number of elements in each query equal, it just displays result from the first query only. Here is the query which links 5 tables.

(SELECT a.a1,b.b1 
      FROM a,b 
     WHERE a.a2 = b.b2) 
   UNION
   (SELECT a.a1,c.c1 
      FROM a,c 
     WHERE a.a3 = c.c3) 
   UNION
   (SELECT a.a1,d.d1,d.d2 
      FROM a,d 
     WHERE a.a4 = d.d4) 
   UNION
   (SELECT a.a1,e.e1,e.e2,e.e3 
      FROM a,e 
     WHERE a.a5 = e.e5)

This shows an error, while when I rewrite it so as to make the number of elements equal (here 4) as:

(SELECT a.a1,b.b1,'','' 
      FROM a,b 
 WHERE a.a2 = b.b2) 
UNION
(SELECT a.a1,c.c1,'','' 
   FROM a,c 
  WHERE a.a3 = c.c3) 
UNION
(SELECT a.a1,d.d1,d.d2,'' 
   FROM a,d 
  WHERE a.a4 = d.d4) 
UNION
(SELECT a.a1,e.e1,e.e2,e.e3 
   FROM a,e 
  WHERE a.a5 = e.e5)

I get only the result from 1st query ie, result of SELECT a.a1,b.b1,'','' from a,b where a.a2 = b.b2 alone

Maybe those queries return many duplicates. Try to use UNION ALL instead of UNION, because the later removes duplicate rows.

i suggest running each individual statement by itself and determining if you are getting the results you expect from each one.

I'd suggest using UNION ALL and adding an identifier to each part, something like

 SELECT "q1" AS src, a.a1, b.b1, NULL, NULL
 FROM a
 JOIN b ON a.a2 = b.b2
UNION ALL
 SELECT "q2" AS src, a.a1, c.c1, NULL, NULL
 FROM a
 JOIN c ON a.a3 = c.c3 
[...]

That way you should at least be able to find out which part of your UNION returns data

Use NULL, rather than an empty string:

   SELECT a.a1, b.b1, NULL, NULL
     FROM a
     JOIN b ON a.a2 = b.b2
   UNION
   SELECT a.a1, c.c1, NULL, NULL
     FROM a
     JOIN c ON a.a3 = c.c3 
   UNION
   SELECT a.a1, d.d1, d.d2, NULL
     FROM a
     JOIN d ON a.a4 = d.d4
   UNION
   SELECT a.a1, e.e1, e.e2, e.e3 
     FROM a
     JOIN e ON a.a5 = e.e5

NULL is not a value -- it's a placeholder for the lack of any value.

This is necessary because UNION statements (including UNION ALL) require that the same number of columns returned in all unioned SELECT statements exist, and that the data types should match.

If you are still not seeing data you'd expect, you need to look at each statement individually to be sure it actually returns data. It's possible the join is not working as you'd expected...