Query does not return rows

Happy new year to you!

I am having a strange problem today, which I can hardly understand! Thus I wanted to ask you!

I have a very simple query, which used to work as far as I remember. Now, however, the query doesn't return any rows at all. Can you please tell me what is wrong? CNT_GEBIET is a string just like lng_gebiet. Both are varchar2.

I simply need to output all records of table tbl_gebiet, which are non existent in tbl_arbeit_zu_gebiet.

I appreciate your help and am very curious if you have any ideas!

BR,
skahlert2010


Select distinct cnt_gebiet from VT_TBL_GEBIET where cnt_gebiet not in (Select distinct lng_gebiet from VT_TBL_ARBEIT_ZU_GEBIET)

Open in new window

skahlert2010Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
your query should work. For perfornance reason you should use a structure like this.
If the query dont return any rows then all the cnt_gebiet values have corresponding value in the lng_gebiet column.
SELECT distinct cnt_gebiet 
FROM VT_TBL_GEBIET T1
WHERE NOT EXISTS (	SELECT NULL 
					FROM VT_TBL_ARBEIT_ZU_GEBIET T2 
					WHERE T1.cnt_gebiet = T2.lng_gebiet)

Open in new window

0
 
skahlert2010Author Commented:
Okay, I just found out that the query did not return any results as I had null values for lng_gebiet in tbl_arbeit_zu_gebiet. I think it should work nevertheless!?
0
 
zadeveloperCommented:
The syntax looks good to me,
you could try a different approach:

Select 
	distinct t1.cnt_gebiet 
from 
	VT_TBL_GEBIET t1
	left outer join lng_gebiet t2 on t1.lng_gebiet = t2.lng_gebiet
where 
	t2.lng_gebiet is null

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Reza RadConsultant, TrainerCommented:
maybe you have all records in vt_tbl_arbeit_zu_gebiet and then query result no rows.
did you checked your table data? do you have any opposite sample ?
I mean do you have any rows in VT_tbl_gebiet that his cnt_gebiet has no correspond value to lng_gebiet in vt_tbl_arbeit_zu_gebiet ??
0
 
skahlert2010Author Commented:
Thanks for your rapid help!

I took tigin44's proposal and modified it to suit my needs. It works indeed a little faster! Splendid!

Thanks a lot!

@ Zadeveloper: Your query was not quite correct, but it works correctly now. It's just not as performant as the other one.

@reza_rad: Yeah, I checked my tables. TBL_GEBIET contains all regions. The only value in cnt_gebiet that is missing in lng_gbeite is null!
Select 
        distinct t1.cnt_gebiet 
from 
        VT_TBL_GEBIET t1
        left outer join tbl_arbeit_zu_gebiet t2 on t1.cnt_gebiet = t2.lng_gebiet
where 
        t2.lng_gebiet is null 

Open in new window

0
 
skahlert2010Author Commented:
Thanks!
0
 
zadeveloperCommented:
Glad you got it working.
0
 
skahlert2010Author Commented:
@ zadeveloper

I feel a little sorry. Maybe I can award you on another question! Have a great day!
0
 
zadeveloperCommented:
No problems man....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.