Re : Advanced SQL Query ( And condition )

Re : 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

It is an old story and I'm a newcomer, but YES, if we have a not exist, it can be moved to a simple query.

Use tab1 as first table, join tab2 "with or without", and add filter on "tab2.id = nullIdentifier()".

You can even omit the filter on tab2.id within the query, get all rows and filter/decide later outside the query.


Problem arises if we have two tables T1 and T2 joined by J where J.t1id = T1.id and J.t2id = T2.id. In this case we need a "JOIN ON" which reflects on both T1 and T2 for checking J:

select T1.id, T2.id, min(J.id) from T1 cross join T2 left outer join J on T1.id = J.t1id and T2.id = J.t2id

For this kind of "two sided" join, which basically checks which T1 and T2 records are connected (and is usually filtered a bit more to avoid cartesian product of T1 and T2) is NOT possible with SIMPLE query - at least I could not manage it, as in the simple query join clause we currently can refer only on one table, not on two different ones.