CODEWITHSUNDEEP
sqlsql-servert-sql
Jon Lachonis
Jon Lachonis

Reputation: 931

SQL Query With UNION Returning One Less Than Expected

I'm using this query in an attempt to retrieve 4 galleries:

(SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
 WHERE a.pid = b.previewpic and galleryid = '567' limit 1) 
UNION ALL 
   (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
    WHERE a.pid = b.previewpic and galleryid = '541' limit 1) 
UNION ALL 
   (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
    WHERE a.pid = b.previewpic and galleryid = '484' limit 1) 
UNION ALL 
   (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
    WHERE a.pid = b.previewpic and galleryid = '545' limit 1)

However, it returns 3. If I duplicate the last query, it returns 4. Any ideas why?

This works but is messy:

(SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
 WHERE a.pid = b.previewpic and galleryid = '567' limit 1) 
 UNION ALL 
      (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
       WHERE a.pid = b.previewpic and galleryid = '541' limit 1) 
 UNION ALL (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
      WHERE a.pid = b.previewpic and galleryid = '484' limit 1) 
 UNION ALL 
     (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
      WHERE a.pid = b.previewpic and galleryid = '545' limit 1) #
 UNION ALL 
      (SELECT * FROM edgewe_ngg_pictures a, edgewe_ngg_gallery b 
      WHERE a.pid = b.previewpic and galleryid = '545' limit 1)

Upvotes: 2

Views: 63

Answers (1)

Beth
Beth

Reputation: 9607

try:

SELECT * 
FROM 
edgewe_ngg_pictures p inner join
edgewe_ngg_gallery g on
p.pid = g.previewpic
WHERE  
galleryid in( '567','541','484','545' )

Upvotes: 1

Related Questions