CODEWITHSUNDEEP
mysqlsqljoin
Peter Brännström
Peter Brännström

Reputation: 35

Get information from child and childs child if column is null

I have been stuck for a while now. I have 3 tables with relationships and I am supposed to get information from child and "grandchild" if the column in the parent is null. Hard to explain, but let me visualize.

In Table1 I just use DATE to visualize a date, and NULL if there is no date.

Table1

ID Name   Leased Delivered
1  John   DATE   DATE
2  Peter  DATE   DATE
3  Martha DATE   NULL

Table2

Firstname Lastname PostalCode
John      Doe      50001
Peter     Johnson  20200
Martha    Peterson 39201

Table3

PostalCode Place
50001      New York
20200      California
39201      Texas

What I want to be able to do is to select the persons firstname, lastname, postalcode and place if the delivered date is null, but I do not know how. What I have been able to do is get the name of the person who has NULL as delivered date and get that exact persons last name, postal code and place. I know how to get the persons information out of table2 if the delivered date in table1 is null by using a inner join, but I have no idea how to get information from table3 aswell.

How do I match the name from table1 with a firstname on table2 and get that row and then match postalcode in table2 with postalcode with table3 and get that rows information.

With the example above the select statement should output this:

Firstname Lastname PostalCode Place
Martha    Peterson 39201      Texas

Upvotes: 1

Views: 63

Answers (2)

Deepika
Deepika

Reputation: 296

select t2.firstname, t2.lastname,t2.postalcode, t3.place from 
table1 t1
inner join table2 t2
on t1.name = t2.firstname
inner join table3 t3
on t2.postalcode = t3.postalcode
where t1.delivered is null

Upvotes: 0

GMB
GMB

Reputation: 222462

You seem to want joins:

select
    t2.firstname,
    t2.lastname,
    t2.postalcode,
    t3.place
from table1 t1
inner join table2 t2 on t2.firstname = t1.name
inner join table3 t3 on t3.postalcode = t2.postalcode
where t1.delivered is null

Upvotes: 1

Related Questions