SQL query against two tables

Question

I have two tables in a MySql DB:

items

• id (char)
• name (varchar)
• description (varchar)
• modified_at (datetime)

folders

• id (int)
• name (varchar)
• parent_id (int)

From a query against a search index I get back an ordered list of ids for items and folders that matched the query. For example, ("ph76gjd", 34, "rh75ghd", "gr45gfd"). Of course, in most cases the set will be much larger than this. I also have a list columns that I want in the result set and some of these columns may not exist in both tables. For example, ("id", "name", "modified_at").

I am looking for a way to efficiently build a result set by querying the DB.

• Do I need to make two separate queries since I want data from two different tables? If so, then I will need to put the results back in order somehow?
• Is there a way to do this with a single query using a union or a join?

I am using Ruby on Rails but I don't think the programming language is really a factor here since I hope to do this mostly in SQL. Any help is appreciated.

Update

Thanks for the answers but I guess I wasn't clear enough. The tables are unrelated as far as this problem is concerned. I have an array of ids of the rows I want in the order they should appear and all I want is to pull the additional column data from the DB.

Answer 1

It's not apparent from your description how the two tables are related.

It sounds to me like a one-to-many relationship (e.g. a folder has one or more items; an item belongs to only one folder), but that usually means a foreign key relationship between the two tables:

create table folder
(
    id int not null auto increment,
    name varchar(80) not null,
    primary key(id)
);

create table item
(
    id int not null auto increment,
    name varchar(80) not null,
    folder_id int,
    modified date,
    primary key(id),
    foreign key folder_id references table folder(id)
);

You'd query as follows:

select folder.id, folder.name, item.id, item.name, item.modified_date
from folder, item
where folder.id = item.folder_id
order by item.modified_date desc;

Answer 2

Hi Randi Simon,

what about trying the following...

select f.id, f.name, i.id, i.name, i.modified_date from items i left join folders f on i.id = f.parent_id order by i.modified_date

hope it helps,

Santi! :)

Answer 3

You can get the result you want using the combination of UNION, FIELD and select_rows.

#ids is an array of id's
results = Items.connection.select_rows("
  SELECT A.id, A.name, A.description, A.modified_at, NULL AS parent_id
  FROM   items A
  WHERE  A.id IN (#{ids.join(',')})

  UNION

  SELECT B.id, B.name, NULL AS description, NULL AS modified_at, B.parent_id
  FROM   folders B
  WHERE  A.id IN (#{ids.join(',')})
  ORDER  BY FIELD(id, #{ids.join(','))
")

The result is ordered by the order of the id's. This works for MySQL.