#kasus joining table visits, patients, villages
#penggunaan select * , select by field yang ditentukan

SELECT * FROM
    `visits` V, patients P,
    villages Vl

WHERE

    V.patient_id = P.id
    AND V.village_id = Vl.id

Showing rows 0 - 24 (352759 total, Query took 0.1554 seconds.)


SELECT V.nama,V.nama_kk,Vl.desa,P.alamat
    FROM
    `visits` V, patients P,
    villages Vl

WHERE

    V.patient_id = P.id
    AND V.village_id = Vl.id

Showing rows 0 - 24 (352759 total, Query took 0.0036 seconds.)


SELECT * FROM `visits` V inner join patients P on V.patient_id=P.id inner join villages Vl on Vl.id = V.village_id

Showing rows 0 - 24 (352759 total, Query took 0.0031 seconds.)


-------------------------------------------------------------------------


# kasus joining table visits -> table anamnesis

select * from visits V, anamnesis An
where V.id=An.visit_id

 Showing rows 0 - 24 (47943 total, Query took 0.0946 seconds.)


 select * from visits V
 left join anamnesis An on V.id=An.visit_id

  Showing rows 0 - 24 (362217 total, Query took 0.0996 seconds.)


 select * from visits V
 inner join anamnesis An on V.id=An.visit_id

  Showing rows 0 - 24 (47943 total, Query took 0.0027 seconds.)


  ------------------------------------------------------------------------

  sample relasional 5 table

#select custom field from 5 related table (use join)

  select V.nama, V.tanggal,V.nama_kk from visits V inner join anamnesis An on V.id=An.visit_id inner join patients P on V.patient_id=P.id inner join villages Vl on V.village_id=Vl.id inner join orchards Orc on V.orchard_id=Orc.id


     Showing rows 0 - 24 (33849 total, Query took 0.0055 seconds.)


#select all field from 5 related table (use join)

  select * from visits V inner join anamnesis An on V.id=An.visit_id inner join patients P on V.patient_id=P.id inner join villages Vl on V.village_id=Vl.id inner join orchards Orc on V.orchard_id=Orc.id


   Showing rows 0 - 24 (33849 total, Query took 0.0058 seconds.)

---------------------------

#select custom field from 5 related table

  select V.tanggal, P.nama, Vl.desa, Orc.dusun
    from visits V, anamnesis An, patients P, villages Vl, orchards Orc
  where
    V.id=An.visit_id
    AND V.patient_id=P.id
    AND Vl.id = V.village_id
    AND V.orchard_id = Orc.id

 Showing rows 0 - 24 (33849 total, Query took 0.0055 seconds.)



#select all field from 5 related table

select *
    from visits V, anamnesis An, patients P, villages Vl, orchards Orc
  where
    V.id=An.visit_id
    AND V.patient_id=P.id
    AND Vl.id = V.village_id
    AND V.orchard_id = Orc.id

  Showing rows 0 - 24 (33849 total, Query took 0.0047 seconds.)


  -------------------------------------------------------------------


  where / having

#menggunakan WHERE

  select *
    from visits V, anamnesis An, patients P, villages Vl, orchards Orc
  where
    V.id=An.visit_id
    AND V.patient_id=P.id
    AND Vl.id = V.village_id
    AND V.orchard_id = Orc.id
    AND V.patient_id = '03020001'

    Showing rows 0 - 0 (1 total, Query took 0.0786 seconds.)


    select V.nama,V.nama_kk
    from visits V, anamnesis An, patients P, villages Vl, orchards Orc
      where
        V.id=An.visit_id
        AND V.patient_id=P.id
        AND Vl.id = V.village_id
        AND V.orchard_id = Orc.id
        AND V.patient_id = '03020001'


     Showing rows 0 - 0 (1 total, Query took 0.0055 seconds.)


#menggunakan HAVING

select *
    from visits V, anamnesis An, patients P, villages Vl, orchards Orc
  where
    V.id=An.visit_id
    AND V.patient_id=P.id
    AND Vl.id = V.village_id
    AND V.orchard_id = Orc.id

    having V.patient_id = '03020001'

    Showing rows 0 - 0 (1 total, Query took 0.3835 seconds.)


select V.nama,V.nama_kk, V.patient_id
    from
        visits V, anamnesis An, patients P, villages Vl, orchards Orc
    where
    V.id=An.visit_id AND V.patient_id=P.id AND Vl.id = V.village_id AND V.orchard_id = Orc.id having V.patient_id = '03020001'

    having V.patient_id = '03020001'

    Showing rows 0 - 0 (1 total, Query took 0.2444 seconds.)