Fast & Reliable Cloud Backups

For MySQL, MongoDB, Linux, Unix

Get Started

Oct 31, 2014

Helpful Alternatives to Pulling Data from Unrelated Tables

Categories IT Best Practices

Posted by Gen

feature photo

TL;DR: The simplest database operations are often the ones that pose the thorniest problems. You would think that retrieving data from two or more unrelated tables with a single query would be relatively easy, but in practice, the action can be anything but. Here are alternative approaches to grabbing data from unrelated tables in SQL and MySQL.

Ask 100 database administrators how to solve a design problem and you're likely to get 100 different answers -- at least. One of the most common predicaments facing DBAs is retrieving data from two unrelated tables at the same time. Here are three different approaches to the situation in SQL and MySQL.

One of the tips in Prashant S. Sarode's How to Write Efficient SQL Queries (pdf) describes how to combine several simple, unrelated database queries into one:

  • select name from emp where emp_no=1234;
  • select name from dpt where dot_no=10;
  • select name from cat where emp_no='RD';
  • select e.name, d.name, c.name
  • from cat c, dpt d, emp e, dual x
  • where NVL('x',X.DUMMY)=NVL('X', E.ROWId (+))
  • and NVL('x',X.DUMMY)=NVL('X', D.ROWId (+))
  • and NVL('x',X.DUMMY)=NVL('X', C.ROWId (+))
  • and e.emp_no(+)=1234
  • and d.dept_no(+)=10
  • and c.cat_type(+)='RD'

A question about this approach posted to Stack Overflow asks why the three null value comparisons are needed in the query. One response explains that the NVLs provide the outer join with something to join to. However, another response proposes a simpler solution (two, actually):

  • select name from emp where emp_no=1234;
  • UNION ALL
  • select name from dpt where dot_no=10;
  • UNION ALL
  • select name from cat where emp_no='RD';

This approach generates three separate rows. To place all three values in a single row, use this method:

  • SELECT (select name from emp where emp_no=1234) a,
  • (select name from dpt where dot_no=10) b,
  • (select name from cat where emp_no='RD') c
  • FROM DUAL

The null-column approach vs. multiple queries

When a MySQL user was faced with the need to pull data from two unrelated tables (one tracking customers' parts orders, and another their service orders), the first option was to use a UNION. However, this didn't work because the data and number of columns in the two tables were so dissimilar. This is what the person ended up with:

 

A MySQL query for retrieving data from two unrelated tables. Source: Stack Overflow

The first proposed solution was to use null columns, so that if the first table has columns labeled "name," "date," and "pieces," and the second has columns labeled "name_cust" and "date_bike_entered," the query would look like this:

  • Select name, date, pieces, null, null from table1
  • Union All
  • Select null, null, null, name_cust, date_bike_entered from table2

An alternative approach combines two queries to handle separate requests for "parts" and "services":

  • SELECT COUNT(*) FROM parts WHERE part_id = $transaction['part_id']
  • SELECT COUNT(*) FROM services WHERE serv_id = $transaction['serv_id']

Once you have the results for each query, you add logic to pull data based on the result.

Keep in mind that you may be closer than you think to the solution to a database glitch that has you stumped. A DBA posted a similar problem to Stack Overflow: the person had created a prepared statement to query two tables simultaneously that aren't joined because there's no logic between them:

  • SELECT t1.id AS tid, item_name,price,user_name
  • FROM users t1, items t2
  • WHERE t1.uid=$uid AND t2.id=$item_id AND t2.price <= t1.users_deposit

Placing "echo $row['tid'];" further into the script failed to generate output, but also failed to generate an undefined error. It turns out the person hadn't defined "$row", so adding "while ($row = $stmt->fetch()) {" solved the problem.

Even the best coders need somebody to look over their work now and again. The BitCan cloud storage service has you covered by ensuring automatic backups of your heterogeneous MySQL and MongoDB databases, as well as Unix/Linux systems and files. Set up and schedule backups in seconds using BitCan's simple interface, which requires no client-side installs or plug-ins. BitCan encrypts your data at the communication and storage layers, and your backups are retained forever on Amazon's reliable S3 servers. 

Fast & Reliable Cloud Backups

For MySQL, MongoDB, Linux, Unix

Get Started

Categories IT Best Practices