Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Question Unanswered: Probably a simple SQL question...

    Hi All,

    After searching for a while I really couldn't find what I need or I may of just been using the wrong keywords. At any rate, here's my problem:

    I need data from 1 table, however this table is structured in a way where I need to search for one set of criterion and then based on that result I need to search for another set of criterion that relies on the first search and some new conditions.

    Essentially here are the two separate statements which as a whole would represent the data I need. However I'd like to filter it down so that based upon the first SELECT statement I can then call the SECOND select statement and make comparisons to the first:

    SELECT * FROM tableX WHERE MFG_AREA = 'OS' AND LINE_NO = '3' AND (STATUS_01 LIKE '3%' OR STATUS_02 LIKE '3%' OR ... STATUS_28 LIKE '3%')

    SELECT * FROM tableX WHERE MFG_AREA = 'OS' AND LINE_NO = '4'

    Now I've tried a lot of different things and I just can't get the correct data returned. I know I can make a select call like this:

    SELECT * FROM tableX WHERE MFG_AREA = 'OS' AND (LINE_NO = '3' OR LINE = '4')

    And then based upon that I can write a program that filters the data how I'd like it, however I have a feeling I can just do it using SQL.

    Hopefully I've explain this thoroughly, if you need any clarification just let me know. Thank you so much in advance!

    Regards,
    Warren

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    There is nothing stopping you from doing like the following....

    SELECT XX.A1, YY.B2, ZZ.C3
    FROM SAMPLE_TBL XX, SAMPLE_TBL YY, SAMPLE_TBL ZZ
    WHERE XX.D4 = YY.E5
    AND YY.F6 = ZZ.G7;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Not sure if this what you're looking for, but:

    SELECT * FROM tableX WHERE MFG_AREA = 'OS'
    AND LINE_NO in ('3' ,'4')
    and line_no in ( substr(status_01,1,1), substr(status_02,1,1)... substr(status_28,1,1) )

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Thanks for the responses! I don't have time to check them as I am heading home from work. However I did want to express my appreciation before I did and let you know that I'll try them out your possible solutions tomorrow.

    Although, I have tried deviations of both... in my statements I'm missing something minute that either of you can spot. I'll post it up tomorrow...

    Thanks Again!
    Warren

  5. #5
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Thumbs down

    Here's one of my initial queries that I tried:

    SELECT COUNT(*) FROM tableX lineFour, tableX
    WHERE (lineFour.Key1 = lineThree.key1 AND lineFour.Key2 = lineThree.Key2 ) AND
    ((lineThree.MFG_AREA = 'AA' AND lineThree.LINE_NO = '3' AND (lineThree.OP_STATUS_01 LIKE '3%') AND (lineFour.MFG_AREA = '4' AND lineFour.LINE_NO = '4'))

    But that was a disaster...

    I don't think I get why you use three tables above and not just two? Is it because two of them will contain the data I want and then you compare them against the third? If that's the case I'm no to sure how to set up the Joining.

    Thanks!
    Warren

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Whoops... sorry after the second tableX there should be a qualifier "lineThree".

    Thanks
    Warren

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT COUNT(*) FROM tableX lineFour
    WHERE lineFour.MFG_AREA = '4'
    AND lineFour.LINE_NO = '4'
    AND EXISTS (SELECT '1'
    FROM TABLEX LINETHREE
    WHERE lineFour.Key1 = lineThree.key1
    AND lineFour.Key2 = lineThree.Key2
    AND lineThree.MFG_AREA = 'AA'
    AND lineThree.LINE_NO = '3'
    AND lineThree.OP_STATUS_01 LIKE '3%'
    )
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Unfortunately that's not working either.... maybe showing how the table is laid out will help:

    key1 key2 status1 status2 ... status28
    11 22 oper1 oper2 ... oper28
    11 22 12 1.4 ... 1.2

    As you can see status1 has multiple values, it either represents an operation value, or the time for the corresponding operation.

    So what I need from a query is to obtain the two correlating rows that represent the operation I want, in this case 'oper1' and the time it's there, 12. So out of all the records I need the query to return somethin like the example above...

    I might not be getting your example either, please let me know if I'm confused.

    Thanks!
    Warren

  9. #9
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Oh and by the way, no I didn't design the DB structure ... I have to work with what I got! haha

  10. #10
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Cool

    Well here's it is (partially):


    Select *
    FROM tableX table4,
    (SELECT * FROM tableX WHERE MFG_AREA = 'OS' AND LINE_NO = '3' AND OP_STATUS_01 LIKE '3%') table3
    WHERE table4.LINE_NO = '4' AND table4.key1 = table3.key1 AND
    table4.key2 = table3.key2

    This was the best I could do, however instead of having the data split into two rows it is split into one long row. Is there anyway to break these up? ORDER BY or GROUP BY won't work because Oracle sees this as one row right? i.e. it is returned like this:
    (table4.key1, table4.key2, table4.mfg_area, table4.line_no, table4.op_status_01, table3.key1, table3.key2, table3.mfg_area,table3.line_no, table3.op_status_01)

    But it'd rather have it like this:
    (table4.key1, table4.key2, table4.mfg_area, table4.line_no, table4.op_status_01)
    (table3.key1, table3.key2, table3.mfg_area,table3.line_no, table3.op_status_01)

    Where (...) represents a row returned by the query.

    Any help is greatly appreciated!!

    Thanks.
    Warren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •