Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Multiple Queries but no join

I have 5 tables, all unrelated to each other, however I want to perform a search on them.

Each table are different in their construction, with 3 constants, a PRI-ID which is an auto-number, only used for updating and deleting, a date field and a `Notes` field

Currently I have 5 queries:-
SELECT *, `1` as `Table` FROM `Table1` WHERE `Notes` LIKE '%name%' ORDER BY `crDate`;
SELECT *, `2` as `Table` FROM `Table2` WHERE `Notes` LIKE '%name%' ORDER BY `crDate`;
SELECT *, `3` as `Table` FROM `Table3` WHERE `Notes` LIKE '%name%' ORDER BY `crDate`;
SELECT *, `4` as `Table` FROM `Table4` WHERE `Notes` LIKE '%name%' ORDER BY `crDate`;
SELECT *, `5` as `Table` FROM `Table5` WHERE `Notes` LIKE '%name%' ORDER BY `crDate`;

Open in new window


Currently I execute each one and make the table up as required, however I would ideally like to sort by created date, so I managed to join all the queries:-
SELECT `Table1`.`*`, `Table2`.`*`, `Table3`.`*`, `Table4`.`*`, `Table5`.`*`
FROM `Table1`, `Table2`, `Table3`, `Table4`, `Table5`

Open in new window


Which works great, however sorting by crDate in each table as simply "SORT BY `crDate` ASC" doesn't work, and if I sort by date on individual tables, then it correctly sorts Table1, then adds onto a correctly sorted Table2 etc, it doesnt combine the 2 tables and sort by that.

I'm hoping that by merging crDate into 1 field and then sorting, will also help me search the notes field.

Any ideas?

Thank you
0
tonelm54
Asked:
tonelm54
  • 2
2 Solutions
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
How about using UNION ALL?
SELECT *, `1` as `Table` FROM `Table1` WHERE `Notes` LIKE '%name%'
UNION ALL
SELECT *, `2` as `Table` FROM `Table2` WHERE `Notes` LIKE '%name%'
UNION ALL
SELECT *, `3` as `Table` FROM `Table3` WHERE `Notes` LIKE '%name%'
UNION ALL
SELECT *, `4` as `Table` FROM `Table4` WHERE `Notes` LIKE '%name%'
UNION ALL
SELECT *, `5` as `Table` FROM `Table5` WHERE `Notes` LIKE '%name%'
ORDER BY `crDate`;

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Don't know if MySQL supports it, but it would be much more convinient to have:
select * from
(
SELECT *, `1` as `Table` FROM `Table1` 
UNION ALL
SELECT *, `2` as `Table` FROM `Table2`
UNION ALL
SELECT *, `3` as `Table` FROM `Table3`
UNION ALL
SELECT *, `4` as `Table` FROM `Table4`
UNION ALL
SELECT *, `5` as `Table` FROM `Table5`
) data
WHERE `Notes` LIKE '%name%'
ORDER BY `crDate`;

Open in new window

0
 
PortletPaulfreelancerCommented:
A word of caution. UNION ALL is quite appropriate BUT, the field list MUST be the same AND every column MUST be the same data type. So in the above comments just be aware that:

select *

cannot be taken literally, you MUST specify the fields, and they must be the same data types
select
    `frmTable`
  , `crDate`
  , `notes`
from
    (
        SELECT '1' as `frmTable` , `crdate`, `notes` /* more ?? */
        FROM `Table1` 
        UNION ALL
        SELECT '2' as `frmTable` , `crdate`, `notes` /* more ?? */
        FROM `Table2`
        UNION ALL
        SELECT '3' as `frmTable` , `crdate`, `notes` /* more ?? */
        FROM `Table3`
        UNION ALL
        SELECT '4' as `frmTable` , `crdate`, `notes` /* more ?? */
        FROM `Table4`
        UNION ALL
        SELECT '5' as `frmTable` , `crdate`, `notes` /* more ?? */
        FROM `Table5`
    ) data
WHERE `Notes` LIKE '%name%'
ORDER BY `crDate`;

Open in new window

MySQL does support nested subqueries - but it can be slower - for more recent versions this shouldn't be an issue I think.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now