CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
Fabian
Fabian

Reputation: 53

mysql - do a top5 of two (not related) tables in one statement? (with the use of "order by")

I want to do a top5 of two tables that are not related with each other. And this all should be in one statement. I'm wondering if this possible cause I'm trying it now for hours. Google didn't help me after all...

My Code:

SELECT
  a.id AS "link_id", a.href AS "link_href", a.clicks AS "link_clicks",  
  b.id AS "user_id", b.name AS "user_name", b.posts AS "user_posts"
FROM links a, users b
ORDER BY a.clicks DESC, b.posts DESC 
LIMIT 5

When I execute this, I get the following:

link_id     link_href     link_clicks     user_id     user_name     user_posts
5           example.com   123456         10          Name 1        98765
5           example.com   123456         99          Name 2        7486
5           example.com   123456         77          Name 3        6543
5           example.com   123456         65          Name 4        23
5           example.com   123456         1           Name 5        0

As you can see the result of the links are every time the same..

I tried everything I could imagine... inner join, left join etc... nothing worked. Is this in one statement actually possible or should I use two separate statements? (I tried one statement cause I thought this would be better for the performance?)

Upvotes: 0

Views: 89

Answers (3)

nate c
nate c

Reputation: 9005

By what your saying you want to join the tables on a user_id

SELECT
  a.id AS "link_id", a.href AS "link_href", a.clicks AS "link_clicks",  
  b.id AS "user_id", b.name AS "user_name", b.posts AS "user_posts"
FROM links a, users b

where a.user_id = b.id

ORDER BY a.clicks DESC, b.posts DESC 
LIMIT 5

If table a does not have a user id then you need to figure out the common link between the two tables.

Upvotes: 0

Donnie
Donnie

Reputation: 46933

Given that links and users aren't related then doing this as a single query doesn't make sense. Your query as written is doing a cross join, which isn't what you want at all.

Just submit two queries. Getting unrelated data in one query is nonsensical.

Upvotes: 1

ThiefMaster
ThiefMaster

Reputation: 318568

It makes no sense to do this in one query at all - so don't do it. You might be able to do it using UNION if you use column name aliases so the column names fit (you also need to ensure the types fit). But as I said, really don't do it.

Upvotes: 1

Related Questions