UNION SELECT after LEFT JOIN and JOIN

up vote
0
down vote

favorite

    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?

edited Nov 27 at 20:34

asked Nov 25 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 at 23:49

add a comment |

up vote
0
down vote

favorite

    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

edited Nov 27 at 20:34

asked Nov 25 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 at 23:49

add a comment |

up vote
0
down vote

favorite

    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

edited Nov 27 at 20:34

asked Nov 25 at 22:44

user165684

    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

mysql

edited Nov 27 at 20:34

asked Nov 25 at 22:44

user165684

edited Nov 27 at 20:34

asked Nov 25 at 22:44

user165684

edited Nov 27 at 20:34

asked Nov 25 at 22:44

user165684

asked Nov 25 at 22:44

user165684

asked Nov 25 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 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 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 at 23:49

add a comment |

2 Answers
2

active

oldest

votes

up vote
1
down vote

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.

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

There's an example of that in the manual page
– danblack
Nov 25 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 at 23:30

add a comment |

up vote
1
down vote

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 other SELECTs. (Simply pad with blanks to 'fix' the problem.)

If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

@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 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 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',
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');
});

Post as a guest

Name

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

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
1
down vote

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.

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

There's an example of that in the manual page
– danblack
Nov 25 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 at 23:30

add a comment |

up vote
1
down vote

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.

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

There's an example of that in the manual page
– danblack
Nov 25 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 at 23:30

add a comment |

up vote
1
down vote

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.

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

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.

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

edited Nov 25 at 23:47

answered Nov 25 at 22:59

danblack

1,4661212

answered Nov 25 at 22:59

danblack

1,4661212

answered Nov 25 at 22:59

danblack

1,4661212

There's an example of that in the manual page
– danblack
Nov 25 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 at 23:30

add a comment |

There's an example of that in the manual page
– danblack
Nov 25 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 at 23:30

There's an example of that in the manual page
– danblack
Nov 25 at 23:24

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 at 23:30

add a comment |

up vote
1
down vote

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 other SELECTs. (Simply pad with blanks to 'fix' the problem.)

If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

@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 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 at 0:12

add a comment |

up vote
1
down vote

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 other SELECTs. (Simply pad with blanks to 'fix' the problem.)

If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

@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 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 at 0:12

add a comment |

up vote
1
down vote

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 other SELECTs. (Simply pad with blanks to 'fix' the problem.)

If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

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 other SELECTs. (Simply pad with blanks to 'fix' the problem.)

If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

edited Nov 26 at 0:13

answered Nov 25 at 23:47

Rick James

40.2k22257

answered Nov 25 at 23:47

Rick James

40.2k22257

answered Nov 25 at 23:47

Rick James

40.2k22257

@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 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 at 0:12

add a comment |

@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 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 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 at 23:58

@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 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.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Csdrhrt