CODEWITHSUNDEEP
sqlpostgresqlselectjoin
Mr B
Mr B

Reputation: 4130

How to select all records but order by a related table's column?

I have the following tables:

  1. keywords,
  2. keywords_clients,
  3. keywords_groups,
  4. clients,
  5. groups

Tables 2 and 3 are for many to many relationships between keywords and groups and keywords and clients.

I want to retrieve all the records from the keywords table. However I want to order the keywords in such a way that the the ones that belong to a particular group, appear first. How do I achieve this?

I hope this makes sense and sincerely appreciate any help.

Upvotes: 0

Views: 76

Answers (1)

vyegorov
vyegorov

Reputation: 22895

You need to join all the tables you want to use, but you should limit your select list to only those columns, that are important for you. It is not mandatory to select all columns from all the tables, although this is what SELECT * gives you.

Something like this:

SELECT k.*
  FROM keywords k
  JOIN keyword_groups kg ON k.k_id=kg.k_id
  JOIN groups g ON kg.g_id=g.g_id
 ORDER BY CASE WHEN g.class='Top' THEN 0 ELSE 1 END, g.name, k.name;

If you need to see only specific group, just add a WHERE clause for it.

Upvotes: 2

Related Questions