Closed Thread Icon

Preserved Topic: How to order by 2 columns in 2 tables (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=21220" title="Pages that link to Preserved Topic: How to order by 2 columns in 2 tables (Page 1 of 1)" rel="nofollow" >Preserved Topic: How to order by 2 columns in 2 tables <span class="small">(Page 1 of 1)</span>\

 
WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 05-30-2002 19:09

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 05-30-2002 19:38

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

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 05-30-2002 20:45

Good thought...

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

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 05-31-2002 16:22

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.

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 05-31-2002 17:47

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).]

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 05-31-2002 18:48

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).]

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 06-01-2002 15:11

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

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 06-01-2002 15:57

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.

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 06-01-2002 20:42

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
...................................

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 06-02-2002 03:04

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?

« BackwardsOnwards »

Show Forum Drop Down Menu