0

I am trying to bind some "account tables" with my users table via account_id, account_type and account_table.

How am I supposed to query the database so that I get the account_table and order by 

`account_table`.`some_column`

Here is the link to some images of my DB.

Now, I can explain what I want to do over an example:

I want to order my users depending on their site_assessors.name.

select * from `users` order by (users.account_table).name desc limit 10 offset 0

This query is actually what I need, I wanna get the account_table, in this case site_assessors and query the name field of it.

Hope it's understandable...

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Ozan Kurt
  • 3,731
  • 4
  • 18
  • 32
  • "Hope it's understandable" - It's not. At least not for me. – Paul Spiegel Feb 28 '17 at 19:27
  • Well, you gotta tell me what you didn't understand. – Ozan Kurt Feb 28 '17 at 19:27
  • I guess reference to a non-existent `account_table` (in the context of the question and the snapshot provided) will only confuse the readers, if the question is really only about `site_assessors`. – Dhruv Saxena Feb 28 '17 at 19:32
  • There are multiple tables like: `admins`, `sites` and `local_authorities`. Thats why I need the `account_table` as a variable from the row. – Ozan Kurt Feb 28 '17 at 19:33
  • Please correct me if I'm wrong, but are you implying that the structure of all these tables is _exactly_ the same: `admins`, `sites`, `local_authorities` and `site_assessors`? And the `account_table` column in `users` is merely used to identify which of those four tables the record actually corresponds to? – Dhruv Saxena Feb 28 '17 at 20:02
  • Exactly, you got it all right. – Ozan Kurt Feb 28 '17 at 20:03
  • 1
    Ok, in that case, the least I can say is that there's an inherent design flaw in the schema structure. This structure is not easily scalable - and will therefore affect all the queries written before - should you define a new `type` in the future. You don't need 4 separate tables. Just have one consolidated table and use a column to identify the `account_type` (which can then hold values such as `site_assessor`, `admin`, `local_authorities`, etc). Or better yet, create a new table for all those `types` and use a foreign key in the `users` table. – Dhruv Saxena Feb 28 '17 at 20:10
  • So you're saying that I should create an `accounts` table and relate the `users` table with its `type` column? – Ozan Kurt Feb 28 '17 at 20:12
  • The problem there is that all these tables should have different columns not only name column. – Ozan Kurt Feb 28 '17 at 20:19

1 Answers1

0

Table and columns names are part and parcel of SQL statements, so if you need different tables/columns, you need a different SQL statements.

It's not clear what you are trying to achive, but maybe you just want a list of names/descriptions sorted by name/description so the user can choose one. If so, you could modify your schema along the lines Dhruv said. Have id, type and descriptions in an account table, type and column_label in a type table, drop the name column or whatever the describing column is called from the other tables, and drop any tables that are left with no columns besides the key.

Based on the details provided, you may be looking for something more flexible. In that case we're probably talking about generating sql dynamically. How To have Dynamic SQL in MySQL Stored Procedure.

Mic
  • 331
  • 1
  • 4