Entire row skipped because of single missing value

Databases
1

So I have “perfectly” written SQL query. It contains 1 minimized (within parenthesizes) SQL query and two INNER JOINs. The query works. Except for a fact that it skips single row, if one of the results is null.

What it should list is ID’s 1, 2, 3, 4, 5, 6. But because part of one of the results is null. It prints only 1, 2, 4, 5, 6.

It lists 5 forums, because 5 of them have artificial threads. But one of them is empty. And is unaccessible to users. Any idea how to force — if something is null?

I would prefer try fixing it without revealing query (not biggest fan).

2

I can’t imagine anyone is going to be able to comment without seeing the query @NoFlag

3

INNER JOIN will only return values if the comparison value exists on BOTH of the entities being compared.

If you want to show the items that are in one entity regardless of whether an item is in the second entity, you have to use OUTER JOIN.

So, say you have this query

SELECT t1.Field1
     , t2.Field2
     , t1.Field3
     , t2.Field4
  FROM table1 t1
 INNER JOIN table2 t2 ON t1.tablekey = t2.tablekey

If you want to show all of the table1 records regardless if there is a matching record on table2, you would use the LEFT OUTER JOIN

SELECT t1.Field1
     , t2.Field2
     , t1.Field3
     , t2.Field4
  FROM table1 t1
  LEFT OUTER JOIN table2 t2 ON t1.tablekey = t2.tablekey

Conversely, if you want to show the records on Table2 regardless of whether there is a matching record on Table1, you would use a RIGHT OUTER JOIN

SELECT t1.Field1
     , t2.Field2
     , t1.Field3
     , t2.Field4
  FROM table1 t1
 RIGHT OUTER JOIN table2 t2 ON t1.tablekey = t2.tablekey
4

Well, if I strip the values from query, it looks like this:

SELECT
	alpha.id,
	alpha.username,
	beta.id,
	beta.name,
	beta.description,
	beta.lastTopicId,
	(SELECT COUNT(*) FROM topics WHERE gamma.parent = beta.id) nonImportantValue,
	gamma.id
FROM beta
	INNER JOIN gamma ON beta.lastTopicId = gamma.id
	INNER JOIN alpha ON gamma.id = alpha.id

There might be some errors in here. But I can’t test it, the original query is working without error. I assign alias to SELECT values.

INNER JOIN alpha ON gamma.id = alpha.id. Will sometimes return null. There is no “last user” who wrote in “empty forum”, so this is where query gives up (but doesn’t produce error) and sends in “yep, empty result, let’s skip it, can’t finish query and bring the result, if one of them is empty”.

5

I know how INNER JOIN works, thank you.
The problem is bypassing.
I know it needs both values. Unfortunately it’s not always suppliable.

6

The problem is the approach. It seems like you don’t really care whether or not there’s a gamma record, but you’re using it as an intermediary. A better approach would be to tie beta and alpha together, then tag gamma onto it. Basically like this…

SELECT alpha.id
     , alpha.username
     , beta.id
     , beta.name
     , beta.description
     , beta.lastTopicId
     , (SELECT COUNT(*) FROM topics WHERE gamma.parent = beta.id) nonImportantValue
     , gamma.id
  FROM beta
 INNER JOIN alpha ON alpha.id = beta.lastTopicId
  LEFT OUTER JOIN gamma ON beta.lastTopicId = gamma.id
1 Like
7

Yep, that’s it. null value is good too. Thank you.

8

Ah, crap… I forgot to change stuff before I requested help.

Sometimes beta.lastTopicId is literally NULL (not four letters, but an indicator) in database. As soon as I enabled that, rows disappears again. Any way to get that fixed? I’d like to pass null if it’s actually null, but it really must give me the result, even if partially empty.

Using this (but modified, changed names):

SELECT alpha.id
     , alpha.username
     , beta.id
     , beta.name
     , beta.description
     , beta.lastTopicId
     , (SELECT COUNT(*) FROM topics WHERE gamma.parent = beta.id) nonImportantValue
     , gamma.id
  FROM beta
 INNER JOIN alpha ON alpha.id = beta.lastTopicId
  LEFT OUTER JOIN gamma ON beta.lastTopicId = gamma.id
9

It lastTopicID is NULL, it’ll never match up a record because a primary key cannot be null.

10

Is there no way to say “if this is null” then “set everything relying on this null too”?

11

that would be a basic LEFT OUTER JOIN

or maybe i don’t understand the question

1 Like
12

Hello.

I already use LEFT OUTER JOIN. This is the template I based myself on (modified names to suit my needs):

SELECT alpha.id
     , alpha.username
     , beta.id
     , beta.name
     , beta.description
     , beta.lastTopicId
     , (SELECT COUNT(*) FROM topics WHERE gamma.parent = beta.id) nonImportantValue
     , gamma.id
  FROM beta
 INNER JOIN alpha ON alpha.id = beta.lastTopicId
  LEFT OUTER JOIN gamma ON beta.lastTopicId = gamma.id
13

You’ll need to change the INNER JOIN on alpha to LEFT OUTER JOIN.

14

Ah, I see now. Thanks.

Something you’d think you know something, and it turns out you know nothing, and you need to wait until someone points obvious out :smiley: .

Thanks.

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.