All IN

I'm populating a criteria list from a secondary table and want to pull back
the matches in the primary table. However I want to set up something like a
an AND criteria between the results.
My current query is:
SELECT Name, TopicID FROM PrimaryTable WHERE TopicID IN (SELECT TopicID FROM
SecondaryTable WHERE DataLabel=1)
However the subquery (SELECT TopicID FROM SecondaryTable WHERE DataLabel=1)
returns a result set of (1, 4, 11), and for another DataLabel value it might
return (5, 7). And the above query returns rows with the equivalent of an O
R
statement for TopicID, ie: it comes back with matches for TopicID=1 OR
TopicID=4 OR TopicID=11.
How can I phrase it so that it comes back with the AND equivalent (TopicID=1
AND TopicID=4 AND TopicID=11), so that only those matching ALL results that
are returned from the SecondaryTable query are listed.
TIA,
Dougdw
If I understood you need something like that
SELECT Name, TopicID FROM PrimaryTable
WHERE EXISTS (SELECT * FROM SecondaryTable
WHERE PrimaryTable.PK=SecondaryTable.PK AND
SecondaryTable .TopicID=1 AND SecondaryTable
.TopicID=4 AND SecondaryTable .TopicID=11)
"dw" <dw@.discussions.microsoft.com> wrote in message
news:66D124A1-470F-4DA4-A9A1-BAC44639551A@.microsoft.com...
> I'm populating a criteria list from a secondary table and want to pull
> back
> the matches in the primary table. However I want to set up something like
> a
> an AND criteria between the results.
> My current query is:
> SELECT Name, TopicID FROM PrimaryTable WHERE TopicID IN (SELECT TopicID
> FROM
> SecondaryTable WHERE DataLabel=1)
> However the subquery (SELECT TopicID FROM SecondaryTable WHERE
> DataLabel=1)
> returns a result set of (1, 4, 11), and for another DataLabel value it
> might
> return (5, 7). And the above query returns rows with the equivalent of an
> OR
> statement for TopicID, ie: it comes back with matches for TopicID=1 OR
> TopicID=4 OR TopicID=11.
> How can I phrase it so that it comes back with the AND equivalent
> (TopicID=1
> AND TopicID=4 AND TopicID=11), so that only those matching ALL results
> that
> are returned from the SecondaryTable query are listed.
> TIA,
> Doug|||What you want is called relational division, which is quite well explained
in the following article:
http://www.examnotes.net/gurus/articles/113.asp
Jacco Schalkwijk
SQL Server MVP
"dw" <dw@.discussions.microsoft.com> wrote in message
news:66D124A1-470F-4DA4-A9A1-BAC44639551A@.microsoft.com...
> I'm populating a criteria list from a secondary table and want to pull
> back
> the matches in the primary table. However I want to set up something like
> a
> an AND criteria between the results.
> My current query is:
> SELECT Name, TopicID FROM PrimaryTable WHERE TopicID IN (SELECT TopicID
> FROM
> SecondaryTable WHERE DataLabel=1)
> However the subquery (SELECT TopicID FROM SecondaryTable WHERE
> DataLabel=1)
> returns a result set of (1, 4, 11), and for another DataLabel value it
> might
> return (5, 7). And the above query returns rows with the equivalent of an
> OR
> statement for TopicID, ie: it comes back with matches for TopicID=1 OR
> TopicID=4 OR TopicID=11.
> How can I phrase it so that it comes back with the AND equivalent
> (TopicID=1
> AND TopicID=4 AND TopicID=11), so that only those matching ALL results
> that
> are returned from the SecondaryTable query are listed.
> TIA,
> Doug|||The results that are returned by the secondary query will be varying in
number, so I can't hard-code the equalities.
My existing query looks like:
SELECT tblDatasets.DS_ID, tblDatasets.DS_Label, tblDS_Topic_Xref.Topic_I
D
FROM tblDatasets INNER JOIN
tblDS_Topic_Xref ON tblDatasets.DS_ID =
tblDS_Topic_Xref.DS_ID INNER JOIN
tblTopics ON tblDS_Topic_Xref.Topic_ID =
tblTopics.Topic_ID INNER JOIN
tblGOOSMatrix_Topic_Xref ON tblTopics.Topic_ID =
tblGOOSMatrix_Topic_Xref.Topic_ID
WHERE (tblDS_Topic_Xref.Topic_ID IN
(SELECT tblGOOSMatrix_Topic_Xref.Topic_ID
FROM tblGOOSMatrix_Topic_Xref
WHERE
tblGOOSMatrix_Topic_Xref.GOOS_Matrix_ID = 1))
But the "IN" portion returns partial matches, just like an ORing of the
subquery results.
How can I modify it so that there is a logical ANDing of the subquery
results so that ALL "x" Topic_IDs are present?
Doug
"Uri Dimant" wrote:

> dw
> If I understood you need something like that
> SELECT Name, TopicID FROM PrimaryTable
> WHERE EXISTS (SELECT * FROM SecondaryTable
> WHERE PrimaryTable.PK=SecondaryTable.PK AND
> SecondaryTable .TopicID=1 AND SecondaryTab
le
> ..TopicID=4 AND SecondaryTable .TopicID=11)
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:66D124A1-470F-4DA4-A9A1-BAC44639551A@.microsoft.com...
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
Look up "Relational Division"; it is one of Codd's original eight
operators.|||Here are the table create statements for the tables I'm using:
CREATE TABLE tblDSType (
DSType_ID int IDENTITY(1,1) NOT NULL,
DS_Type nvarchar(100) NULL,
Description nvarchar(4000) NULL
)
GO
CREATE TABLE tblDS_DSType_Xref (
DS_DSType_ID int IDENTITY(1,1) NOT NULL,
DS_ID int NULL,
DSType_ID int NULL
)
GO
CREATE TABLE tblDS_Topic_Xref (
DS_Topic_ID int IDENTITY(1,1) NOT NULL,
DS_ID int NULL,
Topic_ID int NULL
)
GO
CREATE TABLE tblDataCenters (
DC_ID int IDENTITY(1,1) NOT NULL,
DC_Name nvarchar(120) NULL,
DC_URL nvarchar(255) NULL,
DC_ContactID int NULL,
b_GCOS bit NOT NULL,
b_GOOS bit NOT NULL,
b_GTOS bit NOT NULL,
Notes nvarchar(4000) NULL
)
GO
CREATE TABLE tblDatasets (
DS_ID int IDENTITY(1,1) NOT NULL,
DS_Label nvarchar(175) NULL,
DS_Description nvarchar(250) NULL,
DS_ContactID int NOT NULL,
DS_Country nvarchar(50) NULL,
DS_DataURL nvarchar(125) NULL,
DS_GosicDataURL nvarchar(125) NULL,
DS_ProgElem int NULL,
DS_Status nvarchar(50) NULL,
DS_Keywords nvarchar(255) NULL,
GCMD_EID nvarchar(50) NULL,
b_GCMD_Reg bit NOT NULL,
b_DS_Online bit NOT NULL,
DC_ID int NOT NULL,
b_GCOS bit NOT NULL,
b_GOOS bit NOT NULL,
b_GTOS bit NOT NULL,
b_Active bit NOT NULL,
DS_Cov_N real NULL,
DS_Cov_S real NULL,
DS_Cov_W real NULL,
DS_Cov_E real NULL,
DS_Notes nvarchar(4000) NULL
)
GO
CREATE TABLE tblGOOSMatrix_Topic_Xref (
GOOSMatrix_Topic_ID int IDENTITY(1,1) NOT NULL,
GOOS_Matrix_ID int NULL,
Topic_ID int NULL
)
GO
CREATE TABLE tblGOOS_matrix (
GOOS_Matrix_ID int IDENTITY(1,1) NOT NULL,
Category nvarchar(125) NULL,
ListOrder int NULL
)
GO
CREATE TABLE tblTopics (
Topic_ID int IDENTITY(1,1) NOT NULL,
Topic nvarchar(100) NULL,
Description nvarchar(1000) NULL
)
GO
"dw" wrote:

> I'm populating a criteria list from a secondary table and want to pull bac
k
> the matches in the primary table. However I want to set up something like
a
> an AND criteria between the results.
> My current query is:
> SELECT Name, TopicID FROM PrimaryTable WHERE TopicID IN (SELECT TopicID FR
OM
> SecondaryTable WHERE DataLabel=1)
> However the subquery (SELECT TopicID FROM SecondaryTable WHERE DataLabel=1
)
> returns a result set of (1, 4, 11), and for another DataLabel value it mig
ht
> return (5, 7). And the above query returns rows with the equivalent of an
OR
> statement for TopicID, ie: it comes back with matches for TopicID=1 OR
> TopicID=4 OR TopicID=11.
> How can I phrase it so that it comes back with the AND equivalent (TopicID
=1
> AND TopicID=4 AND TopicID=11), so that only those matching ALL results tha
t
> are returned from the SecondaryTable query are listed.
> TIA,
> Doug|||My objective is to list the Categories in "tblGOOS_Matrix" ordered by
"ListOrder" ASC with a list of matching "Datasets" from "tblDatasets". The
table "tblGOOSMatrix_Topic_Xref" lists the "Topic_ID"s that define the
criteria to fall under a specific Category.
"tblDS_Topic_Xref" lists all of the "Topic_ID"s assigned to each "Dataset".|||Thanks for the tip.
I've been looking over the article in dbazine.com on Relational Division as
well as the section in The Guru's Guide to Transact SQL. The examples
illustrate the concept but I'm still not able to wrap my brain around formin
g
the query using our two Xref tables.
Doug
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications.
> Look up "Relational Division"; it is one of Codd's original eight
> operators.
>|||I'm going to get my act together and post "DDL" and the like from the get-go
and repost this question. Now that I know what "DDL" is ;?)
D
"dw" wrote:
> My objective is to list the Categories in "tblGOOS_Matrix" ordered by
> "ListOrder" ASC with a list of matching "Datasets" from "tblDatasets". Th
e
> table "tblGOOSMatrix_Topic_Xref" lists the "Topic_ID"s that define the
> criteria to fall under a specific Category.
> "tblDS_Topic_Xref" lists all of the "Topic_ID"s assigned to each "Dataset".[/color
]