SQL How to only show the primary/foreign key once when selecting from 3 Tables

[January Memes]

Hello, I have three tables 

Table1 with Multiple Columns and one primary key

Table2 with one column and the primary key from Table1 as foreign key

Table3 with two columns and the primary key from Table1 as foreign key

 

Is it possible to SELECT them in a way that the output is the primary key ONCE in one row with all the matching data from Table2 and Table3 

Because if I do 
SELECT Table1.*,Table2.*, Table3.* 

FROM Table1 INNER JOIN Table2 ON Table3 = Table2.FOREIGN_KEY INNER JOIN Table3 ON Table1.PRIMARY_KEY = Table3.FOREIGN_KEY

 

I get as many rows as output as there is Matching data in Table2 and Table3 
I just want one row per Primary Key

[shadow_ray]

Instead of using wildcards, select only the columns you need.

[January Memes]

1 minute ago, shadow_ray said:

Instead of using wildcards, select only the columns you need.

How would I do that? sorry if the question is stupid , i'm new to SQL

[suedseefrucht]

17 minutes ago, January Memes said:

Table1 with Multiple Columns and one primary key

Table2 with one column and the primary key from Table1 as foreign key

Table3 with two columns and the primary key from Table1 as foreign key

I don't really get what you would like to do. An example would be helpful.
Anyway... Here is a working example. Just go to "Try it yourself".
They selected colms from different tables and they used JOIN to get a customers name from his id using the customers table.
https://www.w3schools.com/sql/sql_join.asp
 

You can use multiple inner join tables like here:

https://www.w3schools.com/sql/sql_join_inner.asp

[shadow_ray]

24 minutes ago, January Memes said:

How would I do that? sorry if the question is stupid , i'm new to SQL

 Sorry I misread. I thought you wanna remove duplicate columns.

 

Maybe this is what you want:

SELECT Table1.*,Table2.*, Table3.* 
FROM Table1 
INNER JOIN Table2 ON Table1.PRIMARY_KEY = Table2.FOREIGN_KEY 
INNER JOIN Table3 ON Table1.PRIMARY_KEY = Table3.FOREIGN_KEY 

 

 

[January Memes]

11 minutes ago, suedseefrucht said:

I don't really get what you would like to do. An example would be helpful.
Anyway... Here is a working example. Just go to "Try it yourself".
They selected colms from different tables and they used JOIN to get a customers name from his id using the customers table.
https://www.w3schools.com/sql/sql_join.asp
 

You can use multiple inner join tables like here:

https://www.w3schools.com/sql/sql_join_inner.asp

For example:

Table1 has ID Name and Info

Table2 has Table1_ID Order

Table3 has Table1_ID Post

what I want as output:

 

ID Name Orders Posts 

 

what I get as output:

Order ID Post Table1_ID Name Table1_ID

Order ID Post Table1_ID Name Table1_ID

Order ID Post Table1_ID Name Table1_ID

Order ID Post Table1_ID Name Table1_ID

...  

 

[shadow_ray]

1 minute ago, January Memes said:

For example:

Table1 has ID Name and Info

Table2 has Table1_ID Order

Table3 has Table1_ID Post

what I want as output:

 

ID Orders Posts 

 

what I get as output:

Order ID Post

Order ID Post

Order ID Post

Order ID Post

...  

 

SELECT Table2.[Order], Table1.ID, Table3.Post
FROM Table1 
INNER JOIN Table2 ON Table1.ID = Table2.Table1_ID
INNER JOIN Table3 ON Table1.ID = Table3.Table1_ID

[January Memes]

3 minutes ago, shadow_ray said:

SELECT Table2.[Order], Table1.ID, Table3.Post
FROM Table1 
INNER JOIN Table2 ON Table1.ID = Table2.Table1_ID
INNER JOIN Table3 ON Table1.ID = Table3.Table1_ID

I still get:

 

order1 Id1 Post1

order2 Id1 Post1

order3 Id2 Post2

order3 Id2 Post3

as output, how do I make it into 

Id1 order1 order2 post1 

Id2 order3 post2 post3

?

[shadow_ray]

1 hour ago, January Memes said:

I still get:

 

order1 Id1 Post1

order2 Id1 Post1

order3 Id2 Post2

order3 Id2 Post3

as output, how do I make it into 

Id1 order1 order2 post1 

Id2 order3 post2 post3

?

I don't think that's possible.

[Franck]

28 minutes ago, shadow_ray said:

I don't think that's possible.

Not i don't think, that is not possible at all. Columns cannot vary in length over a single query

 

@January Memes You should specify what you are trying to do because this is not a format you can work with. You are doing something wrong

[January Memes]

18 minutes ago, Franck said:

Not i don't think, that is not possible at all. Columns cannot vary in length over a single query

 

@January Memes You should specify what you are trying to do because this is not a format you can work with. You are doing something wrong

I have an asp.net c# application where I want to be able to list and filter the Entrys of table1 with their corresponding entrys of table2 and table3 

[Franck]

11 minutes ago, January Memes said:

I have an asp.net c# application where I want to be able to list and filter the Entrys of table1 with their corresponding entrys of table2 and table3 

Specify the minimum info on your table about what you read and what you filter on. I have the feeling you need a custom union but it depend on what data you are reading.

Specify the type of the column as well to see if it's possible.

 

Also code logic wise it not always easy to get what you are doing. Can you also specify in plain english what you want to do. i.e : I want to get all user that are at least 20 years old with blue hair.

[January Memes]

Just now, Franck said:

Specify the minimum info on your table about what you read and what you filter on. I have the feeling you need a custom union but it depend on what data you are reading.

Specify the type of the column as well to see if it's possible.

 

Also code logic wise it not always easy to get what you are doing. Can you also specify in plain english what you want to do. i.e : I want to get all user that are at least 20 years old with blue hair.

So currently I'm just trying to show them in one Asp.net table, before I had all data stored in a XML document consisting of multiple strings and two string arrays, it was easy to read them and show them in a Table I showed the data of those string arrays in one cell joined by commas, but I wanted it to be easier to filter these values so I translated this XML document into three SQL tables two for the arrays and one for all the other data, now I want to list them in one Asp.net Table again like before( meaning for every entry in Table1 there should be one row also containing the data of all corresponding entrys of Table2 and Table3) is that possible or am I being really stupid here? 

[Franck]

2 minutes ago, January Memes said:

So currently I'm just trying to show them in one Asp.net table, before I had all data stored in a XML document consisting of multiple strings and two string arrays, it was easy to read them and show them in a Table I showed the data of those string arrays in one cell joined by commas, but I wanted it to be easier to filter these values so I translated this XML document into three SQL tables two for the arrays and one for all the other data, now I want to list them in one Asp.net Table again like before( meaning for every entry in Table1 there should be one row also containing the data of all corresponding entrys of Table2 and Table3) is that possible or am I being really stupid here? 

you can't in a query. You will need code either way to convert the data rows into column split. Problem is you never iterate on column, always on rows but here you are looking for variable column which does exist but are extremely inefficient and complex to make and it depends on the kind SQL server you are using. It's called Cross Table. In your case it's a simple group by in code and your done but you filter query depend on the table format and the field you search. You can make many query or 1 i depend on what you have and what you are searching on and return. There are hundreds of way to do it which doesn't end up working in all cases hence why we need to know the actual format name/ datatype mostly the later.