-
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.,
-
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.
-
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.
-
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.
-
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.
-
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.
-
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).
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|