i'm looking identify data input wine bottles purchased danmurphys produced in 2012. query syntax isn't right , i'm not sure i'm doing wrong.
your appreciated :)
create table retailer (retail_id nvarchar(4) not null, retail_name nvarchar(30) not null, primary key (retail_id)); create table wine (wine_id nvarchar(3) not null, wine_name nvarchar(30) not null, wine_vint nvarchar(4) not null, primary key(wine_id)); create table dist (retail_id nvarchar(4) not null, wine_id nvarchar(3) not null, wine_price decimal(6,2) not null, constraint pk_dist primary key ( retail_id, wine_id ), foreign key (retail_id) references retailer (retail_id), foreign key (wine_id) references wine (wine_id)); insert wine values('101','grange','2010'); insert wine values('102','grange','2006'); insert wine values('103','reserve shiraz','2013'); insert wine values('104','grey label shiraz','2012'); insert wine values('105','patricia shiraz','2009'); insert wine values('106','ten acres shiraz','2012'); insert wine values('107','double barrel shiraz','2012'); insert wine values('108','platinum label shiraz','2006'); insert retailer values('1001', 'dan murphys'); insert retailer values('1002', 'woolworths'); insert dist values('1001','101','750'); insert dist values('1001','102','700'); insert dist values('1001','103','10'); insert dist values('1001','104','35'); insert dist values('1001','105','50'); insert dist values('1001','106','25'); insert dist values('1001','107','15'); insert dist values('1001','108','170'); insert dist values('1002','103','9'); insert dist values('1002','104','33'); insert dist values('1002','105','44'); select wine.wine_name, dist.wine_price, retailer.retail_name wine inner join dist on (dist.retail_id = retailer.retail_id) inner join retailer on (dist.retail_id = retailer.retail_id) retailer.retail_id = '1001' , wine.wine_vint = '2012'
changed join condition between wine , dist:
select wine.wine_name, dist.wine_price, retailer.retail_name wine inner join dist on (dist.wine_id = wine.wine_id) inner join retailer on (dist.retail_id = retailer.retail_id) retailer.retail_id = '1001' , wine.wine_vint = '2012' turns out as:
sql>select wine.wine_name, dist.wine_price, retailer.retail_name sql&from wine sql&inner join dist on (dist.wine_id = wine.wine_id) sql&inner join retailer on (dist.retail_id = retailer.retail_id) sql&where retailer.retail_id = '1001' , wine.wine_vint = '2012'; wine_name wine_price retail_name ============================== ========== ============================== grey label shiraz 35.00 dan murphys ten acres shiraz 25.00 dan murphys double barrel shiraz 15.00 dan murphys 3 rows found 
Comments
Post a Comment