query question - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

query question

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-07-24 : 16:38:23

I only want to bring back one record for each model, so I want to look at the modelID and and bring back a only one record per model.

SELECT modelSet.modelsSetName, modelSet.modelID, modelPic.modelPic, modelPic.modelPicDefault, modelPic.modelPicsID, modelSet.modelsSetID,
modelPic.modelPicMain, ShoppingCart.DateAdded, modelSet.Price, models.firstName, models.alias, models.heightFeet, models.heightInches, models.weight,
models.bust, models.waist, models.hips

FROM helixpo_models.modelSet INNER JOIN
helixpo_models.ShoppingCart ON modelSet.modelsSetID = ShoppingCart.modelsSetID AND ABS(DATEDIFF(n, ShoppingCart.DateAdded, GETDATE())) > 10 INNER JOIN
helixpo_models.modelPic ON modelSet.modelsSetID = modelPic.modelsSetID INNER JOIN
helixpo_models.models ON modelSet.modelID = models.modelID
WHERE (modelSet.isMainSet = 0) AND (modelSet.AvailDate < GETDATE()) AND (models.isActive = 1) AND (models.isHomePage = 1) AND (modelPic.modelPicDefault = 1) AND
(modelPic.modelPicMain = 1) AND (modelSet.isSold = 0) AND (modelSet.isActive = 1)
UNION
SELECT modelSet_1.modelsSetName, modelSet_1.modelID, modelPic_1.modelPic, modelPic_1.modelPicDefault, modelPic_1.modelPicsID, modelSet_1.modelsSetID,
modelPic_1.modelPicMain, ShoppingCart_1.DateAdded, modelSet_1.Price, models_1.firstName, models_1.alias, models_1.heightFeet, models_1.heightInches,
models_1.weight, models_1.bust, models_1.waist, models_1.hips

FROM helixpo_models.modelSet AS modelSet_1 INNER JOIN
helixpo_models.models AS models_1 ON modelSet_1.modelID = models_1.modelID INNER JOIN
helixpo_models.modelPic AS modelPic_1 ON modelSet_1.modelsSetID = modelPic_1.modelsSetID LEFT OUTER JOIN
helixpo_models.ShoppingCart AS ShoppingCart_1 ON modelSet_1.modelsSetID = ShoppingCart_1.modelsSetID
WHERE (modelSet_1.isMainSet = 0) AND (modelSet_1.AvailDate < GETDATE()) AND (models_1.isActive = 1) AND (models_1.isHomePage = 1) AND
(modelPic_1.modelPicDefault = 1) AND (modelPic_1.modelPicMain = 1) AND (ShoppingCart_1.DateAdded IS NULL) AND (modelSet_1.isSold = 0) AND (modelSet_1.isActive = 1)

Dave
Helixpoint Web Development
http://www.helixpoint.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-24 : 17:46:06

it is not obvious what are you trying to achieve.

but if you want to retrieve only one record (distinct) record per model, use DISTINCT and|or GROUP BY statements. And make sure what you put in select list in order for your GROUP BY statement to work properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources