I have 4 tables as follows. Tables at a glance

I want to find a wholesum resultset, limited to 25 records, in the following way:

  • Users from Table1 based on a condition
  • And those Users should not be in Table2 [Non restricted users]
  • Join those records to Table3 on Table1.Group_id = Table3.Group_MEMBER_ID where Table3.GroupName = 'someValue'
  • Join Table1 with Table4 on Table1.UserName = Table4.UserName

Am currently using two queries and am using UNION. Something like this:

      ( SELECT  Table_4.DeviceName
            from  Table1
            left join  Table2 on Table1.UserName = Table2.UserName
            inner join  Table4 on Table1.UserName = Table4.UserName
            where  Table1.some_column = 'someValue'
              and  Table2.UserName = null
            limit  25
      )
UNION
      ( SELECT  Table4.DeviceName
            from  Table1
            inner join  on Table1.Group_ID = Table3.GROUP_MEMBER_ID
            inner join  Table4 Table1.UserName = Table4.UserName
            where  Table3.GroupName='someValue'
              and  Table1.some_column = 'someValue'
            limit  25
      ); 

The point is, I cant limit the wholesum resultset to 25 records. I should use LIMIT to each query. Is there a way to merge it into a single query so that I can limit the wholesum resultset to 25?

Note: PL/SQL and CTE not available in my Development System and hence am not able to use it.

up vote 0 down vote accepted

Problem 1: LIMIT is somewhat meaningless without ORDER BY to indicate which 25.

Problem 2: Don't say x=null, it won't do what you think. Instead do x IS NULL.

Problem 3: Do you really want a "cross join"? That is, a JOIN without an ON clause, and hence gets all combinations of the two tables?

Problem 4: Well, I did not check the rest of what you have.

Back to your question... The pattern is

( SELECT ... ORDER BY ... LIMIT 25 )
UNION ALL  -- or DISTINCT, depending on your understanding of the data
( SELECT ... ORDER BY ... LIMIT 25 )
ORDER BY ... -- Yes, again
LIMIT 25;

If you need an offset of, say 100, then:

( ... LIMIT 125 )
UNION ...
( ... LIMIT 125 )
ORDER BY ...
LIMIT 100, 25;
  • Problem3: I used an ON clause. Forgot to mention that. Anyway Adding the Outer Order by and then limiting to 25 did the job (y) .. thanks! – rampireram Jan 15 '16 at 6:23

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.