Cross Table Queries

Say I have 10 tables each containing a field `hits`. If I want to find the top 10 records ordered by hits in descending order, can I archieve this with 1 query or will I have to use a query for each table and then sort the resulting 10 arrays?

 

You could do this in a stored procedure.

(Psuedo code)

Create TmpTable

insert into TmpTable (select top 10 hits from Table1 order by hits desc)

repeat for all desired tables.

select top 10 hits from TmpTable order by hits desc

Drop TmpTable

Temp tables are not the "best" way to go, but this will save you trips across the wire and array manipulation.

 

i am sure you can achieved by one query

 

Have you considered a UNION clause? That should get you what you need.

select top 10 hits as 'HitCount' from tblOne
order by dtOne desc
union
select top 10 hits from tblTwo
order by dtTwo desc

 

You probably need to change your database structure. I can't imagine a script that requires something like that.

 

Agreed, the only fields that should be present in many tables are primary/foreign keys.

 

Well about the structure, Had I put everything in one table it would have had a lot of records I and would have needed several extra fields describing which part of the website the record belong to. So I decided to make it different tables.

The top record orderd by hits is not a nessesary part but a welcome one and changing the structure now is too much work.

And I'll look into the UNION clause.

 

You should do that asap cause you'll have more trouble this way.


http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

Yeah not gonna happen, this is a personal site which no one is going to take over and it fits all my needs besides this one which isn't even nessesary.

I do know it's a better way of doing databases, but these were done a long time ago when I knew hardly anything about database structure, and honestly I can't be bothered to change something that works, despite it being a better solution, appreciate the advice though.

 

if you have designed it right, there should be only one table which has all diffrent values of hits once and only once.

Other tables may have hits filed, but they will either have repettion or incomplete for various reasons. (one hit belonging to several category will result in same hit being repeated for all categories).

SO querying the table where HITS is unique, is enough to list hits.

But if you want to display more information along with hits, (say price or disk format of record) then you should add all the tables that have those information to the query.

If you are making a list of hits in one page, the usual prcatice will be to show only a snap shot of information and use seperate page for showing all details.
(ie, like showing hit-title, Author/singer, Released-by, price) in the list-page and complete detail with description for a single hit/record in a separate page.

May be you can show the table names and filed names here so that people can help better.