[MSSQL] Join vraagje

[RobIII]

Allereerst, mijn (recepten) DB ziet er als volgt uit:

Korte uitleg:
• tbl_Recipes bevat recepten
• tbl_RecipeInstructions bevat instructie(regels) voor een recept
• tbl_RecipeIngredients bevat ingrediënten die bij het recept horen
• tbl_RecipeKeyword is een koppeltabel welke een recept aan 1 of meerdere keywords koppelt
• tbl_RecipeKeywords bevat enkele keywords (Makkelijk, Snel, Goedkoop, Vegetarisch, Vis etc)
• tbl_Users bevat gebruikers
• rec_ln_id verwijst, evenals kw_ln_id naar een Language tabel (ik heb recepten in meerdere talen)
• De rest van de velden zijn niet erg interessant voor deze vraag om uit te leggen.

Ik heb dus bijvoorbeeld een recept (ik verzin er maar een):

code:

Recept:
  Title :Lekkere friet
  andere velden : bla

  Keywords
    Makkelijk
    Snel
    Goedkoop

  Ingredienten
    Aardappel
    Zout
    Mayo
    Curry
    Frikandel
    Kroket
    Knakworst
  
  Instructies:
    Sta op
    Loop naar friettent
    Bestel je friet
    Eet op

Nu wil ik dus zoeken op een recept dat voldoet aan 1 of meerdere criteria betreffende de keywords:

SQL:

Declare @intLNID as int
Declare @sKeywords as varchar

Set @intLNID = -1
Set @sKeywords = '1,2,6,9'

Select rec_id, rec_title, rec_date, rec_persons, rec_preptime, rec_cooktime, rec_suggestions, us_name
From tbl_Recipes
Inner join tbl_Users on rec_us_id = us_id
Inner join tbl_RecipeKeyword on (rk_rec_id = rec_id) and (rk_kw_id in (@sKeyWords))
Where (rec_deleted = 0) and (rec_online = 1) and (rec_screened = 1) and ((rec_ln_id = @intLNID) or (@intLNID<0))
Group by rec_id, rec_title, rec_date, rec_persons, rec_preptime, rec_cooktime, rec_suggestions, us_name
Order by rec_date desc

Deze code giet ik straks in een stored procedure, maar hij is nu zo omdat ik 'm aan het testen ben in de SQL Query analyser. Het probleem zit 'm dus in het in keyword ("...and (rk_kw_id in (@sKeyWords))")

Ik krijg nu dus records terug die aan 1 van de criteria (1,2,6 of 9 in dit geval) voldoen. _{(<<met bovenstaand vooreeld krijg ik geen records terug, terwijl er dus wel recepten zijn die voldoen aan de criteria).} Maar ik wil alleen recepten die aan alle criteria (1 en 2 en 6 en 9 dus) voldoen. Dit gaat dus niet lukken met het in keyword, en ik ben effe kwijt hoe ik het dan wel doe. Kan iemand me effe een duwtje geven in de juiste richting?

Oh, en dit stukje "((rec_ln_id = @intLNID) or (@intLNID<0))" zorgt ervoor dat ik alleen recepten in een bepaalde taal zoek (@intLNID>0) of in alle talen zoek (@intLNID<0), afhankelijk van welke waarde ik meegeef aan de SP. In het voorbeeld hierboven zoek ik dus op alle talen.

[ Voor 24% gewijzigd door RobIII op 01-06-2003 18:14 ]

[EfBe]

'in' kan alleen gebruikt worden met een static list of een subquery. Dus:
SELECT * FROM Table WHERE Field IN (1, 2, 3, 4)
of
SELECT * FROM Table WHERE Field IN (SELECT Field FROM Bar WHERE Duh=1)

Je wilt zoeken op keywords, dat wordt bijna altijd een dynamische query (dwz je bouwt een dynamische query op basis van die keywords en die pre-selecteert een setje rows waarmee je de rest van je data selecteert).

Verder, als ik je een tip mag geven: je veldnamen zijn zeer slecht gekozen. 'rec_' 'us_' en andere poeha, het is onleesbaar, en afkortingen zeggen niets, hele woorden wel. een UserID is een UserID, en geen us_ID. Een table Foo is een table die Foo heet en niet tbl_Foo, want je weet al dat het een table is. Gebruik nooit afkortingen, die paar tellen extra typewerk betalen je altijd dubbel en dwars terug...

[RobIII]

EfBe schreef op 01 juni 2003 @ 20:30:
<knip>

Verder, als ik je een tip mag geven: je veldnamen zijn zeer slecht gekozen. 'rec_' 'us_' en andere poeha, het is onleesbaar, en afkortingen zeggen niets, hele woorden wel. een UserID is een UserID, en geen us_ID. Een table Foo is een table die Foo heet en niet tbl_Foo, want je weet al dat het een table is. Gebruik nooit afkortingen, die paar tellen extra typewerk betalen je altijd dubbel en dwars terug...

1. De DB is niet van mij
2. Ik vind het persoonlijk vaak wel duidelijk om "voorloop" te gebruiken, maar dan in meer verwarrende tabellen.

Een dynamische query kan, maar die probeer ik eigenlijk te vermijden. Ik kan idd Exec() gebruiken daarvoor in de SP, maar volgens mij is dit beter op te lossen. Ik ben het alleen kwijt...

Ik kijk er morgen nog wel eens naar met een frisse kop. Dan verzin ik wel iets.

[EfBe]

Omdat je geen arrays kunt passen naar een stored procedure, en je vastzit aan een flexibele hoeveelheid parameters (1 tot n aantal keywords waarop je wilt zoeken), is er geen andere mogelijkheid dan de query dynamisch te maken. Parameters kunnen t.a.t. in sqlserver 1 value bevatten, nooit meerdere. Als je een fixed aantal keywords toestaat, dus bv 1 tot 10, dan kun je een stored procedure maken met 10 AND clauses zoals:
WHERE
rk_kw_id = COALESCE(@keyword1, rk_kw_id) OR
rk_kw_id = COALESCE(@keyword2, rk_kw_id) OR
rk_kw_id = COALESCE(@keyword3, rk_kw_id) OR
etc...
Keywordslots die je niet gebruikt zijn NULL, dus als je op 1 keyword zoekt zijn @keyword2 tm @keyword10 NULL.