Link to home
Start Free TrialLog in
Avatar of crafuse
crafuseFlag for Canada

asked on

Tricky SQL query

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?
Avatar of UnifiedIS
UnifiedIS

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)

Avatar of crafuse

ASKER

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
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
Avatar of crafuse

ASKER

UnifiedIS,

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

crafuse
Avatar of crafuse

ASKER

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
Avatar of crafuse

ASKER

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

Avatar of crafuse

ASKER

UnifiedIS,

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

Thanks,
crafuse
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'
Avatar of crafuse

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of UnifiedIS
UnifiedIS

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crafuse

ASKER

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
Either way is fine with me, I'm glad we're making some progress
Avatar of crafuse

ASKER

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
I'm willing to help but it may be a few days before I get a chance
Avatar of crafuse

ASKER

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

Avatar of crafuse

ASKER

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.

https://www.experts-exchange.com/questions/22909785/Process-results-of-SQL-query.html

thanks,
crafuse