Outer join with where clause in the universe

[former_member340306]

Hi,

I have two tables such as:

Patient Table (P)                          Territory Table (T)

P. Alignment ID --------------------------   T.Alignment ID

P. Patient ID                                    T. Region

Two tables are joined on alignment ID, follow is how the data looks like

When i run the query only on Patient Table  i get the following

e.g.

Select P.Patient ID, P.Alignment ID

from Patient Table (P)

Where P.Data_date=P.Latest data date

----Note that there is a Where clause here always & i get the following result

P.Patient ID         P.Alignment ID      

    1                            1a

    2                             2a

    3                            3a

    4                            Null

    5                            Null

Now when i join two tables on Alignment ID

e.g.

Select P.Patient ID, P.Alignment ID,T.Region

from Patient Table (P)

LEFT OUTER  JOIN Territory Table (T)

ON P. Alignment ID =  T.Alignment ID

Where P.Data_date=P.Latest data date

AND T.Data_date=T.Latest data date

Following is the result:

P.Patient ID         P.Alignment ID      

    1                            1a

    2                            2a

    3                            3a

Even with the left outer join it is not brining in the Null values , it seems that because of the WHERE clause the LEFT OUTER is working as a INNER join.

How can i get the Null rows with my join to the territory table ?

Please Advise,

Thanks,

Sheikh

[former_member203850]

Yes , due to where clause its working as a inner join. Can you please tell me why you have added 2 more joins

Where P.Data_date=P.Latest data date

AND T.Data_date=T.Latest data date

Already you have added join between P. Alignment ID =  T.Alignment ID

Regards,

Pranay

[former_member340306]

Where clause ones are just condition, and not joins:

its is just getting the latest extract date for each table e.g.

P.Data_date=(select max(P.Latest data date) from Patient Table (P) )   --- gets the latest extract date

AND T.Data_date==(select max(T.Latest data date) from Territory Table (T)) ------ gets the latest extract date

I need to have these condition to get the max date --

[former_member340306]

So how can make that join so it gets the null rows and regardless of the where clause ??

[former_member190895]

Hi Ayub,

Can you send us some data from both table along with date.

[former_member340306]

its not letting me atatched the excel extract but the data is simple as i shown above e.g.

Patient Table (P)                          Territory Table (T)

P. Alignment ID --------------------------   T.Alignment ID

P. Patient ID                                    T. Region

Two tables are joined on alignment ID, follow is how the data looks like

When i run the query only on Patient Table  i get the following

e.g.

Select P.Patient ID, P.Alignment ID

from Patient Table (P)

Where P.Data_date='01-OCT-2014'

----Note that there is a Where clause here always & i get the following result

P.Patient ID         P.Alignment ID      

    1                            1a

    2                             2a

    3                            3a

    4                            Null

    5                            Null

Now when i join two tables on Alignment ID

e.g.

Select P.Patient ID, P.Alignment ID,T.Region

from Patient Table (P)

LEFT OUTER  JOIN Territory Table (T)

ON P. Alignment ID =  T.Alignment ID

Where P.Data_date='01-OCT-2014'

AND T.Data_date='01-OCT-2014'

Following is the result:

P.Patient ID         P.Alignment ID      

    1                            1a

    2                            2a

    3                            3a

Now the whole issue is that the condition in the WHERE clause is making it act like a inner join and iw ant the NULL rows to appear

Sheikh