Link to home
Start Free TrialLog in
Avatar of Nathan Riley
Nathan RileyFlag for United States of America

asked on

mySQL Query Syntax

I have the following query that works, but doesn't provide the desired output.  As you can see I'm getting the data for company_id 2, 7, and 11 and ordering by the post_date.  The problem is that if say company #2 has 10 posts that are the most recent by post_date then my limited 5 results are all from the same company.

Is there a way to modify the query so I'll receive results from all companyID's within my limit?

select fp.id as postID, fp.company_id as coID, post, date_format(post_date,'%m/%d/%Y %h:%i %p') postDate, postMedia, name
                                         from fo_posts fp
                                         inner join fo_company co on fp.company_id = co.id
                                         where company_id in (2,7,11)
                                         order by post_date desc
                                         limit 5

Open in new window

Avatar of PortletPaul
PortletPaul
Flag of Australia image

That would require you to get the top 2 results from each company stitched together using UNION ALL
then get top 5 from that list of 6
Which tables do these columns come from (you should specify ALL columns with their source)

fp? co?

 , ?.post
 , date_format( ?.post_date, '%m/%d/%Y %h:%i %p') postDate
 , ?.postMedia
 , ?.NAME
e.g.
SELECT tp.id
 ,tp.company_id
 ,tp.post
 ,date_format(tp.post_date, '%m/%d/%Y %h:%i %p') postDate
 ,tp.postMedia
 ,tp.NAME
FROM (
     SELECT fp.id
      ,fp.company_id
      ,fp.post
      ,fp.postDate
      ,fp.postMedia
      ,fp.NAME
     FROM fo_posts fp
     WHERE company_id = 2
     ORDER BY fp.post_date DESC limit 2
     
     UNION ALL
     
     SELECT fp.id
      ,fp.company_id
      ,fp.post
      ,fp.postDate
      ,fp.postMedia
      ,fp.NAME
     FROM fo_posts fp
     WHERE company_id = 7
     ORDER BY fp.post_date DESC limit 2
     
     UNION ALL
     
     SELECT fp.id
      ,fp.company_id
      ,fp.post
      ,fp.postDate
      ,fp.postMedia
      ,fp.NAME
     FROM fo_posts fp
     WHERE company_id = 11
     ORDER BY fp.post_date DESC limit 2
     ) AS tp
/*
JOIN here, if needed
*/
ORDER BY tp.post_date DESC limit 5

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>"getting the data for company_id 2, 7, and 11 ...
     Is there a way to modify the query so I'll receive results from all companyID's within my limit?
"

The original requirement was for 3 companies to be present in the top 5 results

when did the requirements change?