SQL Gurus

Discussion in 'OT Technology' started by teamelement3, Oct 21, 2005.

  1. teamelement3

    teamelement3 BFB

    Joined:
    Feb 27, 2005
    Messages:
    18,079
    Likes Received:
    0
    can anyone IM me for some quick SQL help AIM is same as on here :wavey:
     
  2. kingtoad

    kingtoad OT Supporter

    Joined:
    Sep 2, 2003
    Messages:
    55,923
    Likes Received:
    11
    Location:
    Los Angeles
    You would be better off just asking your question because I'm not going to AIM you.
     
  3. teamelement3

    teamelement3 BFB

    Joined:
    Feb 27, 2005
    Messages:
    18,079
    Likes Received:
    0
    ok fine, I have two tables, and data has been populated for them. I want to take a column A and a column from B and I want only the ones that match up to be displayed, right now I just get the crossproduct
     
  4. teamelement3

    teamelement3 BFB

    Joined:
    Feb 27, 2005
    Messages:
    18,079
    Likes Received:
    0
    In example, I have a table of events and events have restrictions( a seperate table) now I want to do a join on them, but I only want the events with the actual restrictions that have been populated for them.
     
  5. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    SELECT a.column1, b.column2
    FROM table_a a, table_b b
    WHERE a.column1 = b.column2

    Assuming I understand your question correctly.
     
  6. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Alternately, and probably more efficiently:

    SELECT a.column1, b.column2
    FROM table_a a JOIN table_b b ON a.column1 = b.column2
     
  7. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    is it good or bad that i knew that... and by no means call myself a SQL guru?
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Lesson 1: Choose your data set and how to include it with the SELECT command

    Lesson 2: Relate your tables by JOIN command.
     
  9. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    I don't think of myself as a guru either, but that I write shit like this for assignments, I guess I'm alright:
    Code:
    CREATE VIEW medical_risk(medical_type, abnormal_rate, alarming_age) AS (                                                                                                                                                                                                
    SELECT DISTINCT t.test_name, a2.ratio AS abnormal_rate, MIN(a1.age) AS alarming_age                                                                                                                                                                                     
    FROM test_type t, (                                                                                                                                                                                                                                                     
            SELECT total.type_id, total.age, COUNT(DISTINCT abnormal.test_id) / COUNT(DISTINCT total.test_id) AS ratio                                                                                                                                                      
            FROM (                                                                                                                                                                                                                                                          
                    SELECT DISTINCT t.type_id, r.test_id,  TO_CHAR(TRUNC(SYSDATE, 'year'), 'YYYY') - TO_CHAR(TRUNC(p.birth_day, 'year'), 'YYYY') AS age                                                                                                                     
                    FROM dual, test_record r, test_type t, patient p                                                                                                                                                                                                        
                    WHERE r.type_id = t.type_id AND                                                                                                                                                                                                                         
                            r.result <> 'normal' AND                                                                                                                                                                                                                        
                            p.health_care_no = r.patient_no                                                                                                                                                                                                                 
            ) abnormal, (                                                                                                                                                                                                                                                   
                    SELECT DISTINCT t.type_id, r.test_id, TO_CHAR(TRUNC(SYSDATE, 'year'), 'YYYY') - TO_CHAR(TRUNC(p.birth_day, 'year'), 'YYYY') AS age                                                                                                                      
                    FROM dual, test_record r, test_type t, patient p                                                                                                                                                                                                        
                    WHERE r.type_id = t.type_id AND                                                                                                                                                                                                                         
                            r.patient_no = p.health_care_no                                                                                                                                                                                                                 
            ) total                                                                                                                                                                                                                                                         
            WHERE abnormal.type_id = total.type_id AND                                                                                                                                                                                                                      
                    abnormal.age >= total.age                                                                                                                                                                                                                               
            GROUP BY total.type_id, total.age                                                                                                                                                                                                                               
    ) a1, (                                                                                                                                                                                                                                                                 
            SELECT total.type_id, COUNT(DISTINCT abnormal.test_id) / COUNT(DISTINCT total.test_id) AS ratio                                                                                                                                                                 
            FROM (                                                                                                                                                                                                                                                          
                    SELECT DISTINCT t.type_id, r.test_id                                                                                                                                                                                                                    
                    FROM test_record r, test_type t, patient p                                                                                                                                                                                                              
                    WHERE r.type_id = t.type_id AND                                                                                                                                                                                                                         
                            r.result <> 'normal' AND                                                                                                                                                                                                                        
                            r.patient_no = p.health_care_no                                                                                                                                                                                                                 
            ) abnormal, (                                                                                                                                                                                                                                                   
                    SELECT DISTINCT t.type_id, r.test_id                                                                                                                                                                                                                    
                    FROM test_record r, test_type t, patient p                                                                                                                                                                                                              
                    WHERE r.type_id = t.type_id AND                                                                                                                                                                                                                         
                            r.patient_no = p.health_care_no                                                                                                                                                                                                                 
            ) total                                                                                                                                                                                                                                                         
            WHERE abnormal.type_id = total.type_id                                                                                                                                                                                                                          
            GROUP BY total.type_id                                                                                                                                                                                                                                          
    ) a2                                                                                                                                                                                                                                                                    
    WHERE a1.type_id = a2.type_id AND                                                                                                                                                                                                                                       
            a1.type_id = t.type_id AND                                                                                                                                                                                                                                      
            a1.ratio >= 2 * a2.ratio                                                                                                                                                                                                                                        
    GROUP BY t.test_name, a2.ratio                                                                                                                                                                                                                                          
    );
    SELECT ... FROM ... JOIN ... is about as basic as it gets.
     

Share This Page