CODEWITHSUNDEEP
sql
miles_holt
miles_holt

Reputation: 73

Combine two SQL statements as one statement

I've looked into combining multiple sql statements as one but cannot get it to work.

I basically have two statements which I would like to combine as one statement which will be queried.

Here are the two statements:

$sql="SELECT DISTINCT u.".($config->realnames ? "name" : "username")." AS displayname, u.id 
FROM (#__users AS u INNER JOIN #__uddeim AS um ON u.id=um.fromid)
WHERE um.toid=".(int)$myself." AND um.totrash=0 AND `um`.`delayed`=0".$filter.


$sql2="SELECT DISTINCT u.".($config->realnames ? "name" : "username")." AS displayname, u.id 
FROM (#__users AS u INNER JOIN #__uddeim AS um ON u.id=um.toid)
WHERE um.fromid=".(int)$myself." AND um.totrashoutbox=0".$filter." AND um.systemflag=0"

And here is my attempt at combining them by using two aliases "u", and "o":

$filter="";
if ($filter_user) $filter = " AND um.fromid=".(int)$filter_user;
if ($filter_user==-1) $filter = " AND um.fromid=0";
if ($filter_unread) $filter .= " AND um.toread=0";
if ($filter_flagged) $filter .= " AND um.flagged<>0";

$sql=  "SELECT DISTINCT u.".($config->realnames ? "name" : "username")." AS displayname, u.id,  o.".($config->realnames ? "name" : "username")." AS displayname, o.id
FROM (#__users AS u INNER JOIN #__uddeim AS um ON u.id=um.fromid) INNER JOIN #__uddeim AS om ON o.id=om.toid
WHERE um.toid=".(int)$myself." AND um.totrash=0 AND `um`.`delayed`=0".$filter. " AND om.fromid=".(int)$myself." AND om.totrashoutbox=0".$filter." AND om.systemflag=0";

But this is coming up with no results, where the first two statements, individually work fine.

Any help appreciated.

Upvotes: 0

Views: 306

Answers (1)

davogotland
davogotland

Reputation: 2777

try this:

$sql="
SELECT
    DISTINCT u.".($config->realnames ? "name" : "username")." AS displayname,
    u.id 
FROM
    (#__users AS u
INNER JOIN
    #__uddeim AS um
ON
    u.id=um.fromid)
WHERE
    um.toid=".(int)$myself."
AND
    um.totrash=0
AND
    `um`.`delayed`=0".$filter."
UNION ALL
SELECT
    DISTINCT u.".($config->realnames ? "name" : "username")." AS displayname,
    u.id
FROM
    (#__users AS u
INNER JOIN
    #__uddeim AS um
ON
    u.id=um.toid)
WHERE
    um.fromid=".(int)$myself."
AND
    um.totrashoutbox=0".$filter."
AND um.systemflag=0"

Upvotes: 1

Related Questions