JOIN seems to multiply rows

ghz 8months ago ⋅ 73 views

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.

Answers

To avoid duplicating features_id and tag_list columns while joining history_banners and history_features tables by id_banner, you can use a common table expression (CTE) to first calculate the counts for each id_banner, and then join the tables using this count as a condition. Here's how you can do it:

WITH banner_counts AS (
    SELECT
        id_banner,
        COUNT(*) AS count_per_banner
    FROM
        history_features
    GROUP BY
        id_banner
)
SELECT
    hb.id,
    hb.title_banner,
    hb.text_banner,
    hb.url_banner,
    hb.banner_state,
    hb.created_at,
    hb.updated_at,
    hf.tag_list,
    hf.features_id
FROM
    history_banners hb
JOIN
    (SELECT
         id_banner,
         tag_list,
         features_id
     FROM
         history_features
     WHERE
         id_banner = 51) hf
ON
    hb.id_banner = hf.id_banner
JOIN
    banner_counts bc
ON
    hb.id_banner = bc.id_banner
    AND bc.count_per_banner = 1; -- Adjust the count condition as needed

In this query:

  1. The common table expression (CTE) named banner_counts calculates the count of rows for each id_banner in the history_features table.

  2. Then, in the main query, we join history_banners and history_features tables based on id_banner. We also join the banner_counts CTE to ensure that only those rows with a count of 1 for the corresponding id_banner are selected.

By doing this, you ensure that each row from history_banners is joined only with a single row from history_features, thereby avoiding duplicating features_id and tag_list columns. Adjust the count condition in the join clause with banner_counts as needed based on your specific requirements.

#sql