Two Aliase one table - explain
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Two Aliase one table - explain

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    Two Aliase one table - explain

    Hi all

    Please explain(internal process of) the below code.

    Code:
     SELECT
         b.measure_unit_type_name defaultType,
         c.measure_unit_type_name actualType,
         (a.default_serving_amt * b.measure_unit_amt) defaultFactor,
         (12 * c.measure_unit_amt) actualFactor
         FROM
         FOOD a,
         MEASURE_UNIT b,
         MEASURE_UNIT c
                    WHERE a.default_measure_unit_nr = b.measure_unit_nr
                    AND a.food_nr = 113
                    AND c.measure_unit_nr = 12
    here in measure_unit table two aliase name is given,if i remove one that is 'c' and if i query with 'b' alone it returns null value. one of my friend said it is cross joining but coundn't get the clear picture,if any document is available please give me the link.(google doesn't helps a lot)

    Thanks in advance.,

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Not sure what kind of document are you looking for... logic is pretty clear, your query needs to select two different rows of MEASURE_UNIT -one matching measure_unit_nr = 12 and the other one matching measure_unit_nr with a value coming from FOOD table. That's why query writer listed table twice.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    What i want is,how does the alias name for b and c changes , that is instead of c.measure_unit_nr=12 if i give b.measure_unit_nr=12 i get no rows returned. so i want the value how it is assigned internally.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Think of the two instances of the same table as two different tables, that's how Oracle thinks about it.
    Rows from each one of the two instances of the table are retrieved according to your predicate.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    Thanks for ur replay.,
    again eventhough it considers as two different tables the values are same right..,so it doesn't show any rows if i use b.table_name.. so there comes confusion.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Okay... lets try something different.
    Forget about tables and think about datasets.
    In your query you have as many datasets as you have listed in your FROM clause, each one of them filtered according to your WHERE clause.
    You can't expect data from dataset "A" to show up on dataset "B" no matter if the source of these datasets are two different tables or just one and the same.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    You can't expect data from dataset "A" to show up on dataset "B" no matter if the source of these datasets are two different tables or just one and the same.
    I agree with you.,if i give in general comparing with food table the result will be different but if am particular about a single value that is "12" then it should show the same value(i think my guess is right).

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Why don't you post your two queries?
    Please replace all your selected columns by count(*) and post both sessions including result sets.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    here is the code and result

    Code:
    SQL>SELECT
         b.measure_unit_type_name defaultType,
         c.measure_unit_type_name actualType,
         (a.default_serving_amt * b.measure_unit_amt) defaultFactor,
         (12 * c.measure_unit_amt) actualFactor
         FROM
         FOOD a,
         MEASURE_UNIT b,
         MEASURE_UNIT c
                    WHERE a.default_measure_unit_nr = b.measure_unit_nr
                    AND a.food_nr = 113
                    AND c.measure_unit_nr = 12;
    SQL>/
    
    DEFAULTTYPE  ACTUALTYPE  DEFAULTFACTOR ACTUALFACTOR
    ------------ ----------- ------------- ------------
    A            Volume                 24        12000
    
    
    SQL> SELECT
      2       b.measure_unit_type_name defaultType,
      3       c.measure_unit_type_name actualType,
      4       (a.default_serving_amt * b.measure_unit_amt) defaultFactor,
      5       (12 * c.measure_unit_amt) actualFactor
      6       FROM
      7       FOOD a,
      8       MEASURE_UNIT b,
      9       MEASURE_UNIT c
     10              WHERE a.default_measure_unit_nr = b.measure_unit_nr
     11              AND a.food_nr = 113
     12              AND b.measure_unit_nr = 12;    --Only here i changes
    
    no rows selected
    
    SQL> 
    
    SQL> SELECT
      2       count(b.measure_unit_type_name) defaultType,
      3       count(c.measure_unit_type_name) actualType,
      4       count(a.default_serving_amt * b.measure_unit_amt) defaultFactor,
      5       count(12 * c.measure_unit_amt) actualFactor
      6       FROM
      7       FOOD a,
      8       MEASURE_UNIT b,
      9       MEASURE_UNIT c
     10                  WHERE a.default_measure_unit_nr = b.measure_unit_nr
     11                  AND a.food_nr = 113
     12                  AND b.measure_unit_nr = 12 ;
    
    DEFAULTTYPE  ACTUALTYPE  DEFAULTFACTOR ACTUALFACTOR
    ------------ ----------- ------------- ------------
             0                 0             0           0
    
    
    SQL> SELECT
      2       count(b.measure_unit_type_name) defaultType,
      3       count(c.measure_unit_type_name) actualType,
      4       count(a.default_serving_amt * b.measure_unit_amt) defaultFactor,
      5       count(12 * c.measure_unit_amt) actualFactor
      6       FROM
      7       FOOD a,
      8       MEASURE_UNIT b,
      9       MEASURE_UNIT c
     10                  WHERE a.default_measure_unit_nr = b.measure_unit_nr
     11                  AND a.food_nr = 113
     12                  AND c.measure_unit_nr = 12 ;
    
    DEFAULTTYPE  ACTUALTYPE  DEFAULTFACTOR ACTUALFACTOR
    ------------ ----------- ------------- ------------
             1                 1             1           1


    anything needed further
    Last edited by ams-jamali; 04-29-2008 at 06:32 AM.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    That's exactly what I was talking about...
    Your query is asking for two different rows from MEASURE_UNIT table, you cannot have one row where "a.default_measure_unit_nr = b.measure_unit_nr" and a different one where "b.measure_unit_nr = 12" coming at the same time from the same dataset.
    That's why you need two different datasets e.g. two MEASURE_UNIT entries.
    Can't you see it?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width