Frank Frank - 4 months ago 8
SQL Question

Sql select inner joine and union 3 tables

I have been trying to union three and join one table, i was successfully in selecting using only inner join and also combining the inner join and union bu my problem is to get reply id i have tried many thing to display reply id but keep getting error while other work fine

Bellow sql give me this

//$dsnnot->prepare("SELECT * FROM replys r INNER JOIN blog_post b ON r.rid = b.BID WHERE b.UserName = :ownerna AND r.read = 1");

$dsnnot->prepare('SELECT * FROM (
SELECT "post" AS type, BID AS eid, blog_title AS title, Time AS stime, blog_body AS description, UserName AS owner FROM blog_post b
INNER JOIN replys r ON r.rid = b.BID WHERE b.action = "active" AND r.read = 1
UNION
SELECT "jaile" AS , jid AS eid, orgname AS title, jaildate AS stime, OrgDescription AS description, jailorgnowner AS owner FROM jailorgname
UNION
SELECT "articl" AS type, TID AS eid, Atitle AS title, Atime AS stime, Abody AS description, Auser AS owner FROM articlespost

) foo WHERE owner = :ownerna');


I was able to output by this


type > Show where it came from

eid > Show main post id's

title > Show post title

owner > Show post author

stime > Show time

description > Show post content


Now my problem is to show
replys
> RID, i want to also out put it in my php

Answer

You have to have the same number of columns and the columns from each SELECT must be of the same data type in the same order. So if you want the reply id you have to add a null to each select that you're not selecting from.

$dsnnot->prepare('
SELECT *
FROM (
  SELECT
    "post" AS type,
    BID AS eid,
    blog_title AS title,
    Time AS stime,
    blog_body AS description,
    UserName AS owner,
    r.replyID
  FROM blog_post b
  INNER JOIN replys r ON r.rid = b.BID
  WHERE b.action = "active" AND r.read = 1

  UNION

  SELECT
    "jaile" AS type,
    jid AS eid,
    orgname AS title,
    jaildate AS stime,
    OrgDescription AS description,
    jailorgnowner AS owner,
    null AS replyID
  FROM jailorgname

  UNION

  SELECT
    "articl" AS type,
    TID AS eid,
    Atitle AS title,
    Atime AS stime,
    Abody AS description,
    Auser AS owner,
    null AS replyID
  FROM articlespost
) foo
WHERE owner = :ownerna');
Comments