CODEWITHSUNDEEP
sqlpostgresqljoinlimit
cad
cad

Reputation: 337

Not able to join two tables with limit in Postgres

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

Upvotes: 0

Views: 537

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656401

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.

Upvotes: 3

user330315
user330315

Reputation:

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.

Upvotes: 0

Related Questions