MySQL returning arrays from subqueries, and NULL

Question

I have two tables, "records", and "info".

The "records" table looks like:

mysql> SELECT * FROM records WHERE num = '7';
+-----+--------+----+------+-----+-----+------------+-----------+----------+---------------------+
| id  | city   | st | type | num | val | startdate  | status    | comments | updated             |
+-----+--------+----+------+-----+-----+------------+-----------+----------+---------------------+
| 124 | Encino | CA | AAA  | 7   |   1 | 1993-09-01 | allocated |          | 2014-02-26 08:16:07 |
+-----+--------+----+------+-----+-----+------------+-----------+----------+---------------------+

and so on. Think of the "num" field in this table as a Company ID.

The "info" table contains information about certain companies, and uses that company id as a unique identifier. Not all companies listed in "records" will be in "info". An example of the "info" table:

mysql> SELECT * FROM info LIMIT 2;
+-----+-------+--------------------------+---------------------+
| org | name  | description              | updated             |
+-----+-------+--------------------------+---------------------+
|   0 | ACME  |                          | 2014-02-19 10:35:39 |
|   1 | AT&T  | Some Phone Company, Inc. | 2014-02-18 15:29:50 |
+-----+-------+--------------------------+---------------------+

So "org" here will match "num" in the first table.

I want to be able to run a query that returns, on one line, everything but 'id', 'type' and 'val' from the 1st table, and IF APPLICABLE, the 'name' and 'description' from the 2nd table.

I can achieve what I want using this query:

SELECT city,st,num,startdate,status,comments,updated, \
( SELECT name FROM info WHERE org = '7') AS name, \
( SELECT description FROM info WHERE org = '7') AS description \
FROM records WHERE num = '7'

But I see at least two problems with it:

It seems inefficient to run two subqueries
When there is no record in "info", NULL is printed for the name and description. I would like to print some string instead.

To address the first problem, I tried to return an array. But when no corresponding record exists in the "info" table, then I get nothing, not even the valid info from the "records" table. Here's my array query:

SELECT city,st,num,startdate,status,comments,updated,asinfo.name AS name,asinfo.description AS description \
FROM records, \
( SELECT name,description FROM info WHERE org = '7') AS asinfo \
WHERE num = '7'

This query works fine if a given company id exists in both tables.

To address the second problem, I tried various incantations of IFNULL and coalesce, to no avail.

I'd appreciate any insight.

Thanks.

probably nothing...I've never used it. I will research it now (unless you can post a quick example) — atreyu
– atreyu, Commented Feb 27, 2014 at 13:42

Alma Do · Accepted Answer · 2014-02-27 13:41:02Z

2

Apply LEFT JOIN syntax:

SELECT 
  r.city,
  r.st,
  r.num,
  r.startdate,
  r.status,
  r.comments,
  r.updated,
  IF(d.name IS NULL, 'Default', d.name) AS name,
  IF(d.description IS NULL, 'Default', d.description) AS description
FROM
  records AS r
    LEFT JOIN info AS d ON r.num=d.org
WHERE
  r.num='7'

that will work such way: LEFT JOIN looks into first table, and, if there are no corresponding records in second, it applies NULL. So you'll discover that with IF (or IFNULL) and do substitution of default string.

answered Feb 27, 2014 at 13:41

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

1 Comment

atreyu Over a year ago

thanks for your response, @Alma Do. i appreciate your stringent syntax - makes it easier to understand.

Barmar · Accepted Answer · 2014-02-27 13:40:29Z

1

Use a LEFT JOIN to get null values when there's no matching row in the info table.

SELECT city,st,num,startdate,status,comments,updated,
       IFNULL(name, 'Default Name') name, 
       IFNULL(description, 'Default Description') description
FROM records r
LEFT JOIN info i ON r.num = i.org
WHERE r.num = 7

answered Feb 27, 2014 at 13:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

atreyu Over a year ago

Thanks @Barmar, that did the trick all right. I had to use r.updated b/c that field exists in both tables.

user3360243 · Accepted Answer · 2014-02-27 13:41:38Z

1

It sounds like a simple LEFT JOIN from record to info will do the trick.

LEFT JOIN rather than JOIN in order to ensure you ALWAYS get all rows from the record table, and then the corresponding data in info table if a xref exists for that ID.

Whether using your sub-queries or using joins, if you always want to see all rows in record table, then you will always get NULLs corresponding to the info table where no xref exists. The only way to avoid that is to run some code that calls everything from record, and then iterates over the results to query info, to conditionally add to the record data.

answered Feb 27, 2014 at 13:41

user3360243

372 bronze badges

Collectives™ on Stack Overflow

MySQL returning arrays from subqueries, and NULL

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related