StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdd an extra column with condition in Union Query
text
Body
copied!<p>i have stored user Posts in a table called posts with fields</p> <pre><code>id userid posttitle status </code></pre> <p>i have stored user favorited posts in fav_posts table with fields</p> <pre><code>id post_id userid </code></pre> <p>Post table</p> <p>Code:</p> <pre><code>id userid posttitle status 1 1200 title 1 Active 2 1200 title 2 Active 3 1200 title 3 Active 4 1200 title 4 Active 5 1201 title 5 Active 6 1201 title 6 Active 7 1201 title 7 Active 8 1201 title 8 Active </code></pre> <p>Fav_posts table Code:</p> <pre><code>id post_id userid 1 1 1200 2 2 1201 3 5 1202 4 6 1202 5 6 1201 6 8 1201 7 8 1200 8 7 1200 </code></pre> <p>Now if i input an user i have to get the records that exists in both table but should come only once and with a separate Field value ie if i entered id as 1200 the result should be</p> <p>Code:</p> <pre><code>post_id title post_came_in 1 title 1 BOTH 8 title 8 FAV 7 title 7 FAV 2 title 2 MYPOST 3 title 3 MYPOST 4 title 4 MYPOST </code></pre> <p>i am able to list out the union list but i don't know how to include the third field in the result set after checking and found that the same record comes in both My posts and favorited posts</p> <p>i used the below query to get the Union result</p> <pre><code>SELECT post_id,title FROM posts LEFT JOIN fav_posts ON posts.id = fav_posts.post_id WHERE fav_posts.userid = 1200 UNION ALL SELECT id AS post_id,title FROM posts WHERE userid = 1200 </code></pre> <p>Please tell me how could i include a checking criteria in the query</p> <p>Regards Anees</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload