Subquery help

I hope someone can help with this. If so then I'd really appreciate
it.

I have this sql statement:

SELECT *
FROM affiliates_referrals, applications
WHERE affiliates_referrals.application_id =
applications.application_id
AND applications.status =2
ORDER BY affiliates_referrals.timestamp DESC

and I am trying to reverse the order of it by doing this for display
purposes:

SELECT * FROM (SELECT * FROM affiliates_referrals, applications WHERE
affiliates_referrals.application_id = applications.application_id AND
applications.status = 2 ORDER BY affiliates_referrals.timestamp DESC
LIMIT 50) AS x ORDER BY timestamp DESC

I get a "Column 'timestamp' in order clause is ambiguous" error to do
with the "ORDER BY timestamp DESC" part. I've tried putting
applications.timestamp and affiliates_referrals.timestamp but that
doesn't work either. This subquery worked fine with a single table but
it doesn't work with 2 tables being joined. It's probably something
simple but I just can't figure it out. TIA!

Re: Subquery help

"Evil Bert" wrote in message
news:dh8gu1ts4ont7u382ksq1pmk3mrrs9f34e@4ax.com...
>I hope someone can help with this. If so then I'd really appreciate
> it.
>
> I have this sql statement:
>
> SELECT *
> FROM affiliates_referrals, applications
> WHERE affiliates_referrals.application_id =
> applications.application_id
> AND applications.status =2
> ORDER BY affiliates_referrals.timestamp DESC
>
> and I am trying to reverse the order of it by doing this for display
> purposes:
>
> SELECT * FROM (SELECT * FROM affiliates_referrals, applications WHERE
> affiliates_referrals.application_id = applications.application_id AND
> applications.status = 2 ORDER BY affiliates_referrals.timestamp DESC
> LIMIT 50) AS x ORDER BY timestamp DESC
>
> I get a "Column 'timestamp' in order clause is ambiguous" error to do
> with the "ORDER BY timestamp DESC" part. I've tried putting
> applications.timestamp and affiliates_referrals.timestamp but that
> doesn't work either. This subquery worked fine with a single table but
> it doesn't work with 2 tables being joined. It's probably something
> simple but I just can't figure it out. TIA!

i'm not entirely sure what you are trying to do, but, if you are only trying
to reverse the order why not do...

ORDER BY affiliates_referrals.timestamp ASC ?

probably this is not what you are trying to do, would you mind explaining
further? also, sub-selects are used in WHERE clauses when you need to run a
query that matches the result of another query

SELECT column_name FROM table_name
WHERE column_name =
(
SELECT other_column
FROM other_table
WHERE other_column
)

- kevin

Re: Subquery help

"Evil Bert" wrote in message
news:dh8gu1ts4ont7u382ksq1pmk3mrrs9f34e@4ax.com...
> SELECT * FROM (SELECT * FROM affiliates_referrals, applications WHERE
> affiliates_referrals.application_id = applications.application_id AND
> applications.status = 2 ORDER BY affiliates_referrals.timestamp DESC
> LIMIT 50) AS x ORDER BY timestamp DESC

I assume you meant that one of those orderings should be ASC. Otherwise you
aren't reversing the order, and you might as well have used your original
query.

> I get a "Column 'timestamp' in order clause is ambiguous" error to do
> with the "ORDER BY timestamp DESC" part.

Is there a timestamp column in both tables? If so, you could have two
columns coming out of the subquery with the same name. You might need to
replace your "SELECT *" with an explicit list of columns, and use AS to
rename the ones with duplicate names.

SELECT *
FROM