Mixing
Error “Unknown column in where clause” - What is wrong?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I'm using MySQL Server 5.7 and I have this query:
select
regDate,
userID,
t.teamID,
login
from
tbl_user u
inner join
tbl_team t on u.userID = t.userID
where
regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
AND
(
select sum(transactions) from (
SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
) as all
) > 0
I'm getting this error:
Error Code: 1054. Unknown column 't.teamID' in 'where clause'
I'm sure this is just a minor issue, but I can't get it right now. And the column teamID is existing in table tbl_team. Anybody a hint for me?
mysql sql correlated-subquery
edited Jan 3 at 16:28
Bill Karwin
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
add a comment |
0
I'm using MySQL Server 5.7 and I have this query:
select
regDate,
userID,
t.teamID,
login
from
tbl_user u
inner join
tbl_team t on u.userID = t.userID
where
regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
AND
(
select sum(transactions) from (
SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
) as all
) > 0
I'm getting this error:
Error Code: 1054. Unknown column 't.teamID' in 'where clause'
I'm sure this is just a minor issue, but I can't get it right now. And the column teamID is existing in table tbl_team. Anybody a hint for me?
mysql sql correlated-subquery
edited Jan 3 at 16:28
Bill Karwin
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
add a comment |
0
0
0
I'm using MySQL Server 5.7 and I have this query:
select
regDate,
userID,
t.teamID,
login
from
tbl_user u
inner join
tbl_team t on u.userID = t.userID
where
regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
AND
(
select sum(transactions) from (
SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
) as all
) > 0
I'm getting this error:
Error Code: 1054. Unknown column 't.teamID' in 'where clause'
I'm sure this is just a minor issue, but I can't get it right now. And the column teamID is existing in table tbl_team. Anybody a hint for me?
mysql sql correlated-subquery
edited Jan 3 at 16:28
Bill Karwin
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
I'm using MySQL Server 5.7 and I have this query:
select
regDate,
userID,
t.teamID,
login
from
tbl_user u
inner join
tbl_team t on u.userID = t.userID
where
regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH)
AND
(
select sum(transactions) from (
SELECT count(*) as transactions FROM tbl_pp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_psc where (fromTeamID = t.teamID or toTeamID = t.teamID) and transactionDate >= u.regDate
union all
SELECT count(*) as transactions FROM tbl_mp where (fromTeamID = t.teamID or forTeamID = t.teamID) and transactionDate >= u.regDate
) as all
) > 0
I'm getting this error:
Error Code: 1054. Unknown column 't.teamID' in 'where clause'
I'm sure this is just a minor issue, but I can't get it right now. And the column teamID is existing in table tbl_team. Anybody a hint for me?
mysql sql correlated-subquery
mysql sql correlated-subquery
edited Jan 3 at 16:28
Bill Karwin
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
edited Jan 3 at 16:28
Bill Karwin
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
edited Jan 3 at 16:28
Bill Karwin
386k64521680
edited Jan 3 at 16:28
Bill Karwin
386k64521680
edited Jan 3 at 16:28
Bill Karwin
386k64521680
386k64521680
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
asked Jan 3 at 16:22
dns_nxdns_nx
1,56311736
1,56311736
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
4
You cannot nest correlated references more than one query deep. You are better off using exists anyway:
select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join
tbl_team t
on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
(exists (select 1
from tbl_pp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_psc p
where t.teamID in (p.fromTeamID, p.toTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_mp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
)
)
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Doestbl_teamhave a column namedteamID?
– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
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
});
}
});
draft saved
draft discarded
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%2f54026115%2ferror-unknown-column-in-where-clause-what-is-wrong%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
You cannot nest correlated references more than one query deep. You are better off using exists anyway:
select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join
tbl_team t
on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
(exists (select 1
from tbl_pp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_psc p
where t.teamID in (p.fromTeamID, p.toTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_mp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
)
)
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Doestbl_teamhave a column namedteamID?
– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
add a comment |
4
You cannot nest correlated references more than one query deep. You are better off using exists anyway:
select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join
tbl_team t
on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
(exists (select 1
from tbl_pp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_psc p
where t.teamID in (p.fromTeamID, p.toTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_mp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
)
)
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Doestbl_teamhave a column namedteamID?
– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
add a comment |
4
4
4
You cannot nest correlated references more than one query deep. You are better off using exists anyway:
select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join
tbl_team t
on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
(exists (select 1
from tbl_pp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_psc p
where t.teamID in (p.fromTeamID, p.toTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_mp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
)
)
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
You cannot nest correlated references more than one query deep. You are better off using exists anyway:
select u.regDate, u.userID, t.teamID, u.login
from tbl_user u inner join
tbl_team t
on u.userID = t.userID
where u.regDate >= DATE_ADD(CURDATE(), INTERVAL -2 MONTH) and
(exists (select 1
from tbl_pp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_psc p
where t.teamID in (p.fromTeamID, p.toTeamID) and
p.transactionDate >= u.regDate
) or
exists (select 1
from tbl_mp p
where t.teamID in (p.fromTeamID, p.forTeamID) and
p.transactionDate >= u.regDate
)
)
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
edited Jan 3 at 16:45
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
answered Jan 3 at 16:26
Gordon LinoffGordon Linoff
798k37320425
798k37320425
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Doestbl_teamhave a column namedteamID?
– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
add a comment |
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Doestbl_teamhave a column namedteamID?
– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
Thanks. I did not know that. But with your solution, the error is still the same.
– dns_nx
Jan 3 at 16:31
@dns_nx . . . Does
tbl_team have a column named teamID?– Gordon Linoff
Jan 3 at 16:34
@dns_nx . . . Does
tbl_team have a column named teamID?– Gordon Linoff
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
Yes, as written above.
– dns_nx
Jan 3 at 16:34
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
I got it. You wrote in your query in tbl_psc 'forTeamID', but there it is 'toTeamID'. Thanks
– dns_nx
Jan 3 at 16:37
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54026115%2ferror-unknown-column-in-where-clause-what-is-wrong%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
