CODEWITHSUNDEEP
mysql
Tsvetomirov Yordan
Tsvetomirov Yordan

Reputation: 77

How to target same column twice with different conditions and get multiple results?

I have been on this problem for 3 days already..and i cannot really fix it.So i have a table in which i must target same column named "guid" twice.And get two different results from it i tried all kind of joins and subqueries but didn't seem to work.This is my last query,which is working,but not as expected there it is

<?php
$newquery = "
SELECT post_title
     , guid
     , (SELECT guid AS guid1 FROM wp_posts WHERE post_mime_type LIKE '%image%' AND post_type = 'attachment')
  FROM wp_posts 
 WHERE comment_status = 'open' 
   AND post_type = 'post' 
 ORDER 
    BY post_date DESC 
 LIMIT 0,5";
?>

The problem is there that sub-query return only 1 row with results,but i need 5 and i don't really know how can it be made,if there is somebody who can help,please do it <3

Upvotes: 1

Views: 64

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Your subquery is not correlated. So, if the subquery can return more than one row, you can use cross join like this:

select post_title,
    guid,
    guid1
from wp_posts t
cross join (
    select guid as guid1
    from wp_posts
    where post_mime_type like '%image%'
        and post_type = 'attachment'
    ) t2
where comment_status = 'open'
    and post_type = 'post'
order by post_date desc LIMIT 0,
    5

Upvotes: 1

Related Questions