0

I need to join three tables to get all the info I need. Table a has 70 million rows, after joining a with b, I got 40 million data. But after I join table c, which has only 1.7 million rows, it becomes 300 million rows.

In table c, there are more than one same pt_id and fi_id, one pt_id can connect to many different fi_id, but one fi_id only connects to one same pt_id.

I'm wondering if there is any way to get rid of the duplicate rows, cause I join table c only to get the pt_id.

Thanks for any help!

select c.pt_id,b.fi_id,a.zq_id
from a 
inner join (select zq_id, fi_id from b) b
on a.zq_id = b.zq_id
inner join (select fi_id,pt_id from c) c
on b.fi_id = c.fi_id
Binke
  • 29
  • 7
  • If there are multiple records in `c` that have the same `fi_id`, then you'll get separate result rows for each copy. Perhaps you need `SELECT UNIQUEROW`. – Tim Roberts Apr 06 '21 at 03:52
  • Thanks for the advice. Is it like select distinct c.pt_id,b.fi_id,a.zq_id? Because I have many other columns that needed to select, it fails after I add distinct. – Binke Apr 06 '21 at 06:27
  • Unrelated, but: `inner join (select zq_id, fi_id from b) b on ...` can be simplified to `inner join b on ...` the derived tables (sub-queries) are completely useless –  Apr 06 '21 at 06:30
  • Thanks. It did help to simplify the code. – Binke Apr 06 '21 at 06:34
  • Tag your question with the database you are using. – Gordon Linoff Apr 06 '21 at 12:17

1 Answers1

0

You can use GROUP BY

select c.pt_id,b.fi_id,a.zq_id
from a 
inner join (select zq_id, fi_id from b) b
on a.zq_id = b.zq_id
inner join (select fi_id,pt_id from c) c
on b.fi_id = c.fi_id
group by c.pt_id,b.fi_id,a.zq_id

to remove all duplicate row as question below:

How do I (or can I) SELECT DISTINCT on multiple columns?

Nguyễn Văn Quyền
  • 350
  • 1
  • 10
  • Thanks for the advice, but don't know why, I got failure on using group by after all the inner join, can't even group by one col, also the select distinct method. – Binke Apr 06 '21 at 06:22
  • @Binke can you show your failure message? – Nguyễn Văn Quyền Apr 06 '21 at 06:33
  • Because the database I used is company inside database, no idea how they designed it, but when you fail, it all returns sql failure without telling you what the specific problem is. – Binke Apr 06 '21 at 06:40
  • I'm pretty sure a inner b is correct, and I got zq_id and fi_id, is there any other way to just add the pt_id based on the fi_id? – Binke Apr 06 '21 at 06:42