Databases
StackOverflow2013
Postsdbo
POMySQL - 3 tables, is this complex join even possible?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - 3 tables, is this complex join even possible?
    primarykey
    data
    text
    <p>I have three tables: users, groups and relation.</p> <ul> <li>Table users with fields: usrID, usrName, usrPass, usrPts</li> <li>Table groups with fields: grpID, grpName, grpMinPts</li> <li>Table relation with fields: uID, gID</li> </ul> <p>User can be placed in group in two ways:</p> <ul> <li>if collect group minimal number of points (users.usrPts > group.grpMinPts ORDER BY group.grpMinPts DSC LIMIT 1)</li> <li>if his relation to the group is manually added in relation tables (user ID provided as uID, as well as group ID provided as gID in table named relation)</li> </ul> <p>Can I create <strong>one single</strong> query, to determine for every user (or one specific), which group he belongs, but, manual relation (using relation table) should have higher priority than usrPts compared to grpMinPts? Also, I do not want to have one user shown twice (to show his real group by points, but related group also)... </p> <p>Thanks in advance! :) I tried:</p> <pre><code>SELECT * FROM users LEFT JOIN (relation LEFT JOIN groups ON (relation.gID = groups.grpID) ON users.usrID = relation.uID </code></pre> <p>Using this I managed to extract specified relations (from relation table), but, I have no idea how to include user points, respecting above mentioned priority (specified first). I know how to do this in a few separated queries in php, that is simple, but I am curious, can it be done using one single query?</p> <p>EDIT TO ADD:</p> <p>Thanks to really educational technique using <a href="http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php" rel="nofollow" title="w3resource example of using coalesce">coalesce</a> @GordonLinoff provided, I managed to make this query to work as I expected. So, here it goes:</p> <pre><code>SELECT o.usrID, o.usrName, o.usrPass, o.usrPts, t.grpID, t.grpName FROM ( SELECT u.*, COALESCE(relationgroupid,groupid) AS thegroupid FROM ( SELECT u.*, ( SELECT grpID FROM groups g WHERE u.usrPts &gt; g.grpMinPts ORDER BY g.grpMinPts DESC LIMIT 1 ) AS groupid, ( SELECT grpUID FROM relation r WHERE r.userUID = u.usrID ) AS relationgroupid FROM users u )u )o JOIN groups t ON t.grpID = o.thegroupid </code></pre> <p>Also, if you are wondering, like I did, is this approach faster or slower than doing three queries and processing in php, the answer is that this is slightly faster way. Average time of this query execution and showing results on a webpage is 14 ms. Three simple queries, processing in php and showing results on a webpage took 21 ms. Average is based on 10 cases, average execution time was, really, a constant time.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDekiB
    1. USDekiB
    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
    1. VO
      singulars
      1. POMySQL - 3 tables, is this complex join even possible?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL - 3 tables, is this complex join even possible?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL - 3 tables, is this complex join even possible?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 For explaining/trying and hopefully learning from a good answer! :)
      singulars
      1. POMySQL - 3 tables, is this complex join even possible?
      1. USnickhar
    2. COOf course, every correct answer is a lesson, more or less good lesson. It is important that answer stimulates me to think, like Gordon Linoff's do.
      singulars
      1. POMySQL - 3 tables, is this complex join even possible?
      1. USDekiB
 

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