?
Solved

Tricky SQL query

Posted on 2007-10-17
16
Medium Priority
?
263 Views
Last Modified: 2010-03-19
SQL Problem

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
where qclient.clinum in (142)
and qdocall.doctyp is null  
union all
Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
where qclient.clinum in (142)
and qdocall.doctyp not like 'Mickey Mouse'
and qdocall.doctyp not like 'Donald Duck'
and qdocall.doctyp not like 'Goofy'
and qdocall.doctyp not like 'Pluto';

The first part of the above query returns all desired records from the qmatter table
where there are no corresponding records in the qdocall table. I'm using the NULL
criteria and the UNION ALL statement because in the second part, the criteria I
use on the right table trumps the left outer join. Simple enough. But here is my
problem: In the second query, I exclude all records that have Mickey Mouse, Goofy,
Pluto, or Donald Duck in the doctyp field. I want records that have different doctyp
values. HOWEVER, if a record ONLY has one of the excluded doctype values, or even more
than one of the excluded doctyp values (say, 3 from that list), and NO OTHER
doctyp value, then (and I'm not sure if this is possible) RETURN that record only
ONCE, and put another value in the doctyp field (example, DISNEYLAND).

So, clear as mud?
0
Comment
Question by:crafuse
  • 10
  • 6
16 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20095323
To avoid the where clause trumping your left join, move your criteria to the join
left outer join qdocall
on qmatter.clinum = qdocall.clinum
and qmatter.matnum = qdocall.matnum
and qdocall.doctyp not like 'Mickey Mouse'
and qdocall.doctyp not like 'Donald Duck'
and qdocall.doctyp not like 'Goofy'
and qdocall.doctyp not like 'Pluto'
where qclient.clinum in (142)

0
 

Author Comment

by:crafuse
ID: 20095445
UnifiedIS -

Nice. I may well look at that. The things is, my application is a report builder that generates dynamic SQL, and the query above was just one example of a query that I build and send to SQL Server. Otherwise, any ideas about my problem? If I incorporated your change, would it help ease a solution to my problem?

Thanks,
crafuse
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20095639
I think you'll want to incorporate counts of doctyp under a couple different scenarios and then compare them to determine what to do next.  
Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type'
Result = CASE WHEN A.DisneyCount - B.NotDisneyCount<= 0 THEN "AllDisney"
ELSE "NotAllDisney"
END
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
LEFT OUTER JOIN
(SELECT COUNT(doctyp) AS DisneyCount, clinum, matnum FROM qdocall WHERE doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY clinum, matnum) AS A
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum
LEFT OUTER JOIN
(SELECT COUNT(doctyp) AS NotDisneyCount, clinum, matnum FROM qdocall WHERE doctyp IS NOT NULL AND doctype NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY clinum, matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:crafuse
ID: 20095936
UnifiedIS,

Thanks, this is going to take me a little time to sort out. I'll get back to you on this.

crafuse
0
 

Author Comment

by:crafuse
ID: 20096287
made few changes (only syntax) but getting errors. I've put in what I changed, and pasted errors below. any suggestions?

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type',   -- added comma
Result = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN "AllDisney" --added brackets
ELSE "NotAllDisney"
END
from (qclient inner join qmatter on qclient.clinum = qmatter.clinum)
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS DisneyCount, qdocall.clinum, qdocall.matnum    --added tablename notation
FROM qdocall WHERE qdocall.doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')  --same
GROUP BY qdocall.clinum, qdocall.matnum) AS A  ---same
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum  ---same
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS NotDisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp IS NOT NULL
AND qdocall.doctyp NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto') GROUP BY qdocall.clinum, qdocall.matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum;

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "qdocall.doctyp" could not be bound.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'AllDisney'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'NotAllDisney'.

Thanks,
crafuse
0
 

Author Comment

by:crafuse
ID: 20096778
I think I've "almost" got it working, but when I try to run it with the select including line 4 (qdocall.doctyp as 'Docket Type',) I get the following error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "qdocall.doctyp" could not be bound.

Otherwise, this is what it looks like now...

Select rtrim(qclient.clname) as 'Client Name',
qclient.clinum as 'Client Number',
qmatter.matnum as 'Matter Number',
qdocall.doctyp as 'Docket Type',
Result = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN 'AllDisney'
ELSE 'NotAllDisney'
END
from qclient inner join qmatter on qclient.clinum = qmatter.clinum
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS DisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')
GROUP BY clinum, matnum) AS A
ON A.clinum = qmatter.clinum AND A.matnum = qmatter.matnum
LEFT OUTER JOIN
(SELECT COUNT(qdocall.doctyp) AS NotDisneyCount, qdocall.clinum, qdocall.matnum
FROM qdocall
WHERE qdocall.doctyp IS NOT NULL
AND qdocall.doctyp NOT In ('Mickey Mouse', 'Donald Duck', 'Goofy', 'Pluto')
GROUP BY clinum, matnum) AS B
ON B.clinum = qmatter.clinum AND B.matnum = qmatter.matnum;

Bear in mind, please, that the results have to include all doctyps other than those excluded, and a single record for those matters that only have those doctyps explicitly exluded, with anyword\phrase in the place of the qdocall.doctyp value(s).

Thanks,
crafuse

0
 

Author Comment

by:crafuse
ID: 20102115
UnifiedIS,

Hate to bother you about this, just wondering if you're still interested in this problem?

Thanks,
crafuse
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20102727
qdocall is no longer a table alias
get rid of this: qdocall.doctyp as 'Docket Type',
and change result to 'Docket Type' in the line below:
'Docket Type' = CASE WHEN (A.DisneyCount - B.NotDisneyCount <= 0) THEN 'AllDisney'
0
 

Author Comment

by:crafuse
ID: 20102931
Thanks for that, I guess I had it going yesterday when I simply went with the 'Result' name.

My question, though, is this: My problem included returning qdocall.doctyp values where they are not explicitly excluded in the 'not like' list, as well as a descriptive value where there are only records in qdocall matching the excluded values (in this case, I'm assuming 'NotAllDisney' is that result?).

Thanks,
crafuse
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 2000 total points
ID: 20103158
Okay, I think we need to adjust the case statements a bit to get all the scenarios covered:
Result = CASE WHEN (A.DisneyCount = 0 AND B.DisneyCount = 0) OR (A.DisneyCount IS NULL AND B.NotDisneyCount IS NULL)  THEN 'NoDocTyp' --no doctype exisits
      WHEN (B.NotDisneyCount > 0) THEN 'NotAllDisney'  --some non disney doctyp exists
      WHEN (A.DisneyCount > 0 AND (B.NotDisneyCount = 0 OR B.NotDisneyCount IS NULL)) THEN 'AllDisney' --all doctyp are disney
END

I think all the possibilities are covered now
0
 

Author Comment

by:crafuse
ID: 20103908
Sweet. Getting there. Still need to display the real doctyp values\records when 'NotAllDisney'. And maybe more. I need to find good examples to test various scenarios. Thanks so much for the help so far! Let me know if you'd like me to open a new question for the second part of the requirement, that the query displays all records for those that are not explicitly excluded but exist. I'd be ok with that cause I do think this is a pretty tricky question.

Thanks,
crafuse
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20104615
Either way is fine with me, I'm glad we're making some progress
0
 

Author Comment

by:crafuse
ID: 20109473
UnifiedIS -

Your logic\code is doing precisely as requested. I'm hoping now to be able to bring back all records\docket types not explicitly excluded. Do you think you can also help me with that?

Thanks,
crafuse
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 20110524
I'm willing to help but it may be a few days before I get a chance
0
 

Author Comment

by:crafuse
ID: 20112166
Going to close this question and open another to deal with the processing of the records. Am thinking the following:

1. If there are one or more docket types per clinum\matnum that are to be excluded, return only 1 record for with a docket type = 'No Due Date'

Example, if there are 4 doctyps = 'Mickey Mouse'
return only one record and say doctyp = 'No Due Date'

2. If there are also docket types that are not in the excluded list, return each of them with their actual docktyp value.

Am thinking it might be easier to process records in a temp table stored locally, anyways will give this some thought over the weekend.

Will close this and include link to new question Monday morning.

Thanks,
crafuse

0
 

Author Comment

by:crafuse
ID: 20125543
UnifiesIS -

I opened up a new question. Thanks for your help on this one! Let me know if you think the new question is a little clearer.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22909785.html

thanks,
crafuse
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question