• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • 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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
PortletPaulCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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