user3521051 user3521051 - 4 years ago 164
MySQL Question

query in mysql to combine parent child relationship

I am having 3 table in sql, as one parent and one child and one as grand child. :)

dependent tables in mysql

Now i am trying to write something like this

select p.id,p.well_no, c.pidd,c.cd_no, l.pid from lib_cd p,cd c, lib_cd_logs l where l.pid=c.pidd=p.id


to display when my id in parent and pidd in child and pid in grand child are same.

Is it possible to write something like above, if yes please help me

Answer Source

You are looking for a JOIN

SELECT
  p.id,
  p.well_no,
  c.pidd,
  c.cd_no,
  l.pid
FROM lib_cd p
  INNER JOIN cd c ON p.id = c.pidd
  INNER JOIN lib_cd_logs l ON p.id = l.pid

If you only want rows where p.id = 19 you can use where too

SELECT
  p.id,
  p.well_no,
  c.pidd,
  c.cd_no,
  l.pid
FROM lib_cd p
  INNER JOIN cd c ON p.id = c.pidd
  INNER JOIN lib_cd_logs l ON p.id = l.pid
WHERE p.id = 19
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download