UNION SELECT after LEFT JOIN and JOIN
0
SELECT DISTINCT re.*
FROM `reports` r
LEFT JOIN `users` u
ON u.`id_rep` = r.`id`
JOIN `customers` c
ON u.`id` = r.`uid`
WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
ORDER BY r.date DESC
Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?
mysql
add a comment |
0
SELECT DISTINCT re.*
FROM `reports` r
LEFT JOIN `users` u
ON u.`id_rep` = r.`id`
JOIN `customers` c
ON u.`id` = r.`uid`
WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
ORDER BY r.date DESC
Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?
mysql
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49
add a comment |
0
0
0
0
SELECT DISTINCT re.*
FROM `reports` r
LEFT JOIN `users` u
ON u.`id_rep` = r.`id`
JOIN `customers` c
ON u.`id` = r.`uid`
WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
ORDER BY r.date DESC
Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?
mysql
SELECT DISTINCT re.*
FROM `reports` r
LEFT JOIN `users` u
ON u.`id_rep` = r.`id`
JOIN `customers` c
ON u.`id` = r.`uid`
WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
ORDER BY r.date DESC
Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?
mysql
mysql
edited Nov 27 '18 at 20:34
asked Nov 25 '18 at 22:44
user165684
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49
add a comment |
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49
add a comment |
2 Answers
2
active
oldest
votes
1
Using Union and ORDER BY:
Be explicit with results form first query:
(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')
UNION
(SELECT username,rdate FROM users)
ORDER BY rdate DESC
I assumed the second table had a date column. Otherwise and explicit value could be used.
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
add a comment |
1
If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x
SELECT ...
FROM a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM a
LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
etc. (I may have added more parentheses than necessary.)
More UNION tips:
- Yes, the number of columns needs to be the same.
- The default column names come from the first
SELECT. - If you have a constant string in a column of the first
SELECT, that limits the length of the matching string of the otherSELECTs. (Simply pad with blanks to 'fix' the problem.) - If appropriate, use
UNION ALLdo avoid the de-duping phase, thereby being faster thanUNION, which is the same asUNION DISTINCT.
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
@Rick the last 2 queries are equivalent. TheORDER BYis applied to the whole result set in both of them: dbfiddle.uk/…
– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f223394%2funion-select-after-left-join-and-join%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Using Union and ORDER BY:
Be explicit with results form first query:
(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')
UNION
(SELECT username,rdate FROM users)
ORDER BY rdate DESC
I assumed the second table had a date column. Otherwise and explicit value could be used.
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
add a comment |
1
Using Union and ORDER BY:
Be explicit with results form first query:
(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')
UNION
(SELECT username,rdate FROM users)
ORDER BY rdate DESC
I assumed the second table had a date column. Otherwise and explicit value could be used.
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
add a comment |
1
1
1
Using Union and ORDER BY:
Be explicit with results form first query:
(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')
UNION
(SELECT username,rdate FROM users)
ORDER BY rdate DESC
I assumed the second table had a date column. Otherwise and explicit value could be used.
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
Using Union and ORDER BY:
Be explicit with results form first query:
(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')
UNION
(SELECT username,rdate FROM users)
ORDER BY rdate DESC
I assumed the second table had a date column. Otherwise and explicit value could be used.
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
edited Nov 25 '18 at 23:47
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
answered Nov 25 '18 at 22:59
danblackdanblack
2,1161214
2,1161214
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
add a comment |
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
There's an example of that in the manual page
– danblack
Nov 25 '18 at 23:24
1
1
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
Try a new question. You're adding an amount of complexity beyond the initial question and without a full example with sample output clarification by comment is getting increasingly likely to be wrong.
– danblack
Nov 25 '18 at 23:30
add a comment |
1
If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x
SELECT ...
FROM a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM a
LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
etc. (I may have added more parentheses than necessary.)
More UNION tips:
- Yes, the number of columns needs to be the same.
- The default column names come from the first
SELECT. - If you have a constant string in a column of the first
SELECT, that limits the length of the matching string of the otherSELECTs. (Simply pad with blanks to 'fix' the problem.) - If appropriate, use
UNION ALLdo avoid the de-duping phase, thereby being faster thanUNION, which is the same asUNION DISTINCT.
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
@Rick the last 2 queries are equivalent. TheORDER BYis applied to the whole result set in both of them: dbfiddle.uk/…
– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
add a comment |
1
If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x
SELECT ...
FROM a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM a
LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
etc. (I may have added more parentheses than necessary.)
More UNION tips:
- Yes, the number of columns needs to be the same.
- The default column names come from the first
SELECT. - If you have a constant string in a column of the first
SELECT, that limits the length of the matching string of the otherSELECTs. (Simply pad with blanks to 'fix' the problem.) - If appropriate, use
UNION ALLdo avoid the de-duping phase, thereby being faster thanUNION, which is the same asUNION DISTINCT.
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
@Rick the last 2 queries are equivalent. TheORDER BYis applied to the whole result set in both of them: dbfiddle.uk/…
– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
add a comment |
1
1
1
If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x
SELECT ...
FROM a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM a
LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
etc. (I may have added more parentheses than necessary.)
More UNION tips:
- Yes, the number of columns needs to be the same.
- The default column names come from the first
SELECT. - If you have a constant string in a column of the first
SELECT, that limits the length of the matching string of the otherSELECTs. (Simply pad with blanks to 'fix' the problem.) - If appropriate, use
UNION ALLdo avoid the de-duping phase, thereby being faster thanUNION, which is the same asUNION DISTINCT.
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x
SELECT ...
FROM a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM a
LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
SELECT ...
FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...
etc. (I may have added more parentheses than necessary.)
More UNION tips:
- Yes, the number of columns needs to be the same.
- The default column names come from the first
SELECT. - If you have a constant string in a column of the first
SELECT, that limits the length of the matching string of the otherSELECTs. (Simply pad with blanks to 'fix' the problem.) - If appropriate, use
UNION ALLdo avoid the de-duping phase, thereby being faster thanUNION, which is the same asUNION DISTINCT.
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
edited Nov 26 '18 at 0:13
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
answered Nov 25 '18 at 23:47
Rick JamesRick James
43.6k22259
43.6k22259
@Rick the last 2 queries are equivalent. TheORDER BYis applied to the whole result set in both of them: dbfiddle.uk/…
– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
add a comment |
@Rick the last 2 queries are equivalent. TheORDER BYis applied to the whole result set in both of them: dbfiddle.uk/…
– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
@Rick the last 2 queries are equivalent. The
ORDER BY is applied to the whole result set in both of them: dbfiddle.uk/…– ypercubeᵀᴹ
Nov 25 '18 at 23:58
@Rick the last 2 queries are equivalent. The
ORDER BY is applied to the whole result set in both of them: dbfiddle.uk/…– ypercubeᵀᴹ
Nov 25 '18 at 23:58
1
1
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
@ypercubeᵀᴹ - Geez, I'm only a decade out of step -- bugs.mysql.com/bug.php?id=27848 "fixed" that in 2008 (5.0.56, 5.1.23). Thanks for rattling my cage.
– Rick James
Nov 26 '18 at 0:12
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f223394%2funion-select-after-left-join-and-join%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
I don't understand your EDIT. Please make a stab at the query; maybe it will be clearer then.
– Rick James
Nov 25 '18 at 23:49