SELECT AVG (stockPrice * quantities * exchrate) AS averageStock FROM stock JOIN nation
	WHERE stock.natcode = nation.natcode

quote:

---

Originally posted by ayamas

SELECT AVG (stockPrice * quantities * exchrate) AS averageStock FROM stock JOIN nation
WHERE stock.natcode = nation.natcode and stock.exchrate='US'

---

quote:

---

Originally posted by sunil

Try
SELECT AVG (stockPrice * quantities * exchrate) AS averageStock FROM stock JOIN nation
WHERE stock.natcode = nation.natcode and tblone.nationCode = 'US code'

---

quote:

---

Originally posted by sunil

Can you post your DDL with sample data for both the tables? It will help to find cause of the error mentioned.

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[nation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[nation]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[stock]

GO



create table nation(

natcode char(3) not null,

natname char(20),

exchrate decimal(9,5),

primary key (natcode));



insert into nation values ('UK','United Kingdom',1);

insert into nation values ('USA','United States',0.67);

insert into nation values ('AUS','Australia',0.46);

insert into nation values ('IND','India',0.0228);

GO



create table stock(

stkcode char(3) not null,

stkfirm char(20),

stkprice decimal(6,2),

stkqty decimal(8),

stkdiv decimal(5,2),

stkpe decimal(5),

natcode char(3),

primary key (stkcode),

constraint fk_hasnation foreign key (natcode) references nation(natcode)

ON DELETE NO ACTION);



insert into stock values ('FC','Freedonia Copper',27.5,10529,1.84,16,'UK');

insert into stock values ('PT','Patagonian Tea',55.25,12635,2.5,10,'UK');

insert into stock values ('AR','Abyssinian Ruby',31.82,22010,1.32,13,'UK');

insert into stock values ('SLG','Sri Lankan Gold',50.37,32868,2.68,16,'UK');

insert into stock values ('ILZ','Indian Lead & Zinc',37.75,6390,3,12,'UK');

insert into stock values ('BE','Burmese Elephant',0.07,154713,0.01,3,'UK');

insert into stock values ('BS','Bolivian Sheep',12.75,231678,1.78,11,'UK');

insert into stock values ('NG','Nigerian Geese',35,12323,1.68,10,'UK');

insert into stock values ('CS','Canadian Sugar',52.78,4716,2.5,15,'UK');

insert into stock values ('ROF','Royal Ostrich Farms',33.75,1234923,3,6,'UK');

insert into stock values ('MG','Minnesota Gold',53.87,816122,1,25,'USA');

insert into stock values ('GP','Georgia Peach',2.35,387333,0.2,5,'USA');

insert into stock values ('NE','Narembeen Emu',12.34,45619,1,8,'AUS');

insert into stock values ('QD','Queensland Diamond',6.73,89251,0.5,7,'AUS');

insert into stock values ('IR','Indooroopilly Ruby',15.92,56147,0.5,20,'AUS');

insert into stock values ('BD','Bombay Duck',25.55,167382,1,12,'IND');

GO

SELECT AVG(stkprice * stkqty * exchrate) AS averageStock FROM stock, nation
	WHERE stock.natcode = nation.natcode AND nation.natcode = 'UK'

quote:

---

Originally posted by sunil

Use ANSI join syntax:

SELECT AVG (stock.stkprice * stock.stkqty * nation.exchrate) AS averageStock FROM stock inner JOIN nation
on stock.natcode = nation.natcode and nation.natCode = 'USA'

---

quote:

---

Originally posted by visakh16

ON simply specifies what field(s) you need to match on from tables involved.Inner join will return only matched records from both tables while outer join returns all records from one of the table (first or second or both based on LEFT or RIGHT or FULL clause) and matched record values from second table and with NULL values for unmatched rows.
JOIN is same as INNER JOIN. so probably you didnt had right fields specified for join condition which caused spurious results.

---