0

So imagine I have 3 related tables with 10 column in each namely users, settings, activitylogs. Obviously the users table would have an id column in it as its primary key, and the settings table and activitylogs table would have a user_id column. Running the query below would return all the columns including the id column in the users table, user_id column in tables settings and activitylogs.

select * from users
  inner join settings on users.id = settings.user_id
  inner join on activitylog on users.id = activitylogs.user_id

The problem is that I don't want to get the user_id columns on other tables and I don't want to end up writing an extremely long query specifying all the columns that I want to get. So I thought I could create a query that kind of looks like this

select all except id from users

which can also be used together with joins.

select all except activitylogs.user_id, settings.user_id from users
  inner join settings on users.id = settings.user_id
  inner join activitylogs on users.id = activitylogs.user_id

which would actually return all the columns except what was explicitly specified.

  • How should I do this? I mean what are the programming languages that I need to use or do I have to edit mysql's source code or do something like that?
  • Is it going to be legal to do this? because I want to do this while avoiding a lawsuit.

If you have anything to say related to this, or if you can point me to any blog posts related to this, please do so.

aprilmintacpineda
  • 1,114
  • 13
  • 21
  • 2
    There's a basic rule: *don't write SELECT *` n production code because nobody knows which columns are returned in which order (and ALTER TABLE might change it). What you propose is just a variation of that. Standard SQL supports `join .. using` which returns only one of the join columns instead of duplicating it. – dnoeth Jul 26 '17 at 14:12
  • @dnoeth but still, it would contain the `user_id` column right? If what I propose cannot be done then how can you achieve the same result while avoiding a very long mysql query? – aprilmintacpineda Jul 26 '17 at 14:18
  • You simply list the columns you want, which are usually way less than all. Btw, most SQL Editors can assist creating a column list ... – dnoeth Jul 26 '17 at 14:22
  • @dnoeth Okay so let's take that out of the equation maybe that query wouldn't be so effective, still, is there a way to create my own customised MySQL query? – aprilmintacpineda Jul 26 '17 at 14:25
  • Of course there are ways, probably most of them either based on modifing existing source or writing your own SQL Editor :-) – dnoeth Jul 26 '17 at 14:30
  • Wow! I didn't know about that, and I thought I might be sued if I even think about modifying the source code, can you point me to any blog posts or video or any resources that talk about this? – aprilmintacpineda Jul 26 '17 at 14:34
  • Sorry, no. I'm an old SQL guy, my programming days are over :-) – dnoeth Jul 26 '17 at 14:40
  • Apart from dnoeths proposal to change the source code, [this answer](https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql) should contain all (limited) possibilities to do this inside mysql (if you don't want to use the (correct) solution of listing all columns you want). If you do this outside of mysql, maybe from php, you can write a framework or at least your own custom fetch function that returns the result as an array without specified columns. You would retrieve everything, but would not return all fields to the higher level functions in your application. – Solarflare Jul 26 '17 at 14:46

2 Answers2

1

Unfortunately with your table structure there is no such way to remove the duplicate coloums in mysql.

Although mysql provide a way to remove the duplicate coloums when you use the join. If you have the same coloum name in both the table then you can use "Using" instead of "ON" with join. "Using" removed the extra identical coloum. Please refer the below url for more detail: MySQL ON vs USING?

Hope this will help!

Anoop Singh
  • 80
  • 4
  • Nice that was helpful! I'll keep that in mind next time. Unfortunately for me, I can't use a column `id` as a `foreign key` for the other relation table because I expect that the `foreign key` would be repeated over and over again. The goal is to actually remove the `user_id` column in the result set while avoiding an extremely long query because there are 30 columns in there. – aprilmintacpineda Jul 26 '17 at 14:32
0

Generally you would write a query to return only the columns you want so that you exercise a greater degree of control over the result even if, yes, this involved having to list out thirty columns - it;s a one off cost, after all.

Consider that a future update might add more columns to the user table, perhaps a notes column that has a large amount of HTML stored in it, or a column that contains an encoded image of the user, or a scanned and saved document. With your method (or select * in general) you then have to be aware of every use of that query and consider whether this additional data is required for that use case or may cause a performance issue selecting an retrieving a large amount of data that is not actually required.

Ben J. Boyle
  • 306
  • 1
  • 12