StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdvanced SQL Query Struggles
text
Body
copied!<p>so basically the issue I am having at the moment is I cannot work out how to do an advanced JOIN query in SQL Server.</p> <p>I have the following tables:</p> <ul> <li>incidents</li> <li>consequences</li> <li>incident_consequence (join)</li> <li>severities</li> </ul> <p>My query needs to pull each incident record from the database and determine its severity which is done via the consequences that are associated with the incident (in the join table incident_consequence). Each consequence record has a severity foreign key associated with it. Once I have all the consequences associated to the incident I need to only return the one that has the highest severity.level integer value.</p> <p>So as you might be seeing here, I need to join the incident_consequences.incident_id to the incidents.id table for all the relationships and from there join the incident_consequence.consequence_id to consequences.id then the severity to the consequences.severity, etc, etc.</p> <p>I am having a lot of trouble doing this and am hoping a smart person that knows SQL really well will be able to help me out with this query.</p> <p>Here is what I have so far:</p> <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT dbo.incidents.id, MAX(severities1.[level]) AS severities FROM dbo.incidents INNER JOIN dbo.incident_consequence ON dbo.incidents.id = dbo.incident_consequence.incident_id INNER JOIN dbo.consequences ON dbo.incident_consequence.consequence_id = dbo.consequences.id INNER JOIN dbo.severities AS severities1 ON dbo.consequences.severity = severities1.id LEFT OUTER JOIN dbo.severities AS severities2 ON severities1.id = severities2.id AND severities1.[level] < severities2.[level] WHERE (severities2.id IS NULL) GROUP BY dbo.incidents.id, severities1.[level] </code></pre> <p><img src="https://i.stack.imgur.com/NsgTH.png" alt="sql join visual"></p> <p>This returns:</p> <p><img src="https://i.stack.imgur.com/oV1F5.png" alt="result of my join query"></p> <p>What I need is:</p> <p><img src="https://i.stack.imgur.com/ip9CK.png" alt="enter image description here"></p> <p>Really appreciate any help I can get with this one!</p> <p>Cheers, Ben</p>

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