CODEWITHSUNDEEP
phpmysql
Steve
Steve

Reputation: 49

UNION ALL - JOIN how to limit to 1

trying to limit per row... In my query i'm getting the results and the images for results however if there are for eg: member has 2 pictures, it'll show two pics and if ttimages has 30 pictures it'll result in showing 30 pictures etc etc

What i'm trying to get to is the limit one per result... showing one result from the table with one picture rather than x amount resulting in how many pictures are in the images table.

here is the sql...

$sql = "
SELECT DISTINCT members.lastlogin as timeline_date, members.memusername as 
timeline_title, members.town as timeline_body, memberimages.images as 
timeline_img FROM members JOIN memberimages ON members.memid = 
memberimages.memid 
UNION ALL
SELECT DISTINCT ttages.dateadded as timeline_date, ttages.title as 
timeline_title, ttages.body as timeline_body, ttages_images.image as 
timeline_img FROM ttages JOIN ttages_images ON ttages.agesid = 
ttages_images.ageid
UNION ALL
SELECT DISTINCT sellit_listings.dateadded as timeline_date, 
sellit_listings.sittitlename as timeline_title, sellit_listings.sitcontent as 
timeline_body, sellit_images.images as timeline_img FROM sellit_listings JOIN 
sellit_images ON sellit_listings.listid = sellit_images.listid
UNION ALL
SELECT DISTINCT property_listings.dateadded as timeline_date, 
property_listings.paddress as timeline_title, property_listings.plistdiscript 
as timeline_body, property_images.image as timeline_img FROM 
property_listings JOIN property_images ON property_listings.propid = 
property_images.propid
UNION ALL
SELECT DISTINCT event_listings.dateadded as timeline_date, 
event_listings.eventtitle as timeline_title, event_listings.eventdescript as 
timeline_body, event_images.image as timeline_img FROM event_listings JOIN 
event_images ON event_listings.eventid = event_images.eventid
ORDER BY timeline_date DESC  LIMIT 70";

Upvotes: 0

Views: 41

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

If you want one result row per member, ttage, etc. then group by them:

SELECT
  m.lastlogin as timeline_date,
  m.memusername as timeline_title,
  m.town as timeline_body,
  MIN(mi.images) as timeline_img 
FROM members m
JOIN memberimages mi ON m.memid = mi.memid
GROUP BY m.memid
UNION ALL
...

This is standard SQL. Some DBMS may require to use pseudo aggregation functions on all columns (i.e. MIN(m.lastlogin) instead of m.lastlogin etc.) or put them in the GROUP BY clause, though.

Upvotes: 1

Related Questions