Having trouble with a query

[Guest]

Greetings.

I have a database with the following tables:
tblOrg
=====
OrgID (*)
OrgName

tblRep
=======
RepID (*)
OrgID (FK)
RepName

tblProject
========
ProjectID (*)
ProjectName

tblProjectPartner (with the combination of ProjectID and RepID as PK)
============
ProjectID (*)
RepID (*)


tblOrg (1) -----> (many) tblOrgRep

---------------------------------------
I need to get a query that lists the Organizations and the Projects, linked
using RepID. I need the pair of OrgID, ProjectID to be unique, and am having
trouble figuring this out. e.g.
===============
tblOrg
===============
OrgID -- OrgName
--------------------
1 -- Org_A
2 -- Org_B
3 -- Org_C

=====================
tblRep
=====================
RepID -- RepName -- OrgID
-----------------------------
1 -- Joe -- 1
2 -- Jane -- 1
3 -- Jack -- 1
4 -- Smith -- 2
5 -- Mel -- 2
6 -- Zack -- 3
====================
tblProject
====================
ProjectID -- ProjectName
---------------------------
1 -- ProjectThis
2 -- ProjectThat

================
tblProjectPartner
================
ProjectID -- RepID
---------------------
1 -- 1
1 -- 2
1 -- 4
1 -- 5
2 -- 1
2 -- 3
2 -- 4
2 -- 5
2 -- 6

I'd like to run a query to get the following results:
------------------------------------------------
OrgName -- ProjectName
--------------------------
Org_A -- ProjectThis
Org_B -- ProjectThis
Org_A -- ProjectThat
Org_B -- ProjectThat
Org_C -- ProjectThat

=> a list of unique Organizations partnerting on each Project.

I've been able to link tables and run a query that lists the Projects and
Organizations, but I've been unable to get the unique pair (Org, Project) in
it. Can this be done without making a table? How?

Thanks for any pointers/help.

-Amit

 

[MGFoster]

Greetings.

I have a database with the following tables:
tblOrg
=====
OrgID (*)
OrgName

tblRep
=======
RepID (*)
OrgID (FK)
RepName

tblProject
========
ProjectID (*)
ProjectName

tblProjectPartner (with the combination of ProjectID and RepID as PK)
============
ProjectID (*)
RepID (*)


tblOrg (1) -----> (many) tblOrgRep

---------------------------------------
I need to get a query that lists the Organizations and the Projects, linked
using RepID. I need the pair of OrgID, ProjectID to be unique, and am having
trouble figuring this out. e.g.
===============
tblOrg
===============
OrgID -- OrgName
--------------------
1 -- Org_A
2 -- Org_B
3 -- Org_C

=====================
tblRep
=====================
RepID -- RepName -- OrgID
-----------------------------
1 -- Joe -- 1
2 -- Jane -- 1
3 -- Jack -- 1
4 -- Smith -- 2
5 -- Mel -- 2
6 -- Zack -- 3
====================
tblProject
====================
ProjectID -- ProjectName
---------------------------
1 -- ProjectThis
2 -- ProjectThat

================
tblProjectPartner
================
ProjectID -- RepID
---------------------
1 -- 1
1 -- 2
1 -- 4
1 -- 5
2 -- 1
2 -- 3
2 -- 4
2 -- 5
2 -- 6

I'd like to run a query to get the following results:
------------------------------------------------
OrgName -- ProjectName
--------------------------
Org_A -- ProjectThis
Org_B -- ProjectThis
Org_A -- ProjectThat
Org_B -- ProjectThat
Org_C -- ProjectThat

=> a list of unique Organizations partnerting on each Project.

I've been able to link tables and run a query that lists the Projects and
Organizations, but I've been unable to get the unique pair (Org, Project) in
it. Can this be done without making a table? How?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table tblRep needs to be like this:

CREATE TABLE tblRep (
RepID Counter NOT NULL UNIQUE ,
RepName varchar(50) PRIMARY KEY
)

The PK on the RepName prevents duplicate name entries. The Unique
constraint on the RepID is so it can be used in FKs w/ other tables.

Then you need a table that joins Reps to Orgs (which assumes that one
rep can be associated w/ more than one org):

CREATE TABLE tblOrgReps (
OrgID integer NOT NULL REFERENCES tblOrg ,
RepID integer NOT NULL REFERENCES tblRep ,
CONSTRAINT PK_tblOrgReps PRIMARY KEY (OrgID, RepID)
)

With that table each Org will have only one instance of a Rep.

To get the Org names & Project names (untested):

SELECT O.OrgName, P.ProjectName
FROM ((tblOrg AS O
INNER JOIN tblOrgReps AS R
ON O.OrgID = R.OrgID)
INNER JOIN tblProjectPartner AS PP
ON O.OrgID = PP.OrgID AND R.RepID = PP.RepID)
INNER JOIN tblProject AS P
ON PP.ProjectID = P.ProjectID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi0oGIechKqOuFEgEQKs4ACdE6yTHg9/PlO/KktWHeqGCuwLs8sAnAq7
/qE6On3gk5o64Fo0nkK/AShG
=OIRm
-----END PGP SIGNATURE-----

 

[Guest]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table tblRep needs to be like this:

CREATE TABLE tblRep (
RepID Counter NOT NULL UNIQUE ,
RepName varchar(50) PRIMARY KEY
)

The PK on the RepName prevents duplicate name entries. The Unique
constraint on the RepID is so it can be used in FKs w/ other tables.

Then you need a table that joins Reps to Orgs (which assumes that one
rep can be associated w/ more than one org):

Hi Mark,

Thanks for your response. Actually, I cannot redesign tblRep the way you
suggested because the relationship between Org and Rep is that one Org can
have many Reps, but one Rep can belong to only one Org. That's why OrgID is a
FK in tblRep.

I also figured out the query by adding DISTINCT to OrgID in the query I had.
What I wanted was to get the Org involvement in Projects when only the Reps
were linked to the Project. So, if there were 2 Reps from one Org linked to a
Project, I wanted the Org name to appear only once with that Project, instead
of twice.

Thanks again.

-Amit

CREATE TABLE tblOrgReps (
OrgID integer NOT NULL REFERENCES tblOrg ,
RepID integer NOT NULL REFERENCES tblRep ,
CONSTRAINT PK_tblOrgReps PRIMARY KEY (OrgID, RepID)
)

With that table each Org will have only one instance of a Rep.

To get the Org names & Project names (untested):

SELECT O.OrgName, P.ProjectName
FROM ((tblOrg AS O
INNER JOIN tblOrgReps AS R
ON O.OrgID = R.OrgID)
INNER JOIN tblProjectPartner AS PP
ON O.OrgID = PP.OrgID AND R.RepID = PP.RepID)
INNER JOIN tblProject AS P
ON PP.ProjectID = P.ProjectID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi0oGIechKqOuFEgEQKs4ACdE6yTHg9/PlO/KktWHeqGCuwLs8sAnAq7
/qE6On3gk5o64Fo0nkK/AShG
=OIRm
-----END PGP SIGNATURE-----

Hi Mark,

Thanks for your response. Actually, I cannot