asked on

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

rafrancisco

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

Scott Pletcher

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

saabStory

ASKER

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!

Scott Pletcher

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.

Scott Pletcher

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.

saabStory

ASKER

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.

Scott Pletcher

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.