SQL Group by Child Table with conditions -

i have simplified bit ask question. run tables please.

i have 2 tables

parent table columns

parentid,parent_firstname,parent_lastname 

child table columns are

childid,child_firstname,child_lastname,parentid,parent_firstname,parent_lastname 

parent table has 1 record

1,joe,bloggs 

child table has 3 records

1,bob,lawrence,1,,  2,sam,hunt,null,joe,bloggs  3,sam,hunt,1,, 

i want able following using query

joe bloggs bob lawrence joe bloggs sam hunt 

but.. want able link parent , child table following

1)if there parentid on child table link using parentid 2)else compare parent_first , parent_lastname in child table of child tables 

thanks in advance

like this:

select   p.parent_firstname,   p.parent_lastname,   c.child_firstname,   c.child_lastname parent p inner join child c on p.parentid = c.parentid; 

see in action here:

• sql fiddle demo.

this give you:

| parent_firstname | parent_lastname | child_firstname | child_lastname | ------------------------------------------------------------------------- |              joe |          bloggs |             bob |       lawrence | |              joe |          bloggs |             sam |           hunt | 

---

note that: query join 2 tables using simple join using first condition link 2 tables:

1)if there parentid on child table link using parentid

and enough; give looking for, but second condition want link child table parent table it:

2)else compare parent_first , parent_lastname in child table of child tables

you can add following condition or:

 c.parentid null   , c.parent_firstname = p.parent_firstname  , c.parent_lastname  = p.parent_lastname 

something this:

select    p.parent_firstname,   p.parent_lastname,   c.child_firstname,   c.child_lastname parent p inner join child c on p.parentid = c.parentid                        or (c.parentid null                            , c.parent_firstname = p.parent_firstname                           , c.parent_lastname  = p.parent_lastname) 

this give same result, duplicate rows:

• sql fiddle demo

like this:

| parent_firstname | parent_lastname | child_firstname | child_lastname | ------------------------------------------------------------------------- |              joe |          bloggs |             bob |       lawrence | |              joe |          bloggs |             sam |           hunt | |              joe |          bloggs |             sam |           hunt | 

add distinct select eliminate duplicate rows.

---

disclaimer:

this assuming not looking recursive query.