getting more records than expected with simple query in postgresql

Question

I have these query:

select b.type,b.name,a.name_b,a.start_time
         from runinfo a left JOIN definition b
                        on a.sched_table=b.parent_table
                        and a.name_b=b.name
                        where sched_table not like 'PLAN' 
         and to_char(start_time,'YYYYMMDD') = to_char(current_date-1,'YYYYMMDD')
         order by start_time desc;

Tables are:

DEFINITION
----------

TYPE: COMMAND
NAME: DAVE
.........

RUNINFO
-------

START_TIME: 2019/08/15 23:59
NAME_B: DAVE
.........
START_TIME: 2019/08/15 23:58
NAME_B: DAVE
........

I get duplicate rows and I can't understand:

TYPE     NAME   START_TIME           NAME_B
COMMAND  DAVE   2019/08/15 23:59     DAVE
COMMAND  DAVE   2019/08/15 23:59     DAVE
COMMAND  DAVE   2019/08/15 23:58     DAVE
COMMAND  DAVE   2019/08/15 23:58     DAVE

Why I get two rows of every record?

thanks and sorry for my English!

Joining a table with one record to a table with two records gives a result set with at most two records, period (even for a cross join). I speculate that your starting tables already have duplicate data. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 16, 2019 at 10:16
But I would like to extract the results of table runfinfo, add the column type of the other table. Is possible to make that? Thanks! — absolute333
– absolute333, Commented Aug 16, 2019 at 10:20
You can use select distinct to handle the symptom, but your underlying tables would seem to have duplicates. — Gordon Linoff
– Gordon Linoff, Commented Aug 16, 2019 at 12:15

Ankit Bajpai · Accepted Answer · 2019-08-16 12:22:47Z

1

I guess you need an INNER JOIN rather than LEFT JOIN -

SELECT DISTINCT b.type,b.name,a.name_b,a.start_time
FROM runinfo a
INNER JOIN definition b ON a.sched_table=b.parent_table
                       AND a.name_b=b.name
WHERE sched_table NOT LIKE '%PLAN%' 
AND TO_CHAR(start_time,'YYYYMMDD') = TO_CHAR(current_date-1,'YYYYMMDD')
ORDER BY start_time DESC;

Also I have updated your Like predicate.

edited Aug 16, 2019 at 12:22

answered Aug 16, 2019 at 11:00

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ankit Bajpai Over a year ago

There must be some duplicates in your underlying tables. I have added DISTINCT clause. This might solve your problem.

Collectives™ on Stack Overflow

getting more records than expected with simple query in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related