Jump to content

Fetching results from two tables


MDanz

Recommended Posts

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.

 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.