[Løst] problematisk join / where

[petterg]

Jeg sliter med å få select query til å funke slik jeg vil.

 

Det er en database over medier, i første omgang kun bøker. Tabellen media innholder informasjon om hver bok, her redusert til kun relevante kollonner for problemstillingen. Tabellen LinkedIDS linker en bok med id=id til en bok med id=linkId. Det betyr at bok med id=linkId er originalutgivelsen (første utgave og/eller originalspråk). Der LinkedIDS.id = LinkedIDS.linkId betyr det at denne boka er selve originalen.

Det finnes også noen med LinkedId=null <= mangler informasjon

Og LinkedId=-1 <= originalen er ikke registrert i databasen

 

Så trenger jeg en query som gjør det mulig å søke etter en substring i title og resultatet skal bestå av alle versjoner av de bøkene som matcher med substringen.

 

Data er som følger:

TABLE Media

Id, title, language

1, aaa, no

2, aab, en

3, ccc, no

4, cca, se

5, ccb, en

6, eee, dk

 

TABLE LinkedIDS

id, linkId

1,1

2,1

3,3

4,3

5,3

6,6

 

F.eks si at man søker etter media.title LIKE %a%

Da er ønsket resultat:

id, linkedId, title, language

1, 1, aaa, no

2, 1, aab, en

3, 3, ccc, no

4, 3, cca, se

5, 3, ccb, en

 

Det nærmeste jeg har kommet er:

 SELECT l.id, l.linkedId, m.title, m.language FROM LinkedIDS l INNER JOIN Media m ON l.id = m.id LEFT JOIN media s ON l.linkedId = s.id

Men så fort jeg legger til WHERE m.title LIKE %a% OR s.title LIKE %a% utelukkes for mange rader (bl.a. id=5).

LEFT JOIN er brukt her fordi den skal ta med de tilfellene hvor linkedId=-1 eller linkedId=null

[Crowly]

Id 5 vil ikke være med da den tittelen ikke inneholder en a. Det du vil er at du skal liste ut alle bøker hvor original utgaven inneholder a i tittelen, og alle senere utgaver selv om de ikke innholder a? cca er originalen og ccb er utgave 2 og ccc er utgave 3?

 

Denne funker, men er usikker på om det er veldig optimal

SELECT l.id, l.linkId, m.title, m.language, s.title org_title
FROM Media m
INNER JOIN LinkedIDS l ON l.id = m.id
LEFT OUTER JOIN Media s ON s.id = l.linkid
WHERE l.linkid IN (
   SELECT DISTINCT(ll.linkid)
FROM LinkedIDS ll
INNER JOIN Media mm ON mm.id=ll.id
WHERE mm.title LIKE '%a%'
)

 

Resultat:

id linkId title language org_title

1 1 aaa no aaa

2 1 aab en aaa

3 3 ccc no ccc

4 3 cca se ccc

5 3 ccb en ccc

Endret 10. juli 2013 av Crowly

[petterg]

Takk Crowly. Det jeg jakter på er å få ut alle utgaver av bøker hvor tittelen i en eller annen utgave inneholder søkestrengen.

 

Synd jeg ikke sjekka forumet oftere i kveld. Jeg har funnet en annen query som gir tilsynelatende nøyaktig samme resultat.

 

SELECT DISTINCT lk.Id, lk.LinkId, s.* FROM

(SELECT k.Id, k.LinkId FROM

(SELECT Id FROM media WHERE Title LIKE '%a%') m

INNER JOIN LinkedIDS l ON m.Id=l.Id

INNER JOIN LinkedIDS k ON l.LinkId=k.LinkId) lk

INNER JOIN media s ON lk.Id=s.Id

 

Begge queryene går imidlertid glipp av de bøkene hvor linkId er null. Det kan jo ingen big deal om det vil kreve en separat query.

 

Og jeg har får ikke til å kjøre GROUP BY lk.LinkId på mitt forsøk. Hvorfor ikke det tro?

 

Edit:

Selv om jeg setter

SELECT DISTINCT lk.Id, lk.LinkId li, s.* FROM ....

får jeg invalid column name om jeg prøver å legge til WHERE li>1 eller GROUP BY li på slutten.

 

Edit2:

Kan jo undres hva jeg skulle med GROUP BY, er jo ORDER BY jeg skulle ha, og det funker. Begynner å bli lite igjen av dagen å våkne på.

 

Edit3: Hvilke av de to metodene vil være mest effektive på prosessering?

Hvordan kan man få med de radene hvor LinkId er null?

Endret 10. juli 2013 av petterg

[petterg]

Et problem med begge de to queryene er at hvis søkestrengen har match med en tittel hvor linkId=-1, så lister de opp alle rader hvor linkId=-1

 

 

Edit:

Løsningen på det problemet var UNION

 

 

SELECT DISTINCT lk.Id, lk.LinkId, s.* FROM

(SELECT k.Id, k.LinkId FROM

(SELECT Id FROM media WHERE Title LIKE '%a%') m

INNER JOIN LinkedIDS l ON m.Id=l.Id AND l.LinkId>0

INNER JOIN LinkedIDS k ON l.LinkId=k.LinkId

UNION

SELECT l.Id, l.LinkId FROM media h

INNER JOIN LinkedIDS l ON h.Id=l.Id AND l.LinkId<=0

WHERE Title LIKE '%a%'

) lk

INNER JOIN media s ON lk.Id=s.Id

 

I forkant kjører jeg da en UPDATE LinkedIDS SET LinkId=0 WHERE LinkId is null

 

 

Men så dukka det opp en ny sak. id=15240 har linkId=15239. id=15239 har LinkId=-5

Det betyr antagelig at bok 15240 er en oversettelse av 15239, men 15239 er antagelig ikke originalen, jeg gjetter at den er en ny utgivelse og at originalen ikke er i databasen. 15239 kommer ikke med i noen av queryene. Litt usikker på hvorfor den ikke gjør det.

Endret 11. juli 2013 av petterg

[petterg]

Nå har jeg omsider en query som ser ut til å virke i alle tilfellene

 

SELECT DISTINCT lk.Id, lk.LinkId, s.* FROM

(SELECT k.Id, k.LinkId FROM

(SELECT Id FROM media WHERE Title LIKE '%a%') m

INNER JOIN LinkedIDS l ON m.Id=l.Id

INNER JOIN LinkedIDS k ON (l.LinkId>0 AND l.LinkId=k.LinkId) OR l.LinkId=k.Id OR l.Id=k.Id

) lk

INNER JOIN media s ON lk.Id=s.Id

 

Bli det mindre prosessering om jeg erstatter den midterste INNER JOIN med denne (som gir samme sluttresultat)?

INNER JOIN LinkedIDS k ON (l.LinkId>0 AND l.LinkId=k.LinkId) OR (l.LinkId=k.Id AND k.LinkId<=0) OR ((l.LinkId<=0 OR l.LinkId is null) AND l.Id=k.Id)

 

Det blir åpenbart mye mer AND/OR for hver rad, men den slipper også at de fleste rader kommer dobbelt og dermed kanskje sparer prosesseringen av disse i siste INNER JOIN. Samt at DISTINCT ser ut til å bli overflødig. Hva tror dere?

[quantum]

 

Bli det mindre prosessering...

 

EXPLAIN PLAN select blablabla... de fleste databaser kan gi en forklaring på hvordan de planlegger å gjennomføre spørringer, hvilke indekser de vil bruke, hvilke tabeller de blir nødt til å gjøre full tablescan på osv., samt å gi en beregning av hva det "koster" å kjøre spørringen. Google litt i forhold til den databasen du bruker så finner du raskt ut hvordan du gjør det.

[petterg]

Ante ikke at den funksjonen eksisterte.

Konklusjonen er at masse and/or reduserer belastningen med 33%.