Small Issue not sure if this can be done

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

General SQL Server Forums

New to SQL Server Programming

Small Issue not sure if this can be done

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2008-07-15 : 09:54:36

I have a flat table created like this:

CREATE TABLE [dbo].[DocumentImageFormatLinks](
[Document_ID] [int],
[ImageFormatID] [int]

It has over three million rows, now for each Document_ID it can have anywhere between 1 - 19 as a ImageFormatID but it can also have more then one different ImageFormatID.

Now what i am trying to do is return the Document_ID's which have the ImageFormatID of 4 and 15 but dont have ImageFormatID 14.

Can anybody help me !!!!

Regards

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-15 : 10:00:58

select document_id
from DocumentImageFOrmatLinks
where imageformatid in(4,15)
and document_id not in(select document_id from DocumentImageFormatLinks where imageFormatID=14)

Mike
"oh, that monkey is going to pay"

lemondash
Posting Yak Master

159 Posts

Posted - 2008-07-15 : 10:21:47

Am sorry maybe i didn't explain myself. But the IN (4,15) is being used as a OR becuase it return values for each one but not as equal to both of them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 10:26:45

[code]-- Prepare sample data
DECLARE @Sample TABLE (DocID INT, FormatID INT)

INSERT @Sample
SELECT 1, 4 UNION ALL
SELECT 1, 15 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 14 UNION ALL
SELECT 2, 15 UNION ALL
SELECT 3, 4 UNION ALL
SELECT 4, 15

-- Correct result is DocID 1

-- Peso
SELECT DocID
FROM @Sample
WHERE FormatID IN (4, 14, 15)
GROUP BY DocID
HAVING MAX(CASE WHEN FormatID = 4 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN FormatID = 15 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN FormatID = 14 THEN 1 ELSE 0 END) = 0[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2008-07-15 : 10:36:08

[code]
SELECT document_id
FROM DocumentImageFOrmatLinks
AND document_id NOT IN (SELECT document_id FROM DocumentImageFormatLinks WHERE imageFormatID=14)
AND document_id IN (SELECT document_id FROM DocumentImageFormatLinks WHERE imageFormatID=15)
AND document_id IN (SELECT document_id FROM DocumentImageFormatLinks WHERE imageFormatID=4)

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 10:59:09

[code]SELECT Document_ID
FROM Sample
WHERE ImageFormatID IN (4,15,14)
GROUP BY Document_ID
HAVING SUM(CASE WHEN ImageFormatID=14 THEN 1 ELSE 0 END)=0
AND COUNT(DISTINCT ImageFormatID) =2[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 11:05:27

Visakh, where did that MERGE JOIN come from?

-- Peso
  |--Filter(WHERE:([Expr1004]=(1) AND [Expr1005]=(1) AND [Expr1006]=(0)))
       |--Stream Aggregate(GROUP BY:([DocID]) DEFINE:([Expr1004]=MAX([Expr1007]), [Expr1005]=MAX([Expr1008]), [Expr1006]=MAX([Expr1009])))
            |--Filter(WHERE:([FormatID]=(4) OR [FormatID]=(14) OR [FormatID]=(15)))
                 |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [FormatID]=(4) THEN (1) ELSE (0) END, [Expr1008]=CASE WHEN [FormatID]=(15) THEN (1) ELSE (0) END, [Expr1009]=CASE WHEN [FormatID]=(14) THEN (1) ELSE (0) END))
                      |--Sort(ORDER BY:([DocID] ASC))
                           |--Table Scan(OBJECT:(@Sample))

-- Visakh
  |--Filter(WHERE:([Expr1004]=(0) AND [Expr1005]=(2)))
       |--Compute Scalar(DEFINE:([DocID]=[DocID]))
            |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([DocID])=([DocID]), RESIDUAL:([DocID] = [DocID]))
                 |--Compute Scalar(DEFINE:([DocID]=[DocID]))
                 |    |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1013],0)))
                 |         |--Stream Aggregate(GROUP BY:([DocID]) DEFINE:([Expr1013]=Count(*)))
                 |              |--Sort(DISTINCT ORDER BY:([DocID] ASC, [FormatID] ASC))
                 |                   |--Table Spool
                 |                        |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [FormatID]=(14) THEN (1) ELSE (0) END))
                 |                             |--Table Scan(OBJECT:(@Sample), WHERE:([FormatID]=(4) OR [FormatID]=(14) OR [FormatID]=(15)))
                 |--Compute Scalar(DEFINE:([DocID]=[DocID]))
                      |--Stream Aggregate(GROUP BY:([DocID]) DEFINE:([Expr1004]=SUM([Expr1006])))
                           |--Sort(ORDER BY:([DocID] ASC))
                                |--Table Spool

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources