Jag har tre tabeller, en som heter Fruits och en som heter Substance och en som heter FruitsSubstance.

Fruits
id name
--------
1 Apple
2 Banana
3 Peach

Substance
id name
----------
1 carbohydrate
2 protein
3 vitamin A
4 vitamin B
5 vitamin B6

FruitsSubstance
id FruitId SubstanceId QuantityMg
-----------
1 1 1 0.7
2 2 1 1.4
3 1 3 3.1
4 1 5 1.2
5 2 5 0.9

Nu vill jag jämföra två frukter ex Äpple med Banan och då skall endast de värden visas som existerar hos dessa båda frukter.
Alltså i detta fall: Både äpple och banan har carbohydrate(kolhydrater) och vitamin B6 då skall dessa värden visas
Äpple har även vitamin A men detta skall då inte tas med i presentationen.

Ex.
Substance Fruit1 Fruit2 Quanity
--------------------------------------------------
Carbohydrate Apple Banana 0.7mg 1.4mg
Vitamin B6 Apple Banana 1.2mg 0.9mg

Tacksam om ngn kunde hjälpa mig lite på traven.

För det första har jag aldrig förstått mig på en lös ID-kolumn i "kopplingstabeller". Vad har man den till?

Jag skulle börja med att hämta ut alla Substance-IDn som båda har. Vad som är snyggast råder det delade meningar om, men jag gillar mängdoperationer:

(SELECT SubstanceId FROM FruitsSubstance WHERE FruitID = 1)
INTERSECT
(SELECT SubstanceId FROM FruitsSubstance WHERE FruitID = 2)

Alternativet är att räkna dem:

SELECT SubstanceId FROM FruitsSubstance WHERE FruitID IN (1, 2) GROUP BY SubstanceID HAVING COUNT(SubstanceId) = 2

När du då har en lista på alla SubstanceId som är aktuella kan du använda det i en join. Då skulle du kunna göra själva urvalet där istället, men jag tycker det här känns renare. Det är också möjligt att det löser sig av sig själv med två inner join:s satta på ett smart sätt.

Oki, nä id't där är kanske överflödigt, det har du rätt i. Duger inte ens att indexera på. :)

INTERSECT vet jag inte äns om mySql har, men tycker att den andra varianten verkar vara smart.

Tack.

Om jag skulle vilja hitta de frukter som ligger närmast en förutbestämd frukt i näringsvärde hur kan jag göra då?

1. Tänker mig att om jag vill kolla "banan".

2. Hittar de frukter som har en gemensam nämnare enligt din modell:
SELECT SubstanceId FROM FruitsSubstance WHERE FruitID IN (1, 2) GROUP BY SubstanceID HAVING COUNT(SubstanceId) = 2

3. Drar ut ett snitt på alla andra frukter (som har denna gemensamma nänmare) och rangordar de som ligger närmast banan i näringsvärde.

Då måste du definiera "närmast", och det är inte lätt. Det lättaste är att bara kolla på ett näringsvärde, säg X.
Då blir det ju något i stil med att först hämta ut X för banan;

SELECT X FROM FruitsSubstance WHERE FruitID = 1

Kalla detta X_0 och jämföra sen detta med de du har hämtat ut;
SELECT FruitID, Abs(X-X0) AS Closeness FROM ... ORDER BY Closeness


Det blir ju dock bara närmast på det värdet. För en total skulle du kunna köra med

d = (protein_1 - protein_2)^2 + (kolhydrat_1 - kolhydrat_2)^2 ...

dvs en klassisk avståndsformel (L_2).
Problemet med det är att varje näringsvärde bör vara av samma storleksordning, osv, osv. Jävligt jobbigt.

Hej, ok verkar vara knivigt.
Så här gör jag för att få ut resultatet i min första fråga
(Då har jag även blandat in en till tabell "category")
Och det är baserat på detta jag skulle vilja jämföra alla frukter...

select c.cname,
sum(case when u.uname = 'Banan' then 1 else 0 end) as Cnt
, concat(sum(case when u.[name] = 'Äpple' then v.QuantityMg else 0 end)
/ sum(case when u.[name] = 'Banan' then 1 else 0 end),
'/',
sum(case when u.[name] = 'Äpple' then v.QuantityMg else 0 end)
/ sum(case when u.[name] = 'Äpple' then 1 else 0 end))
from FruitsSubstance v
inner
join Substance o
on o.id = v.SubstanceId
inner
join category c
on c.cid = o.catId
inner
join Fruits u
on v.FruitId = u.id
where u.[name]
in ('Banan', 'Äpple')
and o.id in(
SELECT f.SubstanceId FROM FruitsSubstance f WHERE f.[name] IN ('Banan', 'Äpple')
GROUP BY f.SubstanceID HAVING COUNT(f.SubstanceId) = 2
)
group
by c.[name]
order
by c.[name]

För det första är jag inte med på det du skriver i början där, med SUM.
Det ser ut som att du sorterar ut fallen "Banan" från alla andra. Då är det bättre att ta bort Banan från början (men ända ha kvar "Banan" i själva urvalet av substanser).

Alltså byta ut

where u.[name]
in ('Banan', 'Äpple')
and o.id in(
SELECT f.SubstanceId FROM FruitsSubstance f WHERE f.[name] IN ('Banan', 'Äpple')
GROUP BY f.SubstanceID HAVING COUNT(f.SubstanceId) = 2
)

till

where u.[name]
in ('Äpple')
and o.id in(
SELECT f.SubstanceId FROM FruitsSubstance f WHERE f.[name] IN ('Banan', 'Äpple')
GROUP BY f.SubstanceID HAVING COUNT(f.SubstanceId) = 2
)

För det andra, vad är kategori? Är "delsubstanserna" inte längre intressanta?
För det tredje, vad är frågan?
Du vill lägga till den här "rankingen" där?

Mitt tips är då något i stil med att först hämta ut kvantiteten
SELECT substanceID, fruitID, qty FROM ...
Sen tar du bort bananen, och kombinerar det med bara bananen:

SELECT * FROM
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit <> "Banan") AS S1
INNER JOIN
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit = "Banan") AS S2
ON S1.substanceID = S2.substanceID

Då får du något i stil med (s2.FruitID är alltid Banan, då)

SubstID, s1.FruitID, s1.qty, s2.qty, s2.FruitID


Av detta tar du skillnaden på qty:

SELECT s1.FruitID substanceID, (s1.qty - s2.qty) AS diff FROM
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit <> "Banan") AS S1
INNER JOIN
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit = "Banan") AS S2
ON S1.substanceID = S2... AND S1.FruitID ...

eller kanske (s1.qty - s2.qty)*(s1.qty - s2.qty) AS diff

Och till slut så slår du ihop alltihop per fruit

SELECT s1.FruitID, SUM(diff) FROM(
SELECT s1.FruitID, substanceID, (s1.qty - s2.qty)*(s1.qty - s2.qty) AS diff FROM
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit <> "Banan") AS S1
INNER JOIN
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit = "Banan") AS S2
ON S1.substanceID = S2... AND S1.FruitID ...)
GROUP BY s1.FruitID
ORDER BY s1.FruitID


(Sen kan du alltid ha med bananen också, även om den alltid hamnar överst.

Fan va korkad jag e. Vad du gör är att använda det relativa felet, och antingen summera det eller kvadraten på det.

Oki, vad menas med det?

Ja, det blir samma sak som posten (2008-03-12 13:37:48), förutom att du ska dela
med qty också, typ;

SELECT s1.FruitID, SUM(diff) FROM(
SELECT s1.FruitID, substanceID, (s1.qty - s2.qty)*(s1.qty - s2.qty)/(s2.qty*s2.qty) AS diff FROM
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit <> "Banan") AS S1
INNER JOIN
(SELECT substanceID, fruitID, qty FROM ... WHERE fruit = "Banan") AS S2
ON S1.substanceID = S2... AND S1.FruitID ...)
GROUP BY s1.FruitID
ORDER BY s1.FruitID