Databases
StackOverflow2013
Postsdbo
POHow do I avoid redundant data fields in the result set when using JOINs?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I avoid redundant data fields in the result set when using JOINs?
    primarykey
    data
    text
    <p>Following join is supposed to retrieve user info along with their messages for users with a certain status:</p> <pre><code>SELECT * FROM user, message WHERE message.user_id=user.id AND user.status=1 </code></pre> <p>The problem is that all rows about a certain user in the result set contain redundant columns that repeat the same data about that user (those fields retrieved from user table), only fields from the message table contain non-redundant information. Something like this:</p> <pre><code>user.id username email message.id subject 1 jane jane@gmail.com 120 Notification 1 jane jane@gmail.com 122 Re:Hello 1 jane jane@gmail.com 125 Quotation 2 john john@yahoo.com 127 Hi jane 2 john john@yahoo.com 128 Fix thiss 2 john john@yahoo.com 129 Ok 3 jim jim@msn.com 140 Re:Re:Quotation </code></pre> <p>As you can see many data are redundant and we do not want to first find the users and then go about their messages in loop like structures or something like that. Loops that cause micro-queries should be avoided at all costs. </p> <p>I am not concerned about the output of my program, that is well handled in the UI. I <em>think</em> perhaps the network traffic produced by returning the result of this query could be substantially reduced if somehow I can manage to eliminate the repetition of user data in all rows pertaining to that user.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USاشکان نظری
    1. USاشکان نظری
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIn what context or output format? What environment as well?
      singulars
      1. POHow do I avoid redundant data fields in the result set when using JOINs?
      1. USREW
    2. COForgive me, but this is how databases work. Each message is different, so your INNER JOIN operation is going to relate each message to the user who wrote it. The data are not physically redundant, you're only seeing the result of the INNER JOIN. You should return the result set as it is and then figure out how you want to show it wherever you are using it (UI, report, etc.).
      singulars
      1. POHow do I avoid redundant data fields in the result set when using JOINs?
      1. USCᴏʀʏ
    3. COWhat is your database access middleware? Rather than a single 'recordset' of rows and columns, it may support 'relational' resultsets (DataSets with relationships in ADO.NET) or hierarchical resultsets (hierarchical recordset in ADO classic).
      singulars
      1. POHow do I avoid redundant data fields in the result set when using JOINs?
      1. USonedaywhen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload