How do I link many to many in SQL query

Question

0.00/5 (No votes)

See more:

Hi Guys

I need some assistance with the below

I have 3 database tables

A
B
C

Table A and Table B have a 1 to many relationship(Key_Table A = Multiple in Table B)
Table A and Table C also have a 1 to many relationship(Key_Table A = Multiple in Table C)

The problem i am facing is that i need to write a query to retrieve some information from Table A,B,C
for eg.

Select Column1,column2,TableB.column1,TableC.column1
from Table A

How would i join these tables so that i wont get a duplication of records?

What I have tried:

Select TableA.Column1,TableB.Column1,TableC.Column1

From TableA
Inner Join
TableB on TableA.Column_key = TableB.TableA_key
Inner Join
TableB on TableA.Column_key = TableB.TableA_key

Posted 14-Apr-16 3:07am

IsiR_19

Updated 16-Apr-16 8:46am

Add a Solution

Comments

ZurdoDev 14-Apr-16 9:12am

Join both tables and you can use SELECT DISTINCT if you are getting duplicates.

IsiR_19 14-Apr-16 10:07am

The problem i am having is when i join the tables i am getting the following result e.g

Table A Key = 1

Table B Column1 = 15 where TableB.TableA_key = 1
Table B Column1 = 20 where TableB.TableA_key = 1

Table C Column1 = 10 where TableB.TableA_key = 1
Table C Column1 = 11 where TableB.TableA_key = 1

Result will bring Back

Result 1
Key = 1
TableB.column1 = 15
TableC.column1 = 10

Result 2
Key = 1
TableB.column1 = 20
TableC.column1 = 10

Result 3
Key = 1
TableB.column1 = 15
TableC.column1 = 11

Result 4
Key = 1
TableB.column1 = 20
TableC.column1 = 11

i only require 2 sets of results

Result 3
Key = 1
TableB.column1 = 15
TableC.column1 = 11

Result 4
Key = 1
TableB.column1 = 20
TableC.column1 = 11

ZurdoDev 14-Apr-16 10:08am

I don't follow.

F-ES Sitecore 14-Apr-16 10:28am

The easiest way of dealing with this is normally just getting all the results, including the duplicate data you don't need, and forming a data structure more like what you want in your code. SQL doesn't really do hierarchical data which is what I suspect you are looking for. Well, you can use xml, but good luck with that.

1 solution

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)

Jörgen Andersson · Answer 1 · 2016-04-16T08:46:00

So you only want resulting rows from one of the rows in TableC, then you must add a condition for that.

SQL

WITH CTE AS (
    SELECT  TableA.KEY
           ,Max(TableC.Column1) AS Column1
    FROM    TableA
    JOIN    TableC ON TableA.Column_key = TableC.TableA_key
    GROUP BY TableA.KEY
    )
SELECT  CTE.KEY
       ,TableB.Column1
       ,CTE.Column1
FROM    CTE
JOIN    TableB ON CTE.KEY = TableB.TableA_key