CODEWITHSUNDEEP
phpmysqlsql
Ivijan Stefan Stipić
Ivijan Stefan Stipić

Reputation: 6678

MySQL listing in a one table, the order in the another table

I have 2 tables: "users" and "pages".

I need to list table "users" but IF user have own pages to first display users who have "pages" and after that to list others who don't have "pages".

USERS table:
id   |   name   |   lastname

PAGES table:
id   |  user_id   |    content

I don't know how to write query for this.

Thanks!

Upvotes: 0

Views: 37

Answers (4)

Vicenç Gascó
Vicenç Gascó

Reputation: 1346

You can easily add a column in your query with COUNT, so you know how many pages the user have.

SELECT u.id, u.name, u.lastname, count(p.id)
FROM USERS u
RIGHT JOIN PAGES p ON u.id = p.user_id
GROUP BY u.id

Upvotes: 0

Paul McNett
Paul McNett

Reputation: 847

Something like this should get you close:

select users.id as user_id,
       users.name as user_name,
       users.lastname as user_lastname,
       count(*) as tot
  from users
  left join pages
    on pages.user_id = users.id
 group by user_id, user_name, user_lastname
 order by tot DESC, user_lastname, user_name

It should list the users with the most pages first, followed by the users with fewer pages, followed by the users with no pages, and within each page count, order the users alphabetically by last name and then user name.

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

Join both tables together then sort on pages.user_id

select *
from users u
left join pages p on u.id = p.user_id
order by p.user_id desc

Upvotes: 0

Anthony Garcia-Labiad
Anthony Garcia-Labiad

Reputation: 3711

You can try LEFT JOIN, this will show all users and replace page data by NULL if the user doesn't owns any, then sort by page. The distinct aims to avoid doubloons.

SELECT DISTINCT u.name, u.lastname FROM users u LEFT JOIN pages p ON u.id = p.user_id ORDER BY p.id;

Upvotes: 0

Related Questions