Select Query - 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 2000 Forums

Transact-SQL (2000)

Select Query

Author

Topic

jrp210
Starting Member

7 Posts

Posted - 2003-08-13 : 11:47:07

I have two tables with the design below:

Company table
companyid
metricid
metricvalue

Metric table
metricid
metricname

I would like to construct a query / stored procedure that would return a recordset with the companyid, metricname, metricid, and metric value based on criteria supplied by the user.

My query is returning records if one of the WHERE criteria is met because of the OR operator. I need to have all criteria met before returning the record.

WHERE (MetricID = 1) AND (MetricValue > 0) OR
(Metrics.MetricID = 4) AND (MetricValue > 0)

Will this construct need to be in the JOIN? Any help is appreciated.

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-13 : 12:33:44

SELECT CompanyID, Metricname, MetricID, MetricValue

FROM Company C
INNER JOIN Metric M ON C.MetricID = M.MetricID

WHERE MetricValue > 0

-- alternatively WHERE MetricID = 1 or MetricID = 4

Sam

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-13 : 14:58:32

WHERE (MetricID = 1 AND MetricValue > 0) OR
(Metrics.MetricID = 4 AND MetricValue > 0)

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-14 : 02:55:51

SELECT
companyid, metricname, c.metricid, c.metricvalue
FROM
Company
INNER JOIN
Metric M ON C.MetricID = M.MetricID
WHERE
MetricValue > 0 and
c.metricid in (1,4)

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources