0

I have table A with col1,col2,col3 and Table B col1.

I want to join both tables using the limit

I want some thing like 

select a.col1,a.col2,a.col3,b.col1 
from tableA a, tableB b limit 5 and a.col1 between 1 AND 10;

So I have 10 records in table b and 10 in table a. I should get total of 50 records by limiting only 5 records from table b

cad
  • 337
  • 2
  • 15

2 Answers2

3

Your description translates to a CROSS JOIN:

SELECT a.col1, a.col2, a.col3, b.b_col1  -- unique column names
FROM   tablea a
CROSS  JOIN ( SELECT col1 AS b_col1 FROM tableb LIMIT 5 ) b;
-- WHERE  a.col1 BETWEEN 1 AND 10;  -- see below

... and LIMIT for tableb like a_horse already demonstrated. LIMIT without ORDER BY returns arbitrary rows. The result can change from one execution to the next.

To select random rows from tableb:

...
CROSS JOIN ( SELECT col1 AS b_col1 FROM tableb ORDER BY random() LIMIT 5) b;

If your table is big consider:

While you ...

have 10 records in ... table a

... the added WHERE condition is either redundant or wrong to get 50 rows.

And while SQL allows it, it rarely makes sense to have multiple result columns of the same name. Some clients throw an error right away. Use a column alias to make names unique.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin this is for one time query this works fine for me – cad Mar 26 '19 at 01:11
  • i have 30 records in table b and i want to select random values while using limit. i tried using SELECT a.col1, a.col2, a.col3, b.b_col1 -- unique column names FROM tablea a CROSS JOIN ( SELECT col1 AS b_col1 OFFSET floor(random()*30) LIMIT 30) b; since I'm using precision SELECT col1 AS b_col1 OFFSET floor(random()*30 it some times gives me 30 and some time 10 ..0. is there way i can get random values what ever mentioned in limit – cad Mar 27 '19 at 00:35
  • 1
    Didn't you accept the other answer? Also: defining info should go into the *question*, not comments. Either way, I added a solution above. – Erwin Brandstetter Mar 27 '19 at 01:36
0

You need a derived table (aka "sub-query") for that. In the derived table, you can limit the number of rows. 

select a.col1, a.col2, b.col3, b.col1
from tablea a
  join (
    select b.col3, b.col1
    from tableb
    limit 5 -- makes no sense without an ORDER BY
  ) b on b.some_column = a.some_column --<< you need a join condition
where a.col1 between 1 and 10;

Note that using LIMIT without an ORDER BY usually makes no sense.