Databases
StackOverflow2013
Postsdbo
POComplex SQL Join help!
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComplex SQL Join help!
    primarykey
    data
    text
    <p>(For a quick recap please see sections titled , DATA. Can only get results in FIGURE A, and FIGURE B, what I want is DESIRED RESULTS, tried everything posted in the answers section.)</p> <p>All, I have been working hard to start smartly using joins in my application. Boy it took me a while but I think I am getting the hang of this, but I can't get this query to work. </p> <p>Please help me! Here is the join as is:</p> <pre><code>SELECT TOP (100) PERCENT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title, a.Description, a.ApplicableDate, a.LocalId, at.Name AS AccomplishmentTypeName, a.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUserGroupName FROM dbo.Accomplishment AS a INNER JOIN dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId INNER JOIN dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id INNER JOIN dbo.Area AS al ON al.Id = aal.AreaId INNER JOIN dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id INNER JOIN dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId INNER JOIN dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId LEFT OUTER JOIN dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId WHERE (ug.LocalId = 2) AND (ugo.LocalId &lt;&gt; 2) AND (ugto.LocalId = 4) ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName </code></pre> <p>Now this is kind of complex, but what I am doing is select info from all these related tables where there is an accomplishment, which has a user associated with it where that user has a usergroup localId = 2, but then if they do have that, I also want to return all the usergroups that user is in where the usertype.localid = 4.</p> <p>Let me give some example data to hopefully make this clear, right now I have 3 accomplishments in the database that look like this:</p> <p><strong><em>-------(DATA)</em></strong></p> <pre><code>Accomplishment 1: User: John Usergroups: Group A (Of UserGroupType 2), Group B (Of UserGroupType 3), Group C (Of UserGroupType 4), Group D (Of UsergroupType 4) Accomplishment 2: User: John Usergroups: Group A (Of UserGroupType 2), Group B (Of UserGroupType 3), Group C (Of UserGroupType 4), Group D (Of UsergroupType 4) Accomplishment 3: User: Sue Usergroups: Group A (Of UserGroupType 2) </code></pre> <p>Now my above Join results 4 rows :</p> <p><strong><em>-------(FIGURE A):</em></strong></p> <pre><code> Accomplishment 1, John, Group A, Group C Accomplishment 1, John, Group A, Group D Accomplishment 2, John, Group A, Group C Accomplishment 2, John, Group A, Group D </code></pre> <p>Now this is correct insofar as there is a row for each userGroup the user has that has a usergrouptype.localid of 4, however, how can I make it so that it also displays Accomplishment 3?? I first started with all the joins being inner joins, and then thought making the last few left joins would take care of it to return the user even if it has no usergroups of usergrouptype localid 4, but it doesn't. Basically I want it to return any accomplishments of a user who is in group A, and if they have any usergroups that are of usergrouptype 4, return all those usergroups as well.</p> <p>Any thoughts? And if I am not being clear please let me know, this is super complex and I may not have explained enough.</p> <p><strong>EDIT:</strong></p> <p>(For a quick recap please see sections titled , DATA. Can only get results in FIGURE A, and FIGURE B, what I want is DESIRED RESULTS, tried everything posted in the answers section.)</p> <p>Both HLGEM and Tom H.s results are the same, and closer to what I need, but still off. Now I get 9 results in Figure B.</p> <p><strong><em>-------(FIGURE B):</em></strong></p> <pre><code>Accomplishment 1, John, Group A, Group C Accomplishment 1, John, Group A, Group D Accomplishment 1, John, Group A, Null Accomplishment 1, John, Group A, Group B (Group B is UsergroupType 3) Accomplishment 3, Sue, Group A, Null Accomplishment 2, John, Group A, Group C Accomplishment 2, John, Group A, Group D Accomplishment 2, John, Group A, Null Accomplishment 2, John, Group A, Group B (Group B is UsergroupType 3) </code></pre> <p>So for some reason it's including a null row for John even though he has usergroups that are of usergrouptype 4, and its including a row with group B which is of usergrouptype 3 and shouldn't be showing up.</p> <p><strong>EDIT AGAIN:</strong></p> <p>(For a quick recap please see sections titled , DATA. Can only get results in FIGURE A, and FIGURE B, what I want is DESIRED RESULTS, tried everything posted in the answers section.)</p> <p>Yep I am truely stumped by this, I have tried pretty much every combination of putting some stuff in the where clause and it either is 4 rows without accomplishment 3, or 9 rows where user John gets two extra rows per accomplishment, or 6 Rows without accomplish 3 and an extra row for usergrouptype 3 usergroup. What's even more baffling is that adding the <code>ugto.localid = 4</code> to the last line doesn't seem to have any affect on the results. It's showing my a usergroup which has usergrouptype 3 and I don't see anywhere in the query where that is okay.</p> <p><strong>EDIT 5/02/10</strong></p> <p>(For a quick recap please see sections titled , DATA. Can only get results in FIGURE A, and FIGURE B, what I want is DESIRED RESULTS, tried everything posted in the answers section.)</p> <p>I think what I want my be impossible with how it's set up?? Anyway the result I want is these 5 rows:</p> <p><strong><em>-------(DESIRED RESULT):</em></strong></p> <pre><code>Accomplishment 1, John, Group A, Group C Accomplishment 1, John, Group A, Group D Accomplishment 2, John, Group A, Group C Accomplishment 2, John, Group A, Group D Accomplishment 3, Sue, Group A, Null </code></pre> <p>Here is a list of everything I have tried and the results they give: (Please forgive the crappy SSMS formatting)</p> <p>Everything below is based on this base query:</p> <p><strong>Starting Query/Attempt 1:</strong></p> <pre><code>SELECT TOP (100) PERCENT a.Id, a.DateCreated, a.DateModified, a.LastUpdatedBy, a.AccomplishmentTypeId, a.Title, a.Description, a.ApplicableDate, a.LocalId, at.Name AS AccomplishmentTypeName, al.Name AS AreaName, u.FirstName, u.LastName, ug.Name AS UserGroupName, ugo.Name AS OtherUsergroupName FROM dbo.Accomplishment AS a INNER JOIN dbo.AccomplishmentType AS at ON at.Id = a.AccomplishmentTypeId INNER JOIN dbo.AccomplishmentArea AS aal ON aal.AccomplishmentId = a.Id INNER JOIN dbo.Area AS al ON al.Id = aal.AreaId INNER JOIN dbo.UserAccomplishment AS ua ON ua.AccomplishmentId = a.Id INNER JOIN dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId INNER JOIN dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId AND ugo.LocalId &lt;&gt; 2 LEFT OUTER JOIN dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId AND ugto.LocalId = 4 WHERE (ug.LocalId = 2) ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName </code></pre> <p><em>Result:</em> <strong>Figure B</strong> (See way above) <em>Why it's bad:</em> Nulls for John show up and usergroup of type 3 is showing up.</p> <p><strong>Attempt 2:</strong></p> <p>Attempt 1, minus all in-line join filtering, (still keeping left joins on the last two) and the following where statement:</p> <pre><code>WHERE (ug.LocalId = 2) AND (ugo.LocalId &lt;&gt; 2 OR ugo.LocalId is null) AND (ugto.LocalId = 4 OR ugto.LocalId is null) </code></pre> <p><em>Result:</em> <strong>Figure A</strong> (See way above) <em>Why it's bad:</em> Doesn't include Accomplishment 3</p> <p><strong>Attempt 3:</strong></p> <p>Same as attempt 1 with changes starting underneath this line: </p> <pre><code> dbo.[User] AS u ON u.Id = ua.UserId INNER JOIN </code></pre> <p>Changes under that line:</p> <pre><code> dbo.UserUserGroup AS uug ON uug.UserId = u.Id INNER JOIN dbo.UserGroup AS ug ON ug.Id = uug.UserGroupId AND ug.LocalId = 2 INNER JOIN dbo.UserGroupType AS ugt ON ugt.Id = ug.UserGroupTypeId INNER JOIN dbo.UserUserGroup AS uugo ON uugo.UserId = u.Id LEFT OUTER JOIN dbo.UserGroup AS ugo ON ugo.Id = uugo.UserGroupId AND ugo.LocalId &lt;&gt; 2 LEFT OUTER JOIN dbo.UserGroupType AS ugto ON ugto.Id = ugo.UserGroupTypeId WHERE (ugto.LocalId = 4) ORDER BY a.DateCreated DESC, u.LastName, u.FirstName, u.Id, UserGroupName </code></pre> <p>(For a quick recap please see sections titled , DATA. Can only get results in FIGURE A, and FIGURE B, what I want is DESIRED RESULTS, tried everything posted in the answers section.)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSventoryMang
    1. USSventoryMang
    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. COThe biggest issue I see here is the poor formatting of the SQL. I can almost guarantee that if you format it nicer you'll have a much easier time getting it to work correctly.
      singulars
      1. POComplex SQL Join help!
      1. USChaosPandion
    2. CONot sure what you mean, I mean it's not how I type it, but SQL management studio converts it this format when I execute the SQL, but I also have no problem reading it this way. How would you format it?
      singulars
      1. POComplex SQL Join help!
      1. USSventoryMang
    3. COI've never liked how SSMS formats SQL myself. Putting the JOIN keywords at the end of the lines makes no sense to me at all. They don't apply to the criteria on the line where they are.
      singulars
      1. POComplex SQL Join help!
      1. USTom H
 

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