SQL:subquerys

Hejsan!
Jag har en sql-fråga vi arbetat med på en labb... Nu tänkte vi göra om den frågan med hjälp av subquerys istället för en massa and... Nu ser frågan ut så här (denna fungerar perfekt):

select SUPPLIER.NAME, SUM(SUPPLY.QUAN*PARTS.WEIGHT) 
	from SUPPLIER, SUPPLY, PARTS, CITY 
	where SUPPLIER.CITY = CITY.NAME and 
		CITY.STATE = 'Mass' and 
		SUPPLIER.KEY = SUPPLY.SUPPLIER and 	
		SUPPLY.PART = PARTS.KEY 
	group by SUPPLIER.NAME;

Men vi vill istället använda subquerys.. Då blir det problem.. vi vet inte hur man lägger flera nästlade selectsatser i varandra.... Vårt första försök ser ut så här:

select SUPPLIER.NAME, SUM(SUPPLY.QUAN * PARTS.WEIGHT)
	from SUPPLIER,SUPPLY,PARTS where SUPPLIER.KEY in
	(select SUPPLY.SUPPLIER from SUPPLY
		where SUPPLY.PART in
		(select PARTS.KEY from PARTS
		where SUPPLIER.CITY in
		(select CITY.NAME from CITY 
			where CITY.STATE = 'Mass')
		)) 
	group by SUPPLIER.NAME;

Vi får inget felmeddelande och det skrivs ut rätt kolumner men fel värde på SUM(SUPPLY.QUAN*PARTS.WEIGHT). Detta beror väl på att den inte hämtar rätt värde från SUPPLY.QUAN och PARTS.WEIGHT. Eller?

Hur ska vi göra?

Vi är tacksamma för snabba svar... :bire

Det enda som är relevant att skriva om som en subquery är villkoret mot tabellen city

select SUPPLIER.NAME, SUM(SUPPLY.QUAN*PARTS.WEIGHT) 
	from SUPPLIER, SUPPLY, PARTS
	where SUPPLIER.KEY = SUPPLY.SUPPLIER and 	
		SUPPLY.PART = PARTS.KEY 
        and SUPPLIER.CITY in (
         select NAME 
           from city 
          where STATE = 'Mass')
	group by SUPPLIER.NAME

Anledningen till att du får fel resultat med andra frågan är att det blir en kryssprodukt eftersom det inte finns några joinvillkor mellan
SUPPLIER, SUPPLY och PARTS

Tack så mycket! Det fungerade perfekt!

Varför är det bara relevant att skriva om STATE-villkoret till en subquery? Är det för att vi har en jämförelse där?

Den kod som fungerar perfekt:

select SUPPLIER.NAME, SUM(SUPPLY.QUAN*PARTS.WEIGHT) from SUPPLIER, SUPPLY, PARTS
      where SUPPLIER.KEY = SUPPLY.SUPPLIER and SUPPLY.PART = PARTS.KEY 
       and SUPPLIER.CITY in (
        select CITY.NAME from CITY 
          where CITY.STATE = 'Mass')
			group by SUPPLIER.NAME;

En annan fråga

Jag har en annan fråga jag tänkte fråga när jag ändå är på gång.. Det gick ju så bra med senaste frågan så....

Jag har skapat en ny tabell ITEM2 som ska innehålla nästan samma saker som ITEM men bara rader med ett visst villkor... Att skapa själva tabellen har jag gjort men att få in värdena automatiskt går lite sämre....

Hittils jobbar vi på något sånt här;

insert into ITEM2(KEY,NAME,DEPT,PRICE,QOH,SUPPLIER) values
	(select * from ITEM 
		where ITEM.PRICE < AVG(ITEM.PRICE)
	);

Hur ska man skriva?

Varför är det bara relevant att skriva om STATE-villkoret till en subquery? Är det för att vi har en jämförelse där?

Nej, det är för att det är den enda tabell som man inte refererar i select-listan.

Jag har skapat en ny tabell ITEM2 som ska innehålla nästan samma saker som ITEM men bara rader med ett visst villkor.

Varför en tabell? Det borde vara en vy.

create view itemsBelowAverage as
select * 
from ITEM 
where PRICE < 
(select AVG(PRICE) 
    from item)

Fördelen med en vy är att då fungerar det även om man ändrar i ITEM-tabellen.

Labbuppgiften gick ut på att skapa en ny tabell... På fråga två ska man sen göra en vy och bestämma skillnaden... så du är inne på helt rätt spår! men man kanske kan göra på ungefär samma sätt som med en vy?

En insert med subselect ser ut som

insert into ITEM2(KEY,NAME,DEPT,PRICE,QOH,SUPPLIER) 
	select * from ITEM 
		where PRICE < (select AVG(PRICE)
      from price)

Tack igen LarsG!

Det fungerade perfekt!

Vi försökte ta bort subqueryn vid AVG(PRICE) och köra direkt men då fick vi ett fel som sade något om gruppfunktioner ("Group function is not allowed here")

Vad beror detta på? Varför fungerar det när man kör en subquery?