Wrong SQL results - Inner Join vs. Left Join

Milo2009 — Fri, 29 Apr 2016 08:46:41 GMT

Hey,

I have a question concerning a SQL Select in QlikView. I have to join two tables (Table A and Table B) to get some specific data into one QlikView-Table.

Table A

ARTNR	B	C
1	...	...
2	...	...
3	...	...
4	...	...
5	...	...
6	...	...

Table B

NR	2NR	...
1	90	...
2		...
3	50	...
4	50	...
5		...
6	100	...

The blank fields in the column 2NR are Null Values in the database.

With the following SQL-Statement I got the expected result:

SQL Select

ARTNR,

2NR

FROM

TABLE_A

LEFT JOIN TABLE_B ON ARTNR = NR;

When I use an Inner Join instead a Left Join (what can be necessary in some use-cases) I got a wrong result:

SQL Select

ARTNR,

2NR

FROM

TABLE_A

INNER JOIN TABLE_B ON ARTNR = NR;

In the case of an Inner Join my result table looks like this:

ARTNR	B	C	2NR
1	...	...	90
2	...	...	90
3	...	...	50
4	...	...	50
5	...	...	50
6	...	...	100

I have marked the wrong field values red. As you can see there are values in the result table, although in Table B there aren't any values for the ARTNR 2 and 5 in the field 2NR. When I use a Left Join this field values are Null in my result table.

So does anybody have an idea, why I get the wrong results when using an Inner Join in QlikView - in MS Access I get the correct results when using an Inner Join.

Thanks in advance:)

Re: Wrong SQL results - Inner Join vs. Left Join

sunny_talwar — Fri, 29 Apr 2016 22:40:13 GMT

I don't know why the numbers are not coming out right, but you need to realize that you are not joining (inner or left) in QlikView. The joins are taking place in SQL also. QlikView is just picking up the value of those joins and displaying them. May be something else in your script might be doing this? You have only pasted the SQL code, so difficult to see what the issue could be. But I would suggest exploring options beyond just the joins cause to me it doesn't make sense that QlikView or SQL will arbitrarily pick a value from above and display it.

topic Re: Wrong SQL results - Inner Join vs. Left Join in QlikView

Wrong SQL results - Inner Join vs. Left Join

Re: Wrong SQL results - Inner Join vs. Left Join