Join two tables based on a view, sql

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have the following sql situation
Table1
id name
Table2
id name
_Table1ToTable2
id, id_table1, id_table2
Result:
id, name, table2_name, table2_id
I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?
mysql sql
add a comment |
I have the following sql situation
Table1
id name
Table2
id name
_Table1ToTable2
id, id_table1, id_table2
Result:
id, name, table2_name, table2_id
I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?
mysql sql
2
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21
add a comment |
I have the following sql situation
Table1
id name
Table2
id name
_Table1ToTable2
id, id_table1, id_table2
Result:
id, name, table2_name, table2_id
I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?
mysql sql
I have the following sql situation
Table1
id name
Table2
id name
_Table1ToTable2
id, id_table1, id_table2
Result:
id, name, table2_name, table2_id
I would like to join Table1 and Table2 into a single query, but I cannot find a way to make a join between them, any ideas?
mysql sql
mysql sql
edited Jan 4 at 6:48
Tony
4121312
4121312
asked Jan 3 at 22:10
andresmijares25andresmijares25
62411426
62411426
2
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21
add a comment |
2
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21
2
2
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21
add a comment |
3 Answers
3
active
oldest
votes
It seems like you want to use bridge table Table1ToTable2
to join Table1
and Table2
. This can be done simply with 2 INNER JOIN
s :
SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2
add a comment |
if there is no relationship between table1 and table 2 then,
select table1.id, table1.name, table2.id, table2.name from
table1, table2
if table1 and table2 are related with ID then,
select table1.id, table1.name, table2.id, table2.name from
table1
inner join table2
on table1.id = table2.id
add a comment |
If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:
select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a
If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.
The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54030476%2fjoin-two-tables-based-on-a-view-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
It seems like you want to use bridge table Table1ToTable2
to join Table1
and Table2
. This can be done simply with 2 INNER JOIN
s :
SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2
add a comment |
It seems like you want to use bridge table Table1ToTable2
to join Table1
and Table2
. This can be done simply with 2 INNER JOIN
s :
SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2
add a comment |
It seems like you want to use bridge table Table1ToTable2
to join Table1
and Table2
. This can be done simply with 2 INNER JOIN
s :
SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2
It seems like you want to use bridge table Table1ToTable2
to join Table1
and Table2
. This can be done simply with 2 INNER JOIN
s :
SELECT
tt.id AS tt_id,
t1.id AS t1_id,
t1.name AS t1_name,
t2.id AS t2_id,
t2.name AS t2_name
FROM
Table1ToTable2 AS tt
INNER JOIN Table1 AS t1
ON t1.id = tt.id_table1
INNER JOIN Table2 AS t2
ON t2.id = tt.id_table2
answered Jan 4 at 1:16


GMBGMB
21.7k61028
21.7k61028
add a comment |
add a comment |
if there is no relationship between table1 and table 2 then,
select table1.id, table1.name, table2.id, table2.name from
table1, table2
if table1 and table2 are related with ID then,
select table1.id, table1.name, table2.id, table2.name from
table1
inner join table2
on table1.id = table2.id
add a comment |
if there is no relationship between table1 and table 2 then,
select table1.id, table1.name, table2.id, table2.name from
table1, table2
if table1 and table2 are related with ID then,
select table1.id, table1.name, table2.id, table2.name from
table1
inner join table2
on table1.id = table2.id
add a comment |
if there is no relationship between table1 and table 2 then,
select table1.id, table1.name, table2.id, table2.name from
table1, table2
if table1 and table2 are related with ID then,
select table1.id, table1.name, table2.id, table2.name from
table1
inner join table2
on table1.id = table2.id
if there is no relationship between table1 and table 2 then,
select table1.id, table1.name, table2.id, table2.name from
table1, table2
if table1 and table2 are related with ID then,
select table1.id, table1.name, table2.id, table2.name from
table1
inner join table2
on table1.id = table2.id
answered Jan 3 at 22:22


Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.7k22042
15.7k22042
add a comment |
add a comment |
If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:
select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a
If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.
The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.
add a comment |
If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:
select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a
If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.
The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.
add a comment |
If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:
select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a
If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.
The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.
If your intention is to retrieve a result set with every possible combination of the id columns from both tables then you can do as follows:
select id = identity (int, 1, 1), * into #a from (select distinct table1. id as id1, table2. id as id2 from table1 cross apply table2) d; select * from #a; drop table #a
If you do not wish to use a temp table an alternative would be to use some variation of the row_number() function.
The above solves for an assumed requirement as your question lacks context. Elaborate on your requirements and we will be able to provide you with a more relevant and detailed answer.
answered Jan 3 at 22:22
Dale LudwigDale Ludwig
815
815
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54030476%2fjoin-two-tables-based-on-a-view-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
UAQ4X iqMU j aIBg7Eo8,RIQhPzHO14WrMVN
2
Sample data and expected results would be helpful...
– sgeddes
Jan 3 at 22:12
@sgeddes done!!
– andresmijares25
Jan 3 at 22:14
Sample data means actual data we can work with. Also, how do the tables relate to each other ? What DBMS are you using ?
– Tony
Jan 3 at 22:15
@Tony relationship is on the _Table1ToTable2 table view, basically, it's a 1 to 1 relation between the 2, this is a mysql table
– andresmijares25
Jan 3 at 22:18
can Table1 and Table2 have different number or rows? in the result, would you like to see nulls if either Table1 or 2 corresponding row is missing?
– Max
Jan 3 at 22:21