CODEWITHSUNDEEP
phpmysqljoin
user3207931
user3207931

Reputation: 11

"joining" multiple tables in MySQL and ordering them by date format: 1319744408

I've been trying to get this to work for hours and have tried several options on stackoverflow with no luck. I have seen on here what I thought I would need but just not getting it. I will give an example of what I think should work but get an error: Column 'date2' in order clause is ambiguous - I know that means date2 is listed in more than one table, which is correct and that's what I want. Anyways, here is how I'd like for it to be:

$sql = "SELECT
            *
        FROM
            videos, games, pics
        ORDER BY
            date2 DESC
        LIMIT 10";

mysql_query($sql) or die(mysql_error());

videos, games & pics all have a date2 field which I would like to order by for the home page. The date2 field has the date formatted like, 1319744408.

I've tried several things with no luck. Someone please help me.

Upvotes: 0

Views: 67

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

It is ambiguous because you have the same field name in three tables but have not specified which of these you are trying to order by (i.e. videos.date2).

You also have the problem that you are currently making a Cartesian join of all records across the tables. So if you have 100 records in each table, you would end up with 1 million records in your result set (100 * 100 * 100) before applying the limit. You are probably not intending to sort 1 million rows.

You should specify the join conditions between the tables, be specific about the table and column you want to order by, and probably be specific about the fields you actually need to return in the result set rather than using SELECT *. You also need to make sure you have indexes on all the fields you would use for joins or for ordering so your query can run more efficiently.

If your intent is to simply get a list of the most recent time items from these tables and the tables themselves are not related to each other, you would likely need to use a UNION.

$query = '(SELECT * FROM videos)
UNION
(SELECT * FROM games)
UNION
(SELECT * FROM pics)
ORDER BY date2 DESC
LIMIT 10';
$r = mysql_query($query);

In this case, date2 would not be ambiguous. Here you may actually be forced to specify the columns you want to select from each table if the tables do not have indentical columns/column types. The UNION will require that each SELECT statement return the same number of columns with the same data type in each column.

I also might suggest you start looking at using mysqli or PDO instead of deprecated mysql_* functions.

Upvotes: 3

Bojangles
Bojangles

Reputation: 101483

Using a LEFT JOIN or other explicit join would be better here, but to the question in hand:

Disambiguate the table like this:

SELECT * FROM videos, games, pics ORDER BY videos.date2 DESC LIMIT 10

Upvotes: 1

Related Questions