OK...so I suck at joins. At least I admit it.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OK...so I suck at joins. At least I admit it.

Author

Topic

storemike
Starting Member

10 Posts

Posted - 2004-08-30 : 11:31:35

I'm not even sure a join is the best solution. I'm building a classifieds site for a client who wants users to be able to flag an ad for various reasons. If the ad has more than 5 flags, the ad won't be displayed and the admin will be notified. So far all is good, except checking TBL_FLAGS for the AD_ID when displaying a list of ads for browsing or displaying search results from TBL_ADS. I was thinking of taking the weasy way out by adding a field to the TBL_ADS to hold the number of flags, but I figure it's time I grow up and do things right. I looked at a Celko book, but that didn't help too much. Could be the looming deadline. Any help would be greatly appreciated.

Thanks,
Mike

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-30 : 11:54:53

Depending on the size of the system I would actually advice to add a field that gets incremented but that's just me I guess. The good thing about this is that it's usually quite fast compared to doing joins (also repending on the read/write-ratio). To give some more specific help we would have to know some DDL and some more details I belive...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-30 : 15:45:27

take it step by step:

This will return the # of flags per AD_ID:

SELECT AD_ID, COUNT(*) as FlagCount
FROM TBL_FLAGS
GROUP BY AD_ID

Call the above "SQL 1".

YOu can just join to this as a derived table to determine which adds to have been flagged over 5 times and should not be displayed:

SELECT A.*, B.FlagCount
FROM Adds A
INNER JOIN (SQL 1) B
ON
A.AD_ID=B.AD_ID
WHERE
B.FlagCount >=5

To return only adds with either no flags or less than 5 (i.e., those that are OK to display) you would use a LEFT OUTER JOIN -- since there may be less than 5 flags, or none at all:

SELECT
A.*, B.FlagCount
FROM
Adds A
LEFT OUTER JOIN (SQL 1) B
ON
A.AD_ID=B.AD_ID
WHERE
B.AD_ID is null OR
B.FlagCount < 5

Does this help at all?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources