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

Postgresql – JOIN seems to multiply rows

I have two tables:

Table history_features

Table history_banenrs

I need to get info from table 1 and table 2 by id_banner (count of rows for equal id_banner is equal)
For example, getting info for id_banner = 51 will post 3 different rows from two tables, but for id_banner = 49 – two rows.
I made this request for id_banner = 51, but features_id and tag_list are duplicating.

SELECT 
  bs.id, bs.title_banner, bs.text_banner, bs.url_banner, bs.banner_state, bs.created_at, bs.updated_at, t.tag_list, t.features_id 
FROM 
  history_banenrs bs 
JOIN 
  (SELECT DISTINCT ON (id_banner) id_banner, tag_list, features_id FROM history_features WHERE id_banner = 51) t 
ON 
  bs.id_banner = t.id_banner;

Resulting table

Need help to make those columns not duplicating.

Tags:

2

Answers


  1. 0

    Going out on a limb, this may be what you want – for a single, given id_banner:

    SELECT *
FROM  (
   SELECT id, title_banner, text_banner, url_banner, banner_state, created_at, updated_at
   FROM   history_banenrs
   WHERE  id_banner = 51
   ORDER  BY id   -- or whatever selects the best row
   LIMIT  1
   ) b
CROSS JOIN  (
   SELECT id_banner, tag_list, features_id
   FROM   history_features
   WHERE  id_banner = 51
   ORDER  BY id   -- or whatever selects the best row
   LIMIT  1
   ) t;

    You get a single row – if both tables have any qualifying rows.

    The CROSS JOIN eliminates rows where not both tables have a match. You may want to use FULL JOIN instead to preserve results where the other side is missing.

    Should be as fast as it gets – with an index on (id_banner) for either table. If there are many dupes, make it a multicolumn index on (id_banner, id).

    Login or Signup to reply.
  2. 0

    Just do a simple JOIN.
    I need more information about t.tag_list & t.features_id,are these two fields calculated?If not,duplicating is normal.

    SELECT 
  bs.id, bs.title_banner, bs.text_banner, bs.url_banner, bs.banner_state, bs.created_at, bs.updated_at, t.tag_list, t.features_id 
FROM 
  history_banenrs bs 
JOIN 
  history_features  t 
ON 
  bs.id_banner = t.id_banner;
WHERE 
  bs.id_banner = 51
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search