How to union the data when extract from 2 or more tables???

[ICE]

Dear All,

Could u pls suggest me how to extract the data from 2 or more tables???
I have 3 tables.I want 3 fileds from table A,2 fields from table B and 1 field from table C.But I want all the data from those 3 tables(A,B,C).I mean it's not like a lookup.
There is some data from A(A.id) may not be in the table B or C.Also B.id may not be in the table A or C.But I want all data(A.id...,B.id...,C.id...) from those 3 tables.It's like union.But I cannot give union in my user defined sql statement when I extract the data from source table.Coz some required fields are duplicate(not exactly the same record.Some fields from those records are not the same but required fields are same) in my source table and when I use union cmd ,I see those kind of record as only once.So I cannot use the union command.I cannot use the lookup too.Do u have any idea???

Thanks in advance,

[loveojha2]

(not exactly the same record.Some fields from those records are not the same but required fields are same)

Looks like you are talking about a Join (for that use user defined sql query). Please provide some more information about what do you want to do?

[Andal]

Is there any problem with Trying UNION ALL

[ICE]

(not exactly the same record.Some fields from those records are not the same but required fields are same)

Looks like you are talking about a Join (for that use user defined sql query). Please provide some more information about what do you want to do?

Hi,

Actually I want to extract all the data from table A.Required fileds are col1,col3,col5.Then I also want to get all the data from table B(Required fileds are col1,col2,col4).Here the col1 is ID column.
I have been used the Union in my user defined sql when I extract the data by using ODBC stage but it doesn't work properly.It elminates the records that have same values in the required fields but different values in other fields.
Pls chk the following.....

I want to get the result as follows......

1,1,1,1,1(both tables have data with same id=1)
2,0,2,0,2(table A has ID=2 but not in table B.So )
3,3,0,3,0(table A has no ID=3 but in table B has)
4,4,4,4,4(both talbes A and B have ID 4)

I mean I also want to get all the records those are not in another table.
I cannot use Union All coz there is no such cmd in AS 400.

Any idea???

Thanks ,

[PhilipDuPlessis]

Alternatively, you may consider a full outer join.

An inner join will provide only records that match, a left join will provide all records in the left table and matching records in the right table, a right join will provide all records in the right table and matching records in the left table.

A full outer join provides an inner, a left and a right. Extremely powerful in recons.

Example
Consider you have to provide a recon between table a and table b of all matches, as well as all mismatches. a join is done as an example on cust_key. Your join would look something like this

Select
isnull(a.cust_key, b.cust_key) as cust_key,
case when a.cust_key is not null then 1 else 0 end as FoundA,
case when b.cust_key is not null then 1 else 0 end as FoundB
from table a as A full outer join table b as B on a.cust_key = b.cust_key

Matches would be where FoundA and FoundB are both equal to 1

Would that help you???

[ICE]

Don't you have the other way like using DS statages???


Thanks,

Alternatively, you may consider a full outer join.

An inner join will provide only records that match, a left join will provide all records in the left table and matching records in the right table, a right join will provide all records in the right table and matching records in the left table.

A full outer join provides an inner, a left and a right. Extremely powerful in recons.

Example
Consider you have to provide a recon between table a and table b of all matches, as well as all mismatches. a join is done as an example on cust_key. Your join would look something like this

Select
isnull(a.cust_key, b.cust_key) as cust_key,
case when a.cust_key is not null then 1 else 0 end as FoundA,
case when b.cust_key is not null then 1 else 0 end as FoundB
from table a as A full outer join table b as B on a.cust_key = b.cust_key

Matches would be where FoundA and FoundB are both equal to 1

Would that help you???

[ray.wurlod]

Include the duplicates with UNION ALL instead of UNION - should remove your objection to UNION.

[ICE]

I got it.

Thank u so much......

Include the duplicates with UNION ALL instead of UNION - should remove your objection to UNION.

[Andal]

Andal Wrote:
Is there any problem with Trying UNION ALL

ICE Wrote:
I cannot use Union All coz there is no such cmd in AS 400.

got it.

Thank u so much......



ray.wurlod wrote:
Include the duplicates with UNION ALL instead of UNION - should remove your objection to UNION.

Contradictions