Joining 4 tables of which one is unrelated

Question

I have 4 tables as follows.

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.

Answer 1

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;