IS there a way to do this? - 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)

IS there a way to do this?

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 12:46:37

I need to run a query that only pulls all titles that have nothing but a 0 value.

Sample Data:

Title value
Title1 0
Title1 0
Title2 1
Title2 0
Title3 0
Title3 1
Results Wanted:
Title1

Only title 1 would qualify since all it's value only contain 0. A where clause of "where value = 0" gives me title 2 and 3 as well, which I don't want since they also contain other values.

How would a query like this work? What do you fileter it on?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-17 : 12:47:55

yes it can be done. just from the value of the data you are giving just do
a select DISTINCT FROM table where value = 0

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 13:18:04

I created this query:

select distinct title, tblA.value
from tblreports
Inner Join tblA On tblReports.reportid = tblA.reportid
where tblA.ViewCount = 0
Order By title asc

And still end up with results that contain titles that have a value other than 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-17 : 13:25:52

[code]
DECLARE @t table(Title char(6), Val tinyint)

INSERT INTO @t VALUES('Title1', 0)
INSERT INTO @t VALUES('Title1', 0)
INSERT INTO @t VALUES('Title2', 1)
INSERT INTO @t VALUES('Title2', 0)
INSERT INTO @t VALUES('Title3', 0)
INSERT INTO @t VALUES('Title3', 1)

SELECT tv.*
FROM @t tv
LEFT JOIN (SELECT DISTINCT Title FROM @t WHERE Val <> 0) dt
ON tv.Title = dt.title
WHERE dt.Title IS NULL

Title Val
------ ----
Title1 0
Title1 0
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 13:44:43

I'm sorry - I wasn't clear, my fault. I have 2 tables:

tableA
RptID Title
1 Title1
2 Title1
3 Title2
4 Title2
5 Title3
6 Title3

TableB
BID RptID Value
1 1 0
2 2 0
3 3 0
4 4 1
5 5 1

Results:
Title1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 13:51:11

[code]
SELECT a.Title
FROM TableA a
JOIN TableB b
ON b.RptID=a.RptID
GROUP BY a.Title
HAVING SUM(CASE WHEN b.Value <> 0 THEN 1 ELSE 0 END) =0
[/code]

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 14:07:08

That didn't work either, no errors, just returned titles that had non-zero values

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-17 : 14:08:14

quote:
Originally posted by ann

I'm sorry - I wasn't clear, my fault. I have 2 tables:

tableA
RptID Title
1 Title1
2 Title1
3 Title2
4 Title2
5 Title3
6 Title3

TableB
BID RptID Value
1 1 0
2 2 0
3 3 0
4 4 1
5 5 1

Results:
Title1

It doesn't matter how many tables you have, just modify my query to do the joins.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:19:15

quote:
Originally posted by ann

That didn't work either, no errors, just returned titles that had non-zero values

its working for me. show query used by you

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 14:29:37

visakh16 - it DID work - I made a muckup on the query the first time I tried.

Thanks so much to all that helped - I really appreciated it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:33:18

welcome

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 14:45:12

Ok - so I started going through the data and it didn't work - here is the query that I am using:

SELECT a.Title, a.REportID
FROM tblreports a
JOIN tblSections b
ON b.ReportiD=a.ReportID
GROUP BY a.Title, a.ReportID
HAVING SUM(CASE WHEN b.ViewCount <> 0 THEN 1 ELSE 0 END) =0
Order By Title

Most of it is right, but not all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:48:46

quote:
Originally posted by ann

Ok - so I started going through the data and it didn't work - here is the query that I am using:

SELECT a.Title~~, a.ReportID~~
FROM tblreports a
JOIN tblSections b
ON b.ReportiD=a.ReportID
GROUP BY a.Title~~, a.ReportID~~
HAVING SUM(CASE WHEN b.ViewCount <> 0 THEN 1 ELSE 0 END) =0
Order By Title

Most of it is right, but not all

this is not what i gave. modify like above

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 14:50:13

The problem is when I add the reportID, which I need because I need to run other information based on it. If I remove the ReportID it works

Doesn't work
SELECT a.Title, a.REportID
FROM tblreports a
JOIN tblSections b
ON b.ReportiD=a.ReportID
GROUP BY a.Title, a.ReportID
HAVING SUM(CASE WHEN b.ViewCount <> 0 THEN 1 ELSE 0 END) =0
Order By Title

Works
SELECT a.Title
FROM tblreports a
JOIN tblSections b
ON b.ReportiD=a.ReportID
GROUP BY a.Title
HAVING SUM(CASE WHEN b.ViewCount <> 0 THEN 1 ELSE 0 END) =0
Order By Title

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:54:55

ok..again another change of requirement

..in that case use the below


SELECT Title,REportID,..other columns if you want
FROM
(
SELECT a.Title, a.REportID,..other columns if you want,
SUM(CASE WHEN b.ViewCount <> 0 THEN 1 ELSE 0 END) OVER (PARTITION BY a.Title) AS NonZeroCnt
FROM tblreports a
JOIN tblSections b
ON b.ReportiD=a.ReportID
)t
WHERE NonZeroCnt=0

ann
Posting Yak Master

220 Posts

Posted - 2009-04-17 : 16:16:35

I'm sorry :) but I really, REALLY do appreciate your help - that query worked great! I was beginning to think I'd have to write some weird looping logic in my code to get what I want :)

have another question and it's another query I have to run regarding titles. So in the title fields I might have titles named something like this:
TableReports
RptID Date(datetime) Title(varchar)
1 01-01-2009 End of Day Accounts 12/31/08
2 01-02-2009 End of Day Accounts 1/01/09
3 01-03-2009 End of Day Accounts 1/01/09
4 01-04-2009 End of Day Accounts 1/01/09
5 01-05-2009 End of Day Accounts 1/01/09

Result:
5 01-04-2009 End of Day Accounts 1/01/09

I have to search for a title based on the latest date. The problem is that the "End of Day Accounts" in the example above are all considered one report, so the search query param. would be based on Title "End of Day Accounts" irrespective of the date used in the title. Can this be done?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 16:24:29

Simpler

SELECT a.Title
FROM TableA as a
INNER JOIN TableB as b ON b.RptID = a.RptID
GROUP BY a.Title
HAVING MAX(b.Value) = 0

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:14:12

quote:
Originally posted by ann

I'm sorry :) but I really, REALLY do appreciate your help - that query worked great! I was beginning to think I'd have to write some weird looping logic in my code to get what I want :)

have another question and it's another query I have to run regarding titles. So in the title fields I might have titles named something like this:
TableReports
RptID Date(datetime) Title(varchar)
1 01-01-2009 End of Day Accounts 12/31/08
2 01-02-2009 End of Day Accounts 1/01/09
3 01-03-2009 End of Day Accounts 1/01/09
4 01-04-2009 End of Day Accounts 1/01/09
5 01-05-2009 End of Day Accounts 1/01/09

Result:
5 01-04-2009 End of Day Accounts 1/01/09

I have to search for a title based on the latest date. The problem is that the "End of Day Accounts" in the example above are all considered one report, so the search query param. would be based on Title "End of Day Accounts" irrespective of the date used in the title. Can this be done?

do you mean this?


SELECT r.*
FROM TableReports r
WHERE Title LIKE @YourTitle + '%'
AND Date=(SELECT MAX(Date) 
            FROM TableReports 
           WHERE  Title LIKE @YourTitle + '%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources