CODEWITHSUNDEEP
phpmysqlsql
Winter
Winter

Reputation: 1741

Combine multiple unique MySQL tables and order by one column

I've been trying to accomplish this MySQL query for the past few days now with very little luck. I'd like to combine these multiple tables and their columns, then order by one that they have in common (not by name, but by content). I have the following database tables:

mb_bans:
mb_bans

mb_ban_records:
mb_ban_records

mb_kicks:
mb_kicks

mb_mutes:
mb_mutes

mb_mutes_records:
mb_mutes_records

mb_warnings:
mb_warnings

What I'm trying to accomplish is something among the lines of this:
End Result

Unfortunately I am beyond stumped on how to combine these MySQL tables together but also at the same time keeping them in separate categories - ordered by the _time columns in each table. How would I approach this? I have been unsuccessful with my attempts at retrieving them.. The closest I could get was combining just the _time columns of each, then giving it a value in the query as "date", however I cannot do much with the results. I would still need to call it as the individual rows, correct? I could probably use fetchAll but then I would be unable to add anything to the values..

$banq = $db->prepare('SELECT banned, banned_by, ban_reason, ban_time, ban_expires_on FROM '.BAN_TABLE.' ORDER BY ban_time DESC');
$kickq = $db->prepare('SELECT kicked, kicked_by, kick_reason, kick_time FROM '.KICK_TABLE.' ORDER BY kick_time DESC');
$muteq = $db->prepare('SELECT muted, muted_by, mute_reason, mute_time, mute_expires_on FROM '.MUTE_TABLE.' ORDER BY mute_time DESC');
$warnq = $db->prepare('SELECT warned, warned_by, warn_reason, warn_time FROM '.WARN_TABLE.' ORDER BY warn_time DESC');
$banq->execute();
$kickq->execute();
$muteq->execute();
$warnq->execute();

Essentially I'd like to combine all of those queries together as one + the two _record tables. Any advice that could help me is greatly appreciated as I've spent countless hours trying to figure this out on my own.

Thanks in advance!

Upvotes: 2

Views: 326

Answers (2)

Carlos Rodriguez
Carlos Rodriguez

Reputation: 883

Try this:

SELECT * from (
SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, ban_time as Date  FROM mb_bans 
UNION
SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, kick_time as Date FROM mb_kicks 
UNION
SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, mute_time as Date  FROM mb_mutes 
UNION
SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  warn_time as Date FROM mb_warnings
) d order by d.Date DESC;

EDIT

how could I get the type of record? (IE. whether the returned result is from the bans table, mutes table, kicks table etc.)

SELECT * from (
    SELECT banned as Punisher, banned_by as Punished, ban_reason as Reason, ban_expires_on  as Expire, 'ban' as TableType, ban_time as Date  FROM mb_bans 
    UNION
    SELECT kicked as Punisher, kicked_by as Punished, kick_reason as Reason, NULL  as Expire, 'kick' as TableType, kick_time as Date FROM mb_kicks 
    UNION
    SELECT muted as Punisher, muted_by as Punished, mute_reason as Reason, mute_expires_on  as Expire, 'mute' as TableType, mute_time as Date  FROM mb_mutes 
    UNION
    SELECT warned as Punisher, warned_by as Punished, warn_reason as Reason, NULL  as Expire,  'warn' as TableType, warn_time as Date FROM mb_warnings
    ) d order by d.Date DESC;

Upvotes: 3

Aditya
Aditya

Reputation: 2301

The generic answer for this question is,

SELECT A.COL, B. COL, C.COL
FROM TABLE1 AS 'A'
INNER JOIN TABLE2 AS 'B' WHERE A.ID = B.ID
INNER JOIN TABLE3 AS 'C' WHERE B.ID = C.ID
.
.
.
<YOU CAN PUT AS MUCH AS JOINS YOU WANT>
ORDER BY <REQUIRED_COL>

Now, do this in your huge scenario.

Upvotes: 1

Related Questions