Please help with many-to-many query... 
Author Message
 Please help with many-to-many query...
I need to get a result set with two fields from each of two tables
that are related by a map table. For example, I would like to get
Project.Name, Project.Dept, Employee.Name, Employee.Title from the
following tables.

Project
=======
ProjectKey (PK)
Name
Dept
Cost

Employee
========
EmployeeKey (PK)
Name
Title
Group

Map
=================
Key (PK)
ProjectKey
EmployeeKey

Thanks!



Fri, 06 Jan 2006 20:59:00 GMT
 Please help with many-to-many query...

Try:

select
    Project.Name, Project.Dept, Employee.Name, Employee.Title
from
    Project
join    Map         on    Map.ProjectKey = Project.ProjectKey
join    Employee    on    Employee.EmployeeKey = Map.EmployeeKey

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

I need to get a result set with two fields from each of two tables
that are related by a map table. For example, I would like to get
Project.Name, Project.Dept, Employee.Name, Employee.Title from the
following tables.

Project
=======
ProjectKey (PK)
Name
Dept
Cost

Employee
========
EmployeeKey (PK)
Name
Title
Group

Map
=================
Key (PK)
ProjectKey
EmployeeKey

Thanks!



Fri, 06 Jan 2006 21:13:01 GMT
 Please help with many-to-many query...
SELECT P.Name, P.Dept, E.Name, E.Title
FROM Project P
INNER JOIN Map M
ON P.ProjectKey  = M.ProjectKey
INNER JOIN Employee E
ON E.EmployeeKey  = M.EmployeeKey


Quote:
> I need to get a result set with two fields from each of two tables
> that are related by a map table. For example, I would like to get
> Project.Name, Project.Dept, Employee.Name, Employee.Title from the
> following tables.

> Project
> =======
> ProjectKey (PK)
> Name
> Dept
> Cost

> Employee
> ========
> EmployeeKey (PK)
> Name
> Title
> Group

> Map
> =================
> Key (PK)
> ProjectKey
> EmployeeKey

> Thanks!



Fri, 06 Jan 2006 21:19:42 GMT
 Please help with many-to-many query...
Thanks! SELECT is working beautifully, UPDATE and DELETE are easy, and
I'm now at the INSERT stage. Is it possible to insert a record into
all five tables at the same time, or do you have to insert into the 3
"main" tables first, and then create the 2 "map" table records with
the primary keys of the the new "main" table records?

Perhaps to make it easier, we could call the tables:

Table A
=======
PK-A
Name

Table B
=======
PK-B
Order

Table C
=======
PK-C
Part

Table AB (map)
========
PK
PK-A
PK-B

Table BC (map)
========
PK
PK-B
PK-C



Tue, 10 Jan 2006 22:52:19 GMT
 Please help with many-to-many query...
You can only insert rows in one table at a time, so you have to do the main
tables first and do the "map" tables (also called join tables or linking
tables) after that with the primary keys of the maintables. To make sure
that your insertion is completed correctly you should wrap it in a
transaction. If you are using identity columns for Primary Keys your code
should look something like this:




BEGIN TRAN








COMMIT TRAN


Quote:
> Thanks! SELECT is working beautifully, UPDATE and DELETE are easy, and
> I'm now at the INSERT stage. Is it possible to insert a record into
> all five tables at the same time, or do you have to insert into the 3
> "main" tables first, and then create the 2 "map" table records with
> the primary keys of the the new "main" table records?

> Perhaps to make it easier, we could call the tables:

> Table A
> =======
> PK-A
> Name

> Table B
> =======
> PK-B
> Order

> Table C
> =======
> PK-C
> Part

> Table AB (map)
> ========
> PK
> PK-A
> PK-B

> Table BC (map)
> ========
> PK
> PK-B
> PK-C



Wed, 11 Jan 2006 09:36:48 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Please help in creating a needed query

2. please help with syntax of this query.

3. Please help me with my SQL query...

4. Please Help!: (sort of) Paramaterised Totals Query

5. PLEASE HELP: Using Variables within SQL Queries

6. please help me on MULTIPLE TABLE QUERIES

7. PLEASE HELP-passing variable into SQL query

8. Please Help: Custom Functions In SQL / Queries

9. Please help with a tough update query

10. Please help with this complex SQL query

11. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

12. APPROACH QUERY-PLEASE PLEASE PLEASE HELP


 
Powered by phpBB® Forum Software