must have this and that

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

Query includes 2 tables:
t040Project (ProjectID PK, ProjectName)
t41ContactsProj( ProjectID PK, ContactSubID SK, ContactID SK, CurrentPMorMgr)
all PK and SK are numeric and CurrentPMorMgr is YesNo)

How can I make the query with the above fields only if there are records in
t41ContactsProj where ProjectID has a ContactSubID =48 and one that =49 and
the CurrentPMorMgr =-1 on both.

example
ProjectID ProjectName ContactSubID CurrentPMorMgr
....1...............ABC.................48....................-1
....1...............ABC.................49....................-1
....2...............EFG.................49....................
....3...............HIJ..................48....................-1

The only records I wnat in the query will be the first 2 because the Project
has ContactSubID =48 CurrentPMorMgr =-1 and ContactSubID =49 CurrentPMorMgr
=-1


Thank you for your help
 
Try this --
SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE t040Project.ProjectID = (SELECT [XX].ProjectID FROM t41ContactsProj AS
[XX] WHERE [XX].ContactSubID = 48 AND [XX].CurrentPMorMgr =-1) AND
t040Project.ProjectID = (SELECT [XX1].ProjectID FROM t41ContactsProj AS [XX1]
WHERE [XX1].ContactSubID = 49 AND [XX1].CurrentPMorMgr =-1)
ORDER BY t040Project.ProjectID;
 
I get msg that says...
At most one record can be returned by this subquery.

Any suggestions
--
deb


KARL DEWEY said:
Try this --
SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE t040Project.ProjectID = (SELECT [XX].ProjectID FROM t41ContactsProj AS
[XX] WHERE [XX].ContactSubID = 48 AND [XX].CurrentPMorMgr =-1) AND
t040Project.ProjectID = (SELECT [XX1].ProjectID FROM t41ContactsProj AS [XX1]
WHERE [XX1].ContactSubID = 49 AND [XX1].CurrentPMorMgr =-1)
ORDER BY t040Project.ProjectID;

--
Build a little, test a little.


deb said:
Query includes 2 tables:
t040Project (ProjectID PK, ProjectName)
t41ContactsProj( ProjectID PK, ContactSubID SK, ContactID SK, CurrentPMorMgr)
all PK and SK are numeric and CurrentPMorMgr is YesNo)

How can I make the query with the above fields only if there are records in
t41ContactsProj where ProjectID has a ContactSubID =48 and one that =49 and
the CurrentPMorMgr =-1 on both.

example
ProjectID ProjectName ContactSubID CurrentPMorMgr
...1...............ABC.................48....................-1
...1...............ABC.................49....................-1
...2...............EFG.................49....................
...3...............HIJ..................48....................-1

The only records I wnat in the query will be the first 2 because the Project
has ContactSubID =48 CurrentPMorMgr =-1 and ContactSubID =49 CurrentPMorMgr
=-1


Thank you for your help
Click to expand...
Click to expand...
 
Maybe now --
SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE t040Project.ProjectID = (SELECT [XX].ProjectID FROM t41ContactsProj AS
[XX] WHERE [XX].ContactSubID = 48 AND [XX].CurrentPMorMgr =-1
AND[XX].ProjectID = t040Project.ProjectID) AND t040Project.ProjectID =
(SELECT [XX1].ProjectID FROM t41ContactsProj AS [XX1] WHERE
[XX1].ContactSubID = 49 AND [XX1].CurrentPMorMgr =-1 AND[XX1].ProjectID =
t040Project.ProjectID)
ORDER BY t040Project.ProjectID;

--
Build a little, test a little.


deb said:
I get msg that says...
At most one record can be returned by this subquery.

Any suggestions
--
deb


KARL DEWEY said:
Try this --
SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE t040Project.ProjectID = (SELECT [XX].ProjectID FROM t41ContactsProj AS
[XX] WHERE [XX].ContactSubID = 48 AND [XX].CurrentPMorMgr =-1) AND
t040Project.ProjectID = (SELECT [XX1].ProjectID FROM t41ContactsProj AS [XX1]
WHERE [XX1].ContactSubID = 49 AND [XX1].CurrentPMorMgr =-1)
ORDER BY t040Project.ProjectID;

--
Build a little, test a little.


deb said:
Query includes 2 tables:
t040Project (ProjectID PK, ProjectName)
t41ContactsProj( ProjectID PK, ContactSubID SK, ContactID SK, CurrentPMorMgr)
all PK and SK are numeric and CurrentPMorMgr is YesNo)

How can I make the query with the above fields only if there are records in
t41ContactsProj where ProjectID has a ContactSubID =48 and one that =49 and
the CurrentPMorMgr =-1 on both.

example
ProjectID ProjectName ContactSubID CurrentPMorMgr
...1...............ABC.................48....................-1
...1...............ABC.................49....................-1
...2...............EFG.................49....................
...3...............HIJ..................48....................-1

The only records I wnat in the query will be the first 2 because the Project
has ContactSubID =48 CurrentPMorMgr =-1 and ContactSubID =49 CurrentPMorMgr
=-1


Thank you for your help
Click to expand...
Click to expand...
Click to expand...
 
Replace the = comparison operator with IN

SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE t040Project.ProjectID IN (SELECT [XX].ProjectID FROM t41ContactsProj AS
[XX] WHERE [XX].ContactSubID = 48 AND [XX].CurrentPMorMgr =-1) AND
t040Project.ProjectID IN (SELECT [XX1].ProjectID FROM t41ContactsProj AS [XX1]
WHERE [XX1].ContactSubID = 49 AND [XX1].CurrentPMorMgr =-1)
ORDER BY t040Project.ProjectID;

OR use Exists operator with coorelated subqueries.

SELECT t040Project.ProjectID, ProjectName, ContactID, ContactSubID,
CurrentPMorMgr
FROM t040Project INNER JOIN t41ContactsProj ON t040Project.ProjectID =
t41ContactsProj.ProjectID
WHERE Exists (SELECT * FROM t41ContactsProj AS [XX]
WHERE [XX].ContactSubID = 48
AND [XX].CurrentPMorMgr =-1
and XX.ProjectID = t41ContactsProj.ProjectID)
AND
EXISTS (SELECT * FROM t41ContactsProj AS [XX1]
WHERE [XX1].ContactSubID = 49
AND [XX1].CurrentPMorMgr =-1
and XX1.ProjectID = t41ContactsProj.ProjectID)
ORDER BY t040Project.ProjectID;



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top