SQL statement Join three tables

Question

i have the following statement

SELECT di_id, 
       di_name, 
       di_location, 
       ig_name, 
       in_latitude, 
       in_longitude
FROM dam_info
LEFT JOIN instrument_group 
       ON ig_diid = di_id
LEFT JOIN instruments 
       ON in_igid = ig_id;

which returned the result as follow

di_id  di_name        di_location           ig_name                 in_latitude   in_longitude
13     Macap          "Kluang, Johor"       "Standpipe Piezometer"  1.890895      103.266853
13     Macap          "Kluang, Johor"       "Standpipe Piezometer"  1.888353      103.267067
1      "Timah Tasoh"  "Kangar, Perlis"      NULL                    NULL          NULL
2      "Padang Saga"  "Langkawi, Kedah"     NULL                    NULL          NULL
3      "Bukit Kwong"  "Pasir Mas, Kelantan" NULL                    NULL          NULL
4      "Bukit Merah"  "Kerian, Perak"       NULL                    NULL          NULL
5      Gopeng         "Gopeng, Perak"       NULL                    NULL          NULL
6      Repas          "Bentong, Pahang"     NULL                    NULL          NULL
7      Batu           "Gombak, Selangor"    NULL                    NULL          NULL
8      Pontian        "Rompin, Pahang"      NULL                    NULL          NULL
9      "Anak Endau"   "Rompin, Pahang"      NULL                    NULL          NULL
10     Labong         "Mersing, Johor"      NULL                    NULL          NULL
11     Bekok          "Batu Pahat, Johor"   NULL                    NULL          NULL
12     Sembrong       "Batu Pahat, Johor"   NULL                    NULL          NULL
14     Perting        "Bentong, Pahang"     NULL                    NULL          NULL
15     Beris          "Sik, Kedah"          NULL                    NULL          NULL

as you can see from the result, there are repeated row which i would like to eliminate one of those and if the table instruments have more rows to return, then I only want one.

What is the correct statement of achieving that?

thanks in advance

`di_id` is not unique so the query return all left table row, you can use group by `di_id` field — Girish, Feb 26 '14 at 06:00
Are you only bothered by the `di_id` repeating? If so you need to pick which latitude and longitude you want to keep. — Hart CO, Feb 26 '14 at 06:07
I CAN'T see repeated rows. For di_id = 13 there are different `in_latitude` and `in_longitude`. So if you want to leave only one distinct di_id you have to decide what to do with different fields such as `in_latitude`. DISTINCT can't help here you can use GROUP BY but what to do with `in_latitude`? — valex, Feb 26 '14 at 08:02

score 2 · Accepted Answer · edited Feb 26 '14 at 10:15

2

you could also use a group by clause:

SELECT di_id, 
       di_name, 
       di_location, 
       ig_name, 
       in_latitude, 
       in_longitude 
FROM   dam_info 
       LEFT JOIN instrument_group 
              ON ig_diid = di_id 
       LEFT JOIN instruments 
              ON in_igid = ig_id 
GROUP  BY di_id;

edited Feb 26 '14 at 10:15

Hawk

5,060
12
49
74

answered Feb 26 '14 at 08:06

Rat-a-tat-a-tat Ratatouille

7,109
5
31
41

thanks @Rat-a-tat-a-tat. This statements work for now. I would update when more data are added later – Muhaimin Feb 26 '14 at 08:38

score 1 · Answer 2 · edited Feb 26 '14 at 10:29

1

Try to add the keyword DISTINCT after the select keyword.

SELECT DISTINCT di_id, 
                di_name, 
                di_location, 
                ig_name, 
                in_latitude, 
                in_longitude 
FROM   dam_info 
       LEFT JOIN instrument_group 
              ON ig_diid = di_id 
       LEFT JOIN instruments 
              ON in_igid = ig_id;

edited Feb 26 '14 at 10:29

Hawk

5,060
12
49
74

answered Feb 26 '14 at 06:03

Musaddiq Khan

23
5

Would not work, because of the different `latitude` and `longitude`. – Daniel Sparing Feb 26 '14 at 08:15

Daniel Sparing · Answer 3 · 2014-02-26T08:21:36.393

1

Looking at your schema it seems to me that it is on purpose that one di_id contains multiple instruments, i.e. the instruments table contains multiple records per in_igid, as the phrase 'instrument_group' suggests.

One meaningful thing you could do is to return the bounding box of your instruments:

SELECT di_id, di_name, di_location, ig_name,
    MIN(in_latitude) as min_latitude,
    MAX(in_latitude) as max_latitude,
    MIN(in_longitude) as min_longitude,
    MAX(in_longitude) as max_longitude
FROM dam_info
left join instrument_group on ig_diid = di_id
left join instruments on in_igid = ig_id
GROUP BY di_id, di_name, di_location, ig_name;

edited Feb 26 '14 at 08:21

answered Feb 26 '14 at 08:14

Daniel Sparing

2,163
15
20

thanks @Daniel but i dont think i would need min or max value. But it helps – Muhaimin Feb 26 '14 at 08:32
@MuhaiminAbdul what do you need then from lat/lon? Nothing? An arbitrary one? And what is the key on the `instruments` table? – Daniel Sparing Feb 26 '14 at 08:44
I only want to refer a dam location based on the Lat/Lng. This coordinates will refer to any instruments listed in `instruments` table. Those instruments are group by an id of `instrument_group` table – Muhaimin Feb 27 '14 at 03:55

SQL statement Join three tables

3 Answers3