Advanced SQL Query ( And condition )

Advanced SQL Query ( And condition )

  
Hi,

I created an Entity to relate Cases.
And I want to populate a Combo Box with all the Cases that are not the CaseId in question (Case_Show Web Screen), and not already a related case from the Case.

SELECT  {Case}.* 
FROM {Case} LEFT OUTER JOIN {RelatedCases}
ON {Case}.[Id] = {RelatedCases}.[CaseId]
WHERE  {Case}.[Id] <> @CaseId
AND NOT ( ( {Case}.[Id] = {RelatedCases}.[CaseIdRelated] ) AND ( {RelatedCases}.[CaseId] = @CaseId ) )
It returns one that already is Related, and another one that is not related, but there still a lot of records from Case Entity that are not returning.
Hi Francisco,
I'm not sure if I understand what you want.

but I think that you want something like this

SELECT  {Case}.*
FROM {Case}
WHERE  {Case}.[Id] <> @CaseId
AND NOT  EXISTS ( select {RelatedCases}.[CaseId] from {RelatedCases} where {RelatedCases}.[CaseId] = {Case}.[Id] AND  {RelatedCases}.[CaseId] = @CaseId)

If this is not working you can use somthing called Minus where you make something like this

SELECT  {Case}.*
FROM {Case}
WHERE  {Case}.[Id] <> @CaseId

Minus

SELECT  {Case}.*
FROM {Case} Inner JOIN {RelatedCases}
ON {Case}.[Id] = {RelatedCases}.[CaseId]
WHERE {RelatedCases}.[CaseId] = @CaseId

Hope this helps,

Regards
Carlos Rocha
Thank you for your help Carlos.

I solved the problem some minutes ago with:

SELECT  {Case}.* 
FROM {Case}
WHERE  {Case}.[Id] <> @CaseId AND 
{Case}.[Id] NOT IN 
( SELECT DISTINCT {RelatedCases}.[CaseIdRelated] FROM {RelatedCases}
  WHERE {RelatedCases}.[CaseId] = @CaseId )
I will remember the minus next time.

Thanks a lot!
Hi Francisco,

Actually you might want to use the 'EXISTS' in your clause as Carlos did instead of your 'IN' if possible.
The EXISTS usually is something that can be determined by using an index instead of actually
executing the query and handling it's result.

SELECT {Case}.* FROM {Case}
WHERE {Case}.[Id] <> @CaseId
AND NOT EXISTS
(
SELECT {RelatedCases}.[CaseIdRelated]
FROM {RelatedCases}
WHERE {RelatedCases}.[CaseId] = @CaseId
)

The distinct is not needed in this context. Depending on the records you get back you should notice that this is faster. Cheers, Eric
I was developing a demo to a client, so performance wont be the problem at this time.
But anyway, thanks a lot !
I will keep the EXIST in mind ;)
Hi guys

Let me add my 2 cents

For heavier queries you should try to avoid these types of conditions...
Where someting in/exist/not exist( select something else from table where ) if the inner query relates to outer query line by line

From the query optimizer this will trigger huge nested loops (take a look at the execution plan)

There is nothing you can do using this type of where clause that you can not accomplish using joins which are much more effective in the optimizer.

exist / in
select something
from tab1
inner join tab2 on tab1.a =  tab2.a

not exist
select something
from tab1
left  join tab2 on tab1.a = tab2.a
where isnull tab2.id

make sure you put as much conditions as possible in the ON clause

Cheers
Thanks Rui.

I'll take that in consideration. I have to make sure i review sql again, I havent worked with it for some years.
I've tried with the join condition, but I was using a NOT as you can see on my first post.

Anyway, thanks, every post is important, because others opinions are always welcome for increasing knowledge and approach to advanced queries.
Rui, thanks for your 2 cents.

Any chance of moving a "NOT EXISTS" like your example to a simple query?

not exist
select something
from tab1
left  join tab2 on tab1.a = tab2.a
where isnull tab2.id

Would by any chance an isnull test like you've described above
on a "WITH OR WITHOUT" join (tab1 => tab2) have the same result?

In that case the simple query would be even quicker since it can use the resultset limiter (MaxRecords)

Cheers,

Eric