COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: peewan on 19 Jun 2020 11:50:50 AM

Title: Join queries
Post by: peewan on 19 Jun 2020 11:50:50 AM
Hi all, I have 2 queries need to join together by ID.

Medical Query: list all member had medical
Vision Query: list all member had Vision coverage

I want to get All members who had Medical with no Vision. (If member had Medical and Vision, they still not count in)

How do I do the join?

Left join: 1:1, 0:n
Right join:0:n, 1:1
Full outer join: 0:n, 0:n

Thank you,

Title: Re: Join queries
Post by: MFGF on 19 Jun 2020 02:36:30 PM
Quote from: peewan on 19 Jun 2020 11:50:50 AM
Hi all, I have 2 queries need to join together by ID.

Medical Query: list all member had medical
Vision Query: list all member had Vision coverage

I want to get All members who had Medical with no Vision. (If member had Medical and Vision, they still not count in)

How do I do the join?

Left join: 1:1, 0:n
Right join:0:n, 1:1
Full outer join: 0:n, 0:n

Thank you,

One way to do this is as follows:

Create a left outer join to begin with [Medical Query ID] 1:1 <--> 0:1 [Vision Query ID]

Then on the resultant joined query you need to filter eg [one of the items from the Vision Query] is null

Cheers!

MF.
Title: Re: Join queries
Post by: peewan on 19 Jun 2020 04:34:20 PM
Quote from: MFGF on 19 Jun 2020 02:36:30 PM
One way to do this is as follows:

Create a left outer join to begin with [Medical Query ID] 1:1 <--> 0:1 [Vision Query ID]

Then on the resultant joined query you need to filter eg [one of the items from the Vision Query] is null

Cheers!

MF.

Thank you :)