StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am not sure why you are using a <code>GROUP BY</code> on your query. A <code>GROUP BY</code> is typically used when you have an aggregate function in your query. But I would say you might be able to use the following:</p> <pre><code>select * from users u left join users_positions up on u.PRN = up.PRN left join positions p on up.id = p.position left join organizations o on p.org_title = o.id left join divisions d on p.division = d.id </code></pre> <p>Edit, with your sample data that you provided, here is a sample of a query that you can use:</p> <pre><code>select u.prn, u.firstname, u.age, o.name orgName, p.name positionName, d.name divisionName from users u left join users_positions up on u.PRN = up.PRN left join organizations o on up.Organization_ID = o.id left join positions p on up.Positionn_ID = p.id left join divisions d on up.Division_ID = d.id </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/7434b/9" rel="nofollow">SQL Fiddle with Demo</a></p> <p>The result of this query is:</p> <pre><code>| PRN | FIRSTNAME | AGE | ORGNAME | POSITIONNAME | DIVISIONNAME | ------------------------------------------------------------------------------ | 001 | david | 24 | test 1 | Programmer | Sale Department | | 001 | david | 24 | test 2 | Manager | Administration Department | | 052 | george | 27 | test 1 | Programmer | IT Department | </code></pre> <p>If you only want to return one organization, etc per user then you need to decide how you will determine the correct result to return. For example, if you only want to return the <code>max(Organization_ID)</code> per user, then you can use something similar to this:</p> <pre><code>select u.prn, u.firstname, u.age, o.name orgName, p.name positionName, d.name divisionName from users u left join ( select max(Organization_ID) Organization_ID, PRN from users_positions group by PRN ) up1 on u.PRN = up1.PRN left join users_positions up2 on up1.PRN = up2.prn and up1.Organization_ID = up2.Organization_ID left join organizations o on up2.Organization_ID = o.id left join positions p on up2.Positionn_ID = p.id left join divisions d on up2.Division_ID = d.id </code></pre> <p>See <a href="http://sqlfiddle.com/#!2/7434b/8" rel="nofollow">SQL Fiddle with Demo</a></p> <p>Result:</p> <pre><code>| PRN | FIRSTNAME | AGE | ORGNAME | POSITIONNAME | DIVISIONNAME | ------------------------------------------------------------------------------ | 001 | david | 24 | test 2 | Manager | Administration Department | | 052 | george | 27 | test 1 | Programmer | IT Department | </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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