CODEWITHSUNDEEP
phpmysqlunion-all
Liz.
Liz.

Reputation: 815

Single Query to fetch records from multiple tables with different columns

I have 2 different tables as want to get records in a single query. Currently, I am using 2 queries then merging the array result and then displaying the record. Following is my current code:

$db = JFactory::getDbo();
$query1 = "SELECT a.id as cId, a.title, a.parent_id,a.level FROM `categories` AS a WHERE ( a.title LIKE '%keyword%' )";
$result1 = $db->setQuery($query1)->loadObjectlist(); //gives selected records

$query2 = "SELECT b.id as indId, b.indicator , b.cat_id, b.subcat_id, b.section_id  FROM `indicator` as b WHERE ( b.indicator LIKE '%keyword%' )"; 
$result2 = $db->setQuery($query2)->loadObjectlist(); //gives selected records

$_items = array_merge($result1,$result2); //then using $_items in php code to display the data

It is in Joomla however I just want to know how we can merge these 2 queries into one. I tried the following but it gives the result of first query from categories table.

(SELECT id as cId, title, parent_id,level, NULL FROM `categories`  WHERE ( title LIKE '%birth%' )) 
      UNION ALL 
(SELECT id as indId, indicator , cat_id, subcat_id, section_id FROM `indicator` WHERE ( indicator LIKE '%birth%' ))

Desired output: 

+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+
| cId  | title       | parent_id  | level  | indId  | indicator      | cat_id | subcat_id | section_id
+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+
| 2874 | births      |   2703     | 2      |  null  |   null         |  null  |   null    | null     |
+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+
| 13   | birth weight|   12       | 3      |   null |   null         | null   |  null     | null     |
+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+
| null |  null       |    null    |  null  | 135    | resident births|   23   |     25    |     1    |
+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+
| null |  null       |    null    |  null  | 189    | births summary |   23   |     25    |     1    |
+------+-------------+------------+--------+--------+----------------+--------+-----------+----------+

This above output will help to get proper pagination records. I tried to use join but JOIN needs a common column in ON clause. Here, I want all the columns and their values. Basically I want to combine the 2 table records in one query. Any help would be appreciated

Upvotes: 0

Views: 2241

Answers (2)

Liz.
Liz.

Reputation: 815

As @Sinto suggested the answer for union and dummy column names following is the whole correct query:

(SELECT id as cId, title, parent_id,level, NULL as indId, NULL as indicator , NULL as cat_id, NULL as subcat_id, NULL as section_id FROM `jm_categories` WHERE ( title LIKE '%births%' )) UNION ALL (SELECT NULL as cId, NULL as title, NULL as parent_id,NULL as level, id as indId, indicator , cat_id, subcat_id, section_id FROM `jm_indicator_setup` WHERE ( indicator LIKE '%births%' ))

We have to match the column names from both tables so that we get records as a combination.

Upvotes: 0

Sinto
Sinto

Reputation: 3997

Here is an example,

There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.

Let's say you have the two tables:

parts:              custs:
+----+----------+   +-----+------+
| id | desc     |   |  id | name |
+----+----------+   +-----+------+
|  1 | Sprocket |   | 100 | Bob  |
|  2 | Flange   |   | 101 | Paul |
+----+----------+   +-----+------+

Forget the actual columns since you'd most likely have a customer/order/part relationship in this case; I've just used those columns to illustrate the ways to do it.

A cartesian product will match every row in the first table with every row in the second:

> select * from parts, custs;
      id desc     id  name
      -- ----     --- ----
      1  Sprocket 101 Bob
      1  Sprocket 102 Paul
      2  Flange   101 Bob
      2  Flange   102 Paul

That's probably not what you want since 1000 parts and 100 customers would result in 100,000 rows with lots of duplicated information.

Alternatively, you can use a union to just output the data, though not side-by-side (you'll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select):

> select id as pid, desc, '' as cid, '' as name from parts
  union
  select '' as pid, '' as desc, id as cid, name from custs;
    pid desc     cid name
    --- ----     --- ----
                 101 Bob 
                 102 Paul
    1   Sprocket
    2   Flange

In some databases, you can use a rowid/rownum column or pseudo-column to match records side-by-side, such as:

id desc     id  name
-- ----     --- ----
1  Sprocket 101 Bob
2  Flange   101 Bob

The code would be something like:

select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;

It's still like a cartesian product but the where clause limits how the rows are combined to form the results (so not a cartesian product at all, really).

I haven't tested that SQL for this since it's one of the limitations of my DBMS of choice, and rightly so, I don't believe it's ever needed in a properly thought-out schema. Since SQL doesn't guarantee the order in which it produces data, the matching can change every time you do the query unless you have a specific relationship or order by clause.

I think the ideal thing to do would be to add a column to both tables specifying what the relationship is. If there's no real relationship, then you probably have no business in trying to put them side-by-side with SQL.

Upvotes: 2

Related Questions