Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 26th, 2003, 02:24 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Complex Query-Plz Help

hi,

  i have a complex query. let me explain it.

i have an SODts table. i have 2 columns JobCode & Quality in it.
for 1 jobcode i can have multiple qualities.
i have 1 jobcode as input.
i can find out the Qualities associated with that JoCode.
Suppose i got 4 Qualities for the current job.
now I NEED TO FIND OUT THE OTHER JOBCODES IN THE SAME TABLE WHICH HAS ALL THESE 4 QUALITIES + THAT JOBCODE SHOULD HAVE ONLY THESE 4 QUALITIES.

i decided to write a stored procedure but still since i cannot use the IN statement in this condition, how can i check for the combination of Qualities. is there any other string functions which can check 4 the combination of datas from 4 diff. rows.

actually i need this querry.
Select JobCode From SODts Where JobCode IN (Select THE JOBCODE WHICH HAS ALL THE QUALITIES OF THE JOBCODE SPECIFIED.)

if i am using cursor, i will have to use 2 loops. plz help me. i am at clients palce now & struggling.
 
Old June 26th, 2003, 02:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not clear on this. As I understand it there should never be more than 4 unique qualities for a single jobcode and you want to return a list of all the jobcodes that have more than 4 qualities? Or are you looking for the duplicate entries of Jobcodes and qualities? Or are you looking for jobcodes that have exactly four qualities?

BTW it sounds as though you data would be better described as:
jobcode
Quality1
Quality2
Quality3
Quality4

regards
David Cameron
 
Old June 26th, 2003, 02:42 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi david,

thnx for the fast response.

i want to find out a similar jobcode of the a given jobcode.
for that i am matching the qualities of the given jobcode with the qualities of all other jobcodes in the table.
suppose that i got 5 qualities for the given jobcode.
there will be jobcodes with 'n' no. of qualities.
i need a jobcode from the same table which has exactly the above 5 qualities. not less than that and not more than that.
the problem is i cannot use IN statement here since i have to search for the combination.
plz try.
thanx
 
Old June 26th, 2003, 04:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about doing this in multiple steps?

(Note, code is untested and not guaranteed, and is only meant to illustrate the idea, etc :))

Code:
Create Proc blah

@Code xxxx

as

Begin

Declare @QualCount int

Select Code, Quality into #qual
From Table
where code = @code


Select Code, Quality into #List
From Table T join #Qual Q on T.Quality = Q.Quality

Set @QualCount = (Select Count(Quality) From #Qual)

Select T.Code, Count(T.Quality) 
From #List L join Table T on L.Code = T.Code
Group By T.Code
having Count(T.Quality) = @QualCount


END



Or something like that. I'm only answering because the clever people are asleep :)

HTH

Chris

There are two secrets to success in this world:
1. Never tell everything you know
 
Old June 26th, 2003, 07:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

If I understand your requirement correctly, I believe what you are looking for is called relational division. In this operation, one table (a divisor) is used to partition another (a dividend), dividing the second into those values exactly and only present in the first (giving a quotient). Of course, there doesn't have to be two different tables - the dividend and divisor can be the same table if the divisor is a specific value. This query assumes the parameter @Jobcode:
Code:
SELECT Jobcode FROM SODts S1
    WHERE NOT EXISTS
        (SELECT Quality FROM SODts
            WHERE JobCode=@JobCode
            AND NOT EXISTS
                (SELECT Quality FROM SODts S2
                    WHERE S1.JobCode=S2.JobCode
                    AND S1.Quality=S2.Quality
GROUP BY JobCode
HAVING COUNT(*)=(SELECT COUNT(*) FROM SODts WHERE JobCode=@Jobcode);
(I think ;))


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old June 26th, 2003, 06:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When you say you are searching for similar jobcodes, are we looking for jobcodes that have the same 5 (or n) qualities or are we looking for jobcodes that have the the same 5 (or n) qualities *and* the same jobcode.

Suppose the table looks like this:
Code:
code1     val1
code1     val2
code1     val1
code1     val2
code2     val1
code2     val2
code3     val3
If I understand the problem correctly, and if you have passed in the jobcode code1, I think you want one of the following resultsets:
Code:
code1     val1
code1     val2
code1     val1
code1     val2
code2     val1
code2     val2
or

Code:
code1     val1
code1     val2
code1     val1
code1     val2
or

Code:
code2     val1
code2     val2
Am I understanding the problem correctly? If so, which one do you want returned?

regards
David Cameron
 
Old June 26th, 2003, 06:41 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
Code:
SELECT Jobcode FROM SODts S1
    WHERE NOT EXISTS
        (SELECT Quality FROM SODts
            WHERE JobCode=@JobCode
            AND NOT EXISTS
                (SELECT Quality FROM SODts S2
                    WHERE S1.JobCode=S2.JobCode
                    AND S1.Quality=S2.Quality
GROUP BY JobCode
HAVING COUNT(*)=(SELECT COUNT(*) FROM SODts WHERE JobCode=@Jobcode);
Very impressive. It took me a good five minutes to get my head around that one. I think you are missing some closing brackets around the subqueries though.

regards
David Cameron
 
Old June 26th, 2003, 11:37 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Oops.

The closing parenthesis belong at the end of the second subquery. My bad. Sorry; the query should read:
Code:
SELECT Jobcode FROM SODts S1
    WHERE NOT EXISTS
        (SELECT Quality FROM SODts
            WHERE JobCode=@JobCode
            AND NOT EXISTS
                (SELECT Quality FROM SODts S2
                    WHERE S1.JobCode=S2.JobCode
                    AND S1.Quality=S2.Quality))
GROUP BY JobCode
HAVING COUNT(*)=(SELECT COUNT(*) FROM SODts WHERE JobCode=@Jobcode);


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old June 27th, 2003, 06:25 AM
Registered User
 
Join Date: Jun 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi David,
   i need only Code2 in ur case. i want to return the jobcode which has the exact Qalities as of the given jobcode. the jobcode need not be same, only qualities need to be similar.

Hi jeff,
        to be very frank, i didnt completely understand ur query. i have used IF EXISTS statement in triggers but havent used NOT EXISTS in queries. i just tried with ur query with one job code. but it is not giving me correct results.

Hi Chris,
    by select into statement, do u mean to create a table each time i am querying this. this is a frequently used query i cannot use that method i guess.

Thanx for all ur responses..... but .....
 
Old June 27th, 2003, 09:15 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:...i didnt completely understand ur query. ... i just tried with ur query with one job code. but it is not giving me correct results.
I did find a problem with the query as I originally posted it when I tested it a bit further. The second subquery wasn't quite right. Also, the query would also return the jobcode provided as a parameter in addition to the other matching jobcodes. I added a not equal test at the end to remove that. This is the modified query:
Code:
SELECT JobCode FROM SODts S1
    WHERE NOT EXISTS
        (SELECT Quality FROM SODts
            WHERE JobCode=@JobCode
            AND NOT EXISTS
                (SELECT Quality FROM SODts S2
                    WHERE S1.JobCode=S2.JobCode
                    AND S2.Quality=SODts.Quality))
GROUP BY JobCode
HAVING COUNT(*)=(SELECT COUNT(*) FROM SODts WHERE JobCode=@Jobcode)
AND JobCode<>@JobCode;
Using the following data:
Code:
AA  Q1
AA  Q2
BB  Q1
BB  Q2
BB  Q3
CC  Q1
CC  Q2
DD  Q2
EE  Q1
EE  Q2
the query correctly (as I understand your requirement) returns the results CC and EE when the jobcode parameter is 'AA'.

As to understanding what's going on here, when formulating a query like this it is sometimes helpful to rephrase the original requirement into a series of negative statements.

Thus, your requirement could be equivalently stated as "Find the jobcodes for which there does not exist qualities that the source (parameter) jobcode does not have. (You may have to read that sentence several times ;))

Restating the query that way shows why the NOT EXISTS predicate is used.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
complex query... help plz hello MySQL 1 April 28th, 2007 05:48 AM
complex query g_vamsi_krish SQL Language 3 February 27th, 2006 10:48 AM
query too complex liz@trinityholdings.co.za Access 3 August 28th, 2003 09:01 AM
COMPLEX QUERY PROBLEM nikosdra SQL Server ASP 15 August 4th, 2003 06:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.