Topic awaiting preservation: How to order by 2 columns in 2 tables (Page 1 of 1)

I have 2 tables, one holds rankings and the other holds content. The content table is ordered by a bigint autoincrement. The rankings table is ordered by the same id held in its table. The problem here is that not all the content elements have a ranking.

For instance,

content table:
id

WarMage: Could you not first pull out everything with a rank and then pull out everything without one. I'm not sure how simple the latter would be and, if it can't be done simply, you may have to store the IDs with a ranking in an array as you output them then do a second search and just not echo a result that has a matching ID in the array. That is the why I'd try it but I'd need to play around with things.

___________________
Emps

FAQs: Emperor

Good thought...

Yeah I will have to play with that idea for a couple minutes. Nice thinking there.

That's exactly how I've done that. Grab all the relevant data WITH rankings. Then grab the empty ones. You end up having two SQL statements, but it's easier.

How about something like
select content.id, ranking.rank
from content, ranking
where content.id = ranking.id
order by ranking.rank, content.id



.:[ The Tao of Steve ]:.
Be Desireless
Be Excellent
Be Gone
...................................

[This message has been edited by bitdamaged (edited 05-31-2002).]

Wait let's see that's not gonna return all the content id's
Try putting something like
select distinct content.id, ranking.rank
from content, ranking
where content.id = ranking.id
or content.id NOT NULL
order by ranking.rank, content.id

Hmm where's my SQL book.

NOPE NOT IT EITHER.

hmm gotta be a way



.:[ The Tao of Steve ]:.
Be Desireless
Be Excellent
Be Gone
...................................

[This message has been edited by bitdamaged (edited 05-31-2002).]

That is some intense SQL there. I think I might have to got out and get a book to translate it.

SELECT distinct content.id, rank.id
FROM content, ranking
WHERE rank.id NOT NULL
ORDER BY ranking.rank, content.id

Would this do it? The distinct part is the overlap? I am trying to figure out what you are going for with this.

The distinct is just a habit I got into at work mostly because everyone else does it it just excludes duplicate entries and is not really necessary here.

What I'm trying to do is grab all the id's in content table with the corresponding rank ordered by the rank.

The Where statement is where I make the connection between the rank and content tables. The problem is that while that allows me to grab the rank for the content id it also excludes any content.id fields that do not have a corresponding field in the rank table.

I know I'm missing something simple here.




.:[ The Tao of Steve ]:.
Be Desireless
Be Excellent
Be Gone
...................................

Would there be a way to join the data.

A command that would cause the data in the second table to act like it was part of the other table based on the ID?

Seeing as the IDs would be the same?