Question 114885 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do i get all the values from 2 tables without doing a Cross Join

I have 2 tables with the following Schema

First ( id, user_id, user_agent, referrer, browser, device_type, IP)

Second ( id, user_id, name, properties)

Table First has a total of 512 entries for user_id 1. Table Second has total of 100 entries for user_id 1. Both tables track different User Activities, so anytime I try to join Table Second on First for user_id 1.

SELECT COUNT(*)
FROM first f
JOIN second AS s ON s.user_id = f.user_id
WHERE f.user_id = 1

I get a total of 51,200 returned rows. Definitely, a Cross Join (first * second) is being done. Is there no way I can get a less enormous returned result? perhaps first + second resul

Tags:

2

Answers


  1. 0

    use Left join and use the foreign key to query the two tables

    Login or Signup to reply.
  2. 0

    I think you can use UNION ALL operator.
    The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various

    Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types. So that you need create same column’s with null as ""

    Or you can try use UNION

    UNION removes duplicate rows.
    UNION ALL does not remove duplicate rows.

    select *
from(

    SELECT id, user_id, user_agent, referrer, browser, device_type, IP, null as "name",
        null as "properties"
        FROM first f
        
        
        UNION ALL
        
        SELECT id, user_id, null as "user_agent", null as "referrer", null as "browser",
        null as "device_type", null as "IP",   name, properties
        FROM second s) x
    Where user_id = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search