OuterJoin in Query - I dont know why it not work

(0) Share

Report

Posted on by Community Member

Dear, I need one form where this show me the next query.

All items of InventTable, that they are associate or not to table InventItemLocation with itemId relation and JoinMode OuterJoin. InventItemlocation is associate with table InventCountGroup with CountGroupId relation and JoinMode InnerJoin. The problem is that in the form appears only the items that are relations with InventItemLocation and not all items.

What am I doing wrong?

Thanks for your help.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

You should use outer join instead of your inner join (for InventCountGroup). Imaging that SQL Server first joins InventTable and InventItemLocation (using the outer join) and than inner-joins it with InventCountGroup, removing all the records you wanted to keep.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Thanks for your help and explanation, I have very clear.

Was this reply helpful? Yes No
Suggested answer

5400 7,162 on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

your join looks wrong to me if you want to fetch entire itemID. you can not put inner join inside the query though outer join is present in header. Inner join will filter the header first then make join with root node.

If you want to fetch the entire data please make outerJoin.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Thanks for the answer, but the problem of leaving the internal realción outerjoin is doubling me information items, for the inner table has at least two records for each item, and that's not what I want. So I try to do what Martin says.

Thank you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Dear, I have a problem of duplication.

What should I do to not doubling ?. I thought I could do it from the same Query, but apparently not allow me to join tables in your relationship should be outerjoin, with others that are in your relationship should be innerjoin.

Thank you.

Was this reply helpful? Yes No
Martin Dráb 236,394 Most Valuable Professional on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Please give us more information about your problem.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Dear Martin, the InventItemLocation table contains at least two records for each item, and I need only show that has valid CountGrupId. For this reason I related the table as InnerJoin InventCountLocation the InventCountGroup table, because with this I will only items that have a valid code. If I apply myself to the table OuterJoin appears duplicate information for each item and this leads me to appear in the result more than one item.

To solve this and as a result of the combination of relationship OuterJoin a table, and the other InnerJoin is not correct, I did the following.

Create a Query with InventItemLocation tables and their relationship with InventCountGroup InnerJoin. This Query create a view. This view was the one added to the initial Query table containing the InventTable and interacted as OuterJoin on ItemId.

With this gives me the result I wanted, but as you can see is quite complex for something that is supposedly simple.

I hope that it can be performed more simply.

Thanks and regards.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

I'm not sure if you know what you are doing. There is multiple records in InventItemLocation for each item, so your joins are working as intended. Looking at the table the primary key is itemId and inventDimId. So in order to get a unique record from that table you would have to provide an invenDimId as well because the records are saved per dimension and can change for each specified dimension.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: OuterJoin in Query - I dont know why it not work

Thank you for your comments, and I'm sorry for not explain my objective.

I need show the following Query in a form.

select t1.ITEMID,t2.COUNTGROUPID
   from AXPRE..INVENTTABLE t1
       left join
       (
       select ITEMID,t10.COUNTGROUPID
           from AXPRE..INVENTITEMLOCATION t10
               inner join AXPRE..INVENTCOUNTGROUP t20 on t10.COUNTGROUPID=t20.COUNTGROUPID
       )t2 on t1.ITEMID=t2.ITEMID

I thought I could generate with a single query for this, but apparently It can not.

At end I could performed with two Querys and one View, but I thought that It could create a single Query for this.

Regards.

Was this reply helpful? Yes No