Combining mysql Table Queries

[Bean Boy]

Hi,

 

I'm trying to run a query for an updates section, where I combine 3 or more tables and then order them by the common time value of each table.

 

But I'm getting an error:

 

"Column 'time' in field list is ambiguous"

 

Here's my current query:

 

$updates = mysql_query("SELECT headline, time FROM articles, comments, threads
ORDER BY time DESC")
or die(mysql_error());

[jl5501]

This means you have the column time in both tables so you need to specify which one to use.

 

I dont know which fields are in which tables but I will make some assumptions and you can adjust

 

$updates = mysql_query("SELECT c.headline, a.time FROM articles a, comments c, threads ORDER BY a.time DESC")

[Bean Boy]

The basic idea is that I want to order it by ALL the times from the 3 tables.

 

Maybe I'm going about it the wrong way, though.

 

Here's an example:

 

An article is posted at 1:00pm (inserted into the article table)

 

A comment is posted at 2:00pm (inserted into the comments table)

 

I want my updates page to display these two values (from two different tables) but order them chronologically, descending:

 

UPDATES:

-Comment Made at 2:00pm

-Article Posted at 1:00pm

[jl5501]

Yes you do need to rethink your approach.

 

You would need some kind of master updates table where the timestamps were stored, along with a key to say type, and the id from the relevant table

[Psycho]

You need to use the MySQL UNION command to merge the tables into one contiguous table. However, you can only do that if the tables have the EXACT same columns in the EXACT same order. If they are exactly the same, then look at this tutorial: http://www.tizag.com/sqlTutorial/sqlunion.php

 

If they are not exactly the same (but the fields have the same format) you could try doing a UNION by only selecting the identical fields like this:

 

SELECT article as name, time FROM articles
UNION
SELECT comment as name, time FROM comments

[Bean Boy]

Thanks man.