Many to many relationship - SQL

Question

3.00/5 (2 votes)

See more:

Hi,

I have 2 tables where, table 1 has got projectID(Primary Key), Project Name etc..
table 2has got ProjectID, BusinesscaseID, etc but there is no primary key in this table.

I have to Filter all the Business case ID's which are related to the Project ID on the Table1 (This is called active project), now take out all active project's business cases and find out the other project id's which are related to these business cases.

BC (BCID, BCNAME, PROJECTID)- no primary key
AP (ProjectID,ProjectName) - Project ID is primary key

1 project has got many business cases and 1 Business case has got many project (Many to many relationship)

Can anyone help me with this please?

Thank you

What I have tried:

I tried using MS ACCESS but not having the results I wanted.

Posted 4-Feb-17 5:13am

Member 12982752

Updated 5-Feb-17 4:55am

Add a Solution

Comments

[no name] 4-Feb-17 14:58pm

First: Many to many and two tables do not match!
Second: A table without a Primary Sound like a bad design.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2017-02-05T04:55:00

A building must have a good foundation, else it risks collapse. In software development, I liken the database component to the foundation of a building.
You must design the database correctly, else you will face many difficulties, such as this one, in subsequent development.
Firstly, to establish many-to-many relationship between 2 tables, you need a third table to broker it. For example,

table1                      table3                       table2
projectID(primary key)+---< projectID(primary key)      bizcaseName
projectName                 bizcaseID(primary key) >---+bizcaseID (primary key)
otherfields                                             otherfields

Secondly, every table must have a primary key, for table3, the 2 fields are the composite primary key.
Learn more:
1. Introduction to database design[^]
2. 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]
Do not move forward until you fix your database design.

RossMW · Answer 2 · 2017-02-04T07:56:00

Solution 1

See the below article on joins for sql server

T-SQL Join Types | T-SQL content from SQL Server Pro[^]

This description the code to use when joining tables and the various relationship models

Posted 4-Feb-17 7:56am

RossMW