[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Need help with a query

I'm working on a web page where we query a database for publication information.  In the application, they can select a city and/or a division to search for pubs that have been assigned to either.  The end result is a list of email addresses that we can then use for press releases and such.  

On the back side, and this is the root of my problem, the publications have a one to one relationship with the division assignments, but.... the publications have a one to many relationship with the offices.  Consequently, when I run a search, the divisions work fine but, should a publication be assigned to 5 cities, I get back 5 records where I only need one.  There aren't too many publications assigned to more than one citiy but when there is, I can get single records for most of my results and then have 6 of one pub just because it is assigned to multiple cities.  I know what I need to do - just not how to do it.

Hope that is clear.  Here is the query I'm using so far.  Thanks for the help.
*******************************************************
SELECT DISTINCT dbo.sam_EntityInfo.entityName, dbo.sam_PressContactInfo.pressEmail, dbo.sam_EntityOfficeRelationships.officeID, dbo.sam_EntityDivisionRelationships.divisionID FROM dbo.sam_EntityInfo INNER JOIN dbo.sam_PressContactInfo ON dbo.sam_EntityInfo.entityID = dbo.sam_PressContactInfo.entityID INNER JOIN dbo.sam_EntityOfficeRelationships ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityOfficeRelationships.entityID INNER JOIN dbo.sam_EntityDivisionRelationships ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityDivisionRelationships.entityID
WHERE (dbo.sam_EntityDivisionRelationships.divisionID = 4) OR (dbo.sam_EntityDivisionRelationships.divisionID = 999) AND (dbo.sam_EntityOfficeRelationships.officeID = 1) ORDER BY dbo.sam_EntityInfo.entityName
0
saabStory
Asked:
saabStory
  • 5
  • 2
1 Solution
 
rafranciscoCommented:
Try this one:

SELECT DISTINCT dbo.sam_EntityInfo.entityName, dbo.sam_PressContactInfo.pressEmail, dbo.sam_EntityOfficeRelationships.officeID, dbo.sam_EntityDivisionRelationships.divisionID
FROM dbo.sam_EntityInfo INNER JOIN dbo.sam_PressContactInfo
                                                    ON dbo.sam_EntityInfo.entityID = dbo.sam_PressContactInfo.entityID
                                     INNER JOIN dbo.sam_EntityOfficeRelationships
                                                    ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityOfficeRelationships.entityID
                                     INNER JOIN dbo.sam_EntityDivisionRelationships
                                                    ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityDivisionRelationships.entityID
WHERE ((dbo.sam_EntityDivisionRelationships.divisionID = 4) OR (dbo.sam_EntityDivisionRelationships.divisionID = 999)) AND (dbo.sam_EntityOfficeRelationships.officeID = 1)
AND (dbo.sam_EntityOfficeRelationships.officeID = (SELECT MIN(officeID) FROM dbo.sam_EntityOfficeRelationships
WHERE dbo.sam_EntityInfo.entityID = dbo.sam_EntityOfficeRelationships.entityID)
ORDER BY dbo.sam_EntityInfo.entityName
0
 
Scott PletcherSenior DBACommented:
Is the officeID different for each city?  If it is, then, since you don't want mutiple rows per division/publication, you probably don't need officeID in the query, so just remove it and the join to dbo.sam_EntityOfficeRelationships.
0
 
Scott PletcherSenior DBACommented:
You could also do something like this:


SELECT dbo.sam_EntityInfo.entityName,
      dbo.sam_PressContactInfo.pressEmail,
      MAX(dbo.sam_EntityOfficeRelationships.officeID) AS [Last Office],
dbo.sam_EntityDivisionRelationships.divisionID
FROM dbo.sam_EntityInfo
INNER JOIN dbo.sam_PressContactInfo ON dbo.sam_EntityInfo.entityID = dbo.sam_PressContactInfo.entityID
INNER JOIN dbo.sam_EntityOfficeRelationships ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityOfficeRelationships.entityID
INNER JOIN dbo.sam_EntityDivisionRelationships ON dbo.sam_EntityInfo.entityID = dbo.sam_EntityDivisionRelationships.entityID
WHERE (dbo.sam_EntityDivisionRelationships.divisionID = 4) OR
      (dbo.sam_EntityDivisionRelationships.divisionID = 999) AND
      (dbo.sam_EntityOfficeRelationships.officeID = 1)
GROUP BY dbo.sam_EntityInfo.entityName
ORDER BY dbo.sam_EntityInfo.entityName, dbo.sam_PressContactInfo.pressEmail, dbo.sam_EntityDivisionRelationships.divisionID


Btw, table aliases can be tremendously helpful in coding queries, reducing typing and related errors, especially if you have to change them later.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
saabStoryAuthor Commented:
Thank you - it worked after a fashion. I had to include dbo.sam_PressContactInfo.pressEmail and dbo.sam_EntityDivisionRelationships.divisionID in the GROUP BY clause to make it work - kept getting an error otherwise.  Why is that the case?

You're right of course about the aliases - something I should be doing for my own sanity if nothing else - I'll have to remember that for the future.

Thanks again for the help - I really appreciate it!
0
 
Scott PletcherSenior DBACommented:
D'OH -- I meant to add those columns to the GROUP BY but added them to the ORDER BY instead ... sorry!!


SQL "understands" that each column represents a *single* value, so when you attempt to write a query that would have more than one value going into a single column SQL -- such as with un-grouped by columns -- SQL gives an error.  I'll post a more specific example ASAP.
0
 
Scott PletcherSenior DBACommented:
Table1: (col1, col2, col3)

Data: (1, 1, 1)
    (1, 2, 3)
    (1, 3, 5)


Now suppose you try to do:

SELECT col1, col2, SUM(col3)
FROM table1
GROUP BY col1

For col1 = 1, there are three different values for col2 -- which one should SQL use?  It doesn't know because you didn't tell it.  And it *won't* guess.  So it gives an error, as it should.

Note that SUM(col3) yields *one* value even though there are three different values originally in col3.  Similarly, if you did this:

SELECT col1, MAX(col2), SUM(col3)
FROM table1
GROUP BY col1

SQL should now be able to run the query, since you've specified *which* col2 value you want.

Finally, note that even if the data in the table was just this:

Data: (1, 1, 1)

SQL will not run the first query.  SQL considers it "logically incorrect", even if at the moment there is only one row in the table so theoretically it doesn't matter that you haven't specified which col2.
0
 
saabStoryAuthor Commented:
That makes sense - more so than several books I've looked through.  I really appreciate the help with the query and the explanation to boot.  It's often intimidating to ask questions as you don't know what someone is likely to consider foolish or not.  Never felt that way here - especially in the SQL group - I've always been treated extremely well and everyone really seems to enjoy sharing.  I always get my question answered and something to mull over and learn anew.  Thanks again.
0
 
Scott PletcherSenior DBACommented:
Thank you *very much* for those comments.  I do appreciate hearing that.  

SQL is not as complex as some people try to make it out to be.  And, as bizarre as SQL's behavior sometimes looks on the front end, the rules it uses almost always have a logical reason behind them.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now