RE: SQL Question

[Alan Shore <AlanShore@xxxxxxxx>]

Hi Mike
completely untested

with select1 as
(select jobnumber from datafile
where partnumber = in1partnumber),
     select2 as
(select jobnumber from datafile
where partnumber = in2partnumber),
     select3 as
(select jobnumber from datafile
where partnumber = in3partnumber)

select a.jobnumber   from select1 a
where exists
(select * from select2 b
where a.jobnumber = b.jonumber) and exists
(select * from select3 c
where a.jobnumber = c.jonumber)




Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



                                                                       
             "Michael Smith"                                           
             <msmith@jhfletche                                         
             r.com>                                                     To
             Sent by:                  "Midrange Systems Technical     
             midrange-l-bounce         Discussion"                     
             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>       
                                                                        cc
                                                                       
             06/28/2010 02:47                                      Subject
             PM                        RE: SQL Question                
                                                                       
                                                                       
             Please respond to                                         
             Midrange Systems                                          
                 Technical                                             
                Discussion                                             
             <midrange-l@midra                                         
                 nge.com>                                              
                                                                       
                                                                       




Sorry,

I was trying to provide as much, but not too much information.

Your statement is correct.  Given the following data, I want the results
of my select statement to return all job numbers that contain all 3
parts.  The results would be C1, D1, if the user enters 123, 456, 789 as
selection part numbers.

JobNumber   PartNumber
      A1    123
      A1    456
      A1    111
      B1    123
      B1    789
      C1    123
      C1    456
      C1    789
		 D1		 123
		 D1		 456
		 D1		 789

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Monday, June 28, 2010 2:07 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Question


Hi Michael
hate to be a 'nudge, but you haven't really explained you selection
criteria
I suspect its the job number that contains all 3 part numbers
so if the data file included the following (AS WELL as your data)
D1		 123
D1		 456
D1		 789

you would also want to see D! as well
however, if the file also contained
D1		 ABC
do you still want to see D1 as well



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill




             "Michael Smith"

             <msmith@jhfletche

             r.com>
To
             Sent by:                  "Midrange Systems Technical

             midrange-l-bounce         Discussion"

             s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>


cc


             06/28/2010 02:02
Subject
             PM                        SQL Question





             Please respond to

             Midrange Systems

                 Technical

                Discussion

             <midrange-l@midra

                 nge.com>









I have a data file:

JobNumber   PartNumber

      A1    123

      A1    456

      A1    111

      B1    123

      B1    789

      C1    123

      C1    456

      C1    789



The user will enter 3 part numbers; 123, 456, 789... and I need the
results of my SQL select to return C1.  Is this possible? If so, can
someone provide a "code snippet"



TIA



Michael Smith



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.