left join？

[silywiwi]

   hi,all
   I have a requirement that integrate two table
   let me illustrate that below
   one table is:
   month1   count1
      1           56
      2           89
      3           23
      4           65
      5           43
      6           32
      7           22
   another table is
  month2   count2
      3           33
      4           98
      5           93
      6           89
      7           53

  I use the four fields  with dataItem ,and i give filter: month1 = month2
and the list in page display like :
month  count1     count2
  3           23         33
  4           65         98 
  5           43         93
  6           32         89
  7           22         53
but i want to it look like this:
month  count1     count2
1           56         0
2           89         0
3           23         33
  4           65         98 
  5           43         93
  6           32         89
  7           22         53

How can i do?   can left join do?  i donnot know how to use left join

any suggestion will be appreciated
thanks!

[acabugason]

Left join is probably a more efficient way of getting to your solution but I haven't done that in Cognos just yet. So I can't really tell you how to do it. However, I can offer you an alternative. Try using a union. Here's how I'd do it:

Create a Data Item with a hard-coded value of 0 in each query.

Query 1 ---> Month   
                  Count 1                     
                  Count 2 (make this 0)

Query 2 ---> Month   
                  Count 1 (make this 0)   
                  Count 2

It's important to have the same field names in both queries because ...

Union Query  ----> Month
                          sum(Count 1)
                          sum(Count 2)

I hope this helps.

[MDXpressor]

In the Query Explorer of Report Studio you have the opportunity to establish relationships (both outer and inner joins, as well as unions).

So in an abbreviated format, here is what you need to do:

Query:  Month1
Query:  Month2
Query:  Merged Months

Drag a new query into the working space of the query explorer (Merged Months).  Next drag a Join item to the right of Merged Months.  Drag Month1 into the top square of the relationship, and Month2 into the bottom square of the relationship.  Next, double-click the 2 yellow squares of the relationship, this will open the Joins Relationships dialog.  In here you can establish the Key relationships as well as Cardinality (Inner join vs. Outer join).  Attached is a gif of how the query structure should look.

-----

Edit

-----

Now that I have given you the solution using C8, I must tell you that you cannot create an outer join using ReportNet, except to use the Union process described by Silywiwi.

[almeids]

A minor clarification, the data items do not need to be the same name in the 2 unioned models, the query will take the names from the first model.  The models do need the same shape, i.e. number, data type, and order of data items.

[silywiwi]

  acabugason,thank you!
  I donnot know how to union the two query ,i cannot find union operation. write sql? 
  sorry for novice
   
    thank MDXpressor too,i work in reportNet,so i cannot do follow your suggestion ,any way thanks for your reply

[silywiwi]

   hi,acabugason
  I use the tabular set to union the two table,and it is successful. is my operation to union right? ha..     thank you again
  i have a question that if i have more than two table to union,how to do?

[acabugason]

silywiwi - no need to apologize for being a novice. I am one myself. I just happened to have done the union join a little more than once before.

MDXpressor actually describes perfectly how to create a join. Follow his instructions carefully and it should get you to the point where you can select the type of join you want.

Also, make sure to follow almeids's advice as well.