Fetching results from two tables

[MDanz]

I'm trying to do a following feed. This feed includes posts and comments that the user is following.

The first 3 following types are in the content table and the last following type is in the comments table.

 

This query fetches the first 3 following types successfully.

$construct = $connectdb->prepare("SELECT parent.*
FROM `content` as parent
JOIN followers ftable on (
(ftable.parent_id=parent.sid AND ftable.userposts='1')
OR
(ftable.`parent_id`=parent.pageid AND ftable.topic='1')
OR
(ftable.parent_id=parent.pageid AND ftable.page='1')
) AND ftable.userid=:userid
WHERE parent.deleted='0'
GROUP BY parent.id
ORDER BY parent.posted DESC");

This query fetches the following comments type successfully

$construct = $connectdb->prepare("SELECT parent.*
FROM `comments` as parent
JOIN followers ftable on ftable.parent_id=parent.postid AND ftable.comments='1' AND ftable.userid=:userid
WHERE parent.deleted='0'
ORDER BY parent.posted DESC");

How do i combine the two queries? In this feed i want the results to be ORDER by posted DESC.

 

[Barand]

Use a UNION

SELECT parent.*
FROM `content` as parent
JOIN followers ftable on (
(ftable.parent_id=parent.sid AND ftable.userposts='1')
OR
(ftable.`parent_id`=parent.pageid AND ftable.topic='1')
OR
(ftable.parent_id=parent.pageid AND ftable.page='1')
) AND ftable.userid=:userid
WHERE parent.deleted='0'

UNION
SELECT parent.*
FROM `comments` as parent
JOIN followers ftable on ftable.parent_id=parent.postid AND ftable.comments='1' AND ftable.userid=:userid
WHERE parent.deleted='0'

ORDER BY parent.posted DESC

You will have to ensure the same column structure is returned by each part of the UNION

Edited October 19, 2014 by Barand

[MDanz]

Is the column structure only required in the SELECT or do i have to have the same amount of JOINS for each query?

[Barand]

You are appending the results of the second part to the first and combining into a single set of results, so you need to make sure you select the same column structure. For example, you cannot select 5 columns in the first part and 6 in the second. Also corresponding column should be of the same type.

[Barand]

Examples

--OK
  SELECT id, name, colour FROM fruit
  UNION
  SELECT id, manufacturer, colour FROM car
  ORDER BY colour

-- WRONG:
   SELECT id, name, colour FROM fruit
   UNION
   SELECT id, manufacturer, colour, engine_size FROM car
   ORDER BY colour

-- OK
   SELECT id, name, colour, NULL as size FROM fruit
   UNION
   SELECT id, manufacturer, colour, engine_size FROM car
   ORDER BY colour

[MDanz]

OK...Can the queries have different amount of JOINS?

 

e.g.

SELECT child.id, child.name, child.colour FROM fruit child
LEFT JOIN tree parent on parent.type=child.type
LEFT JOIN country parent2 on parent2.name=parent.country
UNION
SELECT child.id, child.manufacturer, child.colour FROM car child
LEFT JOIN country parent on parent.name=child.country
ORDER BY colour

[Barand]

yes