[JSP/SQL] Range waarde - zoekfunctie

[Verwijderd]

Ik heb een database met 2 veldjes leeftijd_van en leeftijd_tot. Deze kunnen en moeten via een back-end zo ingevuld worden. Hier moet een zoekfunctie op losgelaten worden waarbij je de veldjes leeftijd_van en leeftijd_tot zelf kan ingeven.

Dit werk allemaal. Maar als in de db de leeftijd_van en leeftijd_tot bijvoorbeeld op respectievelijk 12 en 16 staan, dan zou als er gezocht wordt met leeftijd_van 14 en leeftijd_tot 16 dit ook moeten verschijnen in de zoekresultaten.

Hieronder een deel van de SQL die dus uiteraard niet naar behoren werkt. Ik heb met IN en andere SQL functies geprobeerd maar ik vermoed dat dit in de code opgevangen moet worden i.p.v. met SQL?

code:

        if(_leeftijd_van != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_van >= " + _leeftijd_van;
        }
        if(_leeftijd_tot != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_tot <= " + _leeftijd_tot;
        }

[Verwijderd]

tweede AND vervangen door OR, iets in die trend iig

[ Voor 29% gewijzigd door Verwijderd op 07-12-2006 17:11 ]

[Verwijderd]

Dit lost niks op. Ik denk dat dit programmatorisch opgevangen moet worden, wie zet mij op de goede weg?

[Verwijderd]

hmm ok, misschien moet je dan de BETWEEN functie van SQL even checken...

[Verwijderd]

Java:

if((leeftijd_van > 0) && (leeftijd_tot > leeftijd_van)) {
   
   String queryString = "SELECT * FROM tabel WHERE leeftijd BETWEEN '"+leeftijd_van+"' AND '"+leeftijd_tot+"'";
}

Zoiets?

[ Voor 45% gewijzigd door Verwijderd op 07-12-2006 17:29 ]

[Verwijderd]

Met de BETWEEN functie kom ik er niet. Worteltaart , je voorbeeld gaat enkel nakijken of de velden allebei aangegeven zijn en of het 2de veld groter is dan het eerste, dit is niet voldoende.

Ik zal dit nog eens verder toelichten. In de database zitten allemaal leeftijd_van en leeftijd_tot veldjes met alle mogelijke combinaties (van 8 tot 18 jaar, van 10 tot 12 jaar, van 5 tot 7 jaar, enz...) . In de zoekfunctie kunnen eveneens 2 veldjes ingegeven worden (van/tot).

Probleem is dat het zoeken heel de leeftijdsrange moet bestrijken (dus zoeken op van 10 tot 12 jaar betekent dat 10 tot 12, 11 tot 12 en 12 tot 12 allemaal bij het zoekresultaat moeten uitkomen als er een record van 10 tot 12 jaar in de db zit)

Zoals ik daarnet ook zei:
"Als je in de db de leeftijd_van en leeftijd_tot bijvoorbeeld op respectievelijk 12 en 16 staan, dan zou als er gezocht wordt met leeftijd_van 14 en leeftijd_tot 16 dit ook moeten verschijnen in de zoekresultaten."

Dit is bij de oplossingen die jullie voorlopig hebben aangedragen niet het geval.

[bazkar]

Ok, je wilt een range checker.

Welke results wil je precies terug?
Ik neem aan dat je een record terug wilt hebben als je invoerdata geheel in de data van het record valt?

Ofwel: Als je als testdata van 14, tot 16 invult en je recordset is als volgt:

code:

van tot
12  16
14  18
14  16
15  16
15  18
12  15
 3   5
18  20

dat je alleen de eerste 4 records terug wilt?

Dan moet dus moet dus je code worden:

code:

        if(_leeftijd_van != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_van <= " + _leeftijd_van;
        }
        if(_leeftijd_tot != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_tot >= " + _leeftijd_tot;
        }

Als het andersom is (de recorddata moet geheel binnen de invoerdata vallen, en dus wil je in bovenstaand voorbeeld alleen het 3e en 4e record terug) dan wordt je code:

code:

        if(_leeftijd_van != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_van >= " + _leeftijd_van;
        }
        if(_leeftijd_tot != 0){
            sqlString += "\nAND    ";
            sqlString += "rg.leeftijd_tot <= " + _leeftijd_tot;
        }

Als je tenslotte records terug wilt die een ook maar een gedeelte overlappen (dus uit het voorbeeld de eerste 6 records) dan wordt je code:

code:

        if(_leeftijd_van != 0 || leeftijd_tot !=0){
            sqlString += "\nAND  (0=0  ";
        }
        if(_leeftijd_van != 0){
            sqlString += "\nOR    ";
            sqlString += "rg.leeftijd_van <= " + _leeftijd_tot;
        }
        if(_leeftijd_tot != 0){
            sqlString += "\nOR    ";
            sqlString += "rg.leeftijd_tot >= " + _leeftijd_van;
        }
        if(_leeftijd_van != 0 || leeftijd_tot !=0){
            sqlString += "\n)";
        }

EDIT:

Nu ik je verhaal hierboven lees, lijkt het alsof je records terug wilt die niet in de database zitten?
Als je van 10 tot 12 invult en het enige record in de database is van 10 tot 12 dan wil je als result 3 records??? 10 tot 12, 11 tot 12 en 12 tot 12?

Dat is erg niet handig met SQL te doen

[ Voor 53% gewijzigd door bazkar op 07-12-2006 17:53 ]

[Verwijderd]

Als er gezocht wordt van 14 tot 16 wil ik uit de voorbeeldrecordset inderdaad de 4de en de 5de mogelijkheid terugkrijgen. Maar stel je dan eens voor dat iemand 15 tot 16 ingeeft en dat dit niet in de database zit.

Ergens moet je dan weten dat er bijvoorbeeld een record bestaat van 14-16 in de database en dat deze zoekopdracht dus dit record als resultaat moet teruggeven, enz... Het lijkt simpeler dan het is, toch al bedankt voor je uitgebreide toelichting...

EDIT:

Ik hoef niet noodzakelijk meerdere records, maar er moet wel degelijk nagekeken worden wat binnen de range valt. Anders heb je dus een probleem in jouw voorbeeld: stel je geeft van 13 tot 14 in , dan zou je ook de resultaten uit de db moeten zien van alles wat bijvoorbeeld van 12 tot 14 is ; wat niet het geval is in de voorbeelden. Dat is nu net het probleem

[ Voor 34% gewijzigd door Verwijderd op 07-12-2006 18:05 ]

[Verwijderd]

Aah, now I see the point, je beschrijving kwam een beetje vaag op me over, lange dag geweest

Je wilt dus als de range 12-14 is opgegeven ook alles van (bijvoorbeeld) 11-13 op je scherm getoverd zien?

Wat dacht je van dit:
Stel er wordt een range in gegeven -> 12 -14...

->Zoek alle ranges waar 12 in voor kan komen (11-13, 8-12, ...) en fetch de bijbehorende values
->Zoek alle ranges waar 14 in voor kan komen (1-18, 14-16, ...) en fetch de bijbehorende values

Vervolgens is het een kwestie van alle dubbelen eruit filteren (indien nodig natuurlijk).

Ik weet niet hoe je datamodel precies in elkaar zit, misschien kan je daar wat meer van laten zien. Maar zoiets zoek je dus waarschijnlijk?

[Verwijderd]

>Je wilt dus als de range 12-14 is opgegeven ook alles van (bijvoorbeeld) 11-13 op je scherm >getoverd zien?

Eigenlijk wil ik dat als de range 12-14 opgegeven is ook bijvoorbeeld 13-14 bij de resultaten verschijnt. Je moet het zien in de context van vakanties die afhankelijk van de leeftijd aangeboden worden.

Je wil kijken welke vakanties er zoal voor 12 tot 14 jarigen zijn?

Dan wil je alles wat volledig binnen de range valt weergeven (dus 13-14 wel, 11-14 niet, alhoewel daar misschien wel over te discussiëren valt)

[Verwijderd]

ingegeven 14 en 16

is dit dan toevallig wat je zoekt?

SQL:

SELECT * 
FROM bla
WHERE 14 BETWEEN leeftijd_van AND leeftijd_tot

UNION --< haalt alle dubbele er al uit

SELECT * 
FROM bla
WHERE 16 BETWEEN leeftijd_van AND leeftijd_tot

[Verwijderd]

Nee als je de kwestie goed leest zie je dat dit geen oplossing is. Stel dat je 12 en 14 ingeeft en er wordt een record gevonden, ok. Maar als je dan bijvoorbeeld 13 en 14 ingeeft worden de waarden gewoon vervangen en wordt er dus NIET meer gekeken naar eventuele records tussen 12 en 14 waarbinnen 13,14 perfect past. Snap je het probleem?

[Verwijderd]

Het zal wel komen omdat ik pas 3 koppen koffie op heb, maar ik vind de probleemomschrijving uitermate vaag.

In de database heb je records met velden db.van en db.tot. Een gebruiker zoekt op waarden user.van en user.tot. Wil je nu als resultaat:

a. alle records waarbij user.van en user.tot tussen db.van en db.tot vallen, of
b. alle records waarbij db.van en db.tot tussen user.van en user.tot vallen, of
c. alle records waarbij de periode user.van tot user.tot de periode db.van tot db.tot overlapt, of
d. een combinatie van a, b en c?

[bazkar]

Verwijderd schreef op vrijdag 08 december 2006 @ 10:29:
Het zal wel komen omdat ik pas 3 koppen koffie op heb, maar ik vind de probleemomschrijving uitermate vaag.

In de database heb je records met velden db.van en db.tot. Een gebruiker zoekt op waarden user.van en user.tot. Wil je nu als resultaat:

a. alle records waarbij user.van en user.tot tussen db.van en db.tot vallen, of
b. alle records waarbij db.van en db.tot tussen user.van en user.tot vallen, of
c. alle records waarbij de periode user.van tot user.tot de periode db.van tot db.tot overlapt, of
d. een combinatie van a, b en c?

c is een combinatie van a en b, en voor alle 3 heb ik de query hierboven opgeschreven. Ik zie het probleem dan ook niet meer

[bazkar]

Verwijderd schreef op donderdag 07 december 2006 @ 17:57:
Als er gezocht wordt van 14 tot 16 wil ik uit de voorbeeldrecordset inderdaad de 4de en de 5de mogelijkheid terugkrijgen. Maar stel je dan eens voor dat iemand 15 tot 16 ingeeft en dat dit niet in de database zit.

Ergens moet je dan weten dat er bijvoorbeeld een record bestaat van 14-16 in de database en dat deze zoekopdracht dus dit record als resultaat moet teruggeven, enz... Het lijkt simpeler dan het is, toch al bedankt voor je uitgebreide toelichting...

Dat is dus precies wat de 2e query in mijn voorbeeld hierboven doet. (die geeft het 3e en 4e record, ofwel de 4e en 5e regel in het codeblok terug).
Het boeit niet of het record in precies dezelfde vorm in de database bestaat. Als record 3 (op de 4e regel dus) in de database zou ontbreken, dan zou query 2 nog altijd het record van 15 tot 16 teruggeven hoor, daarvoor zijn de <= >= Kijk maar:

Geef me alle records waarvan de databaseleeftijd_van groter of gelijk is dan de ingevoerde leeftijd_van (15 is groter dan 14 dus record voldoet) en waarvan de databaseleeftijd_tot kleiner of gelijk is dan de ingevoerde leeftijd_tot (16 is gelijk aan 16 dus record voldoet).

Duidelijker dan dit kan ik het echt niet uitleggen.....

[ Voor 18% gewijzigd door bazkar op 08-12-2006 17:07 ]

[B-Man]

Hmm, wil de TS soms zoiets:

• pak alle records waarvoor geld record.van >= search.van AND record.tot <= search.tot
• pak ook alle records waarvoor geld record.van < search.van AND record.tot = search.tot (zoeken op 13-14 moet ook 12-14 teruggeven)
• pak ook alle records waarvoor geld record.van = search.van AND record.tot > search.tot (zoeken op 13-16 moet ook 13-18 weergeven)

Eventueel is dit nog flexibeler te maken:

• pak ook alle records waarvoor geld record.tot BETWEEN search.van AND search.tot (zoeken op 13-14 moet ook 12-14 teruggeven)
• pak ook alle records waarvoor geld record.van BETWEEN search.van AND search.tot (zoeken op 13-16 moet ook 13-18 weergeven)

Dan krijg je alle ranges waarvan de boven of ondergrens in de gevraagde range vallen. (De eerste filter kan dan vervallen, die records worden namelijk ook al gevonden door de bovenstaande twee filters).

Deze twee filters middels OR in je query zetten levert het gevraagde resultaat volgens mij.

[ Voor 61% gewijzigd door B-Man op 08-12-2006 19:08 ]

[Verwijderd]

bazkar schreef op vrijdag 08 december 2006 @ 16:58:
[...]


c is een combinatie van a en b, en voor alle 3 heb ik de query hierboven opgeschreven. Ik zie het probleem dan ook niet meer

c is geen combinatie van a en b. Stel: db bevat een record met db.van = 12 en db.tot = 16; gebruiker zoekt op user.van = 10 en user.tot = 14. Dit valt niet onder a, en niet onder b, maar wel onder c.

[bazkar]

Verwijderd schreef op vrijdag 08 december 2006 @ 21:56:
[...]


c is geen combinatie van a en b. Stel: db bevat een record met db.van = 12 en db.tot = 16; gebruiker zoekt op user.van = 10 en user.tot = 14. Dit valt niet onder a, en niet onder b, maar wel onder c.

I stand corrected. Wat ik had moeten zeggen was: d is hetzelfde als c. Als je een overlappende range checker maakt dan retourneert deze ook de aan a en b voldoenende zaken.

[Verwijderd]

SQL:

select
    *
from
    bla
where
    (leeftijdvan <= #leeftijdvan# and leeftijdtot >= #leeftijdvan#)
    or
    (leeftijdvan <= #leeftijdtot# and leeftijdtot >= #leeftijdtot#)

Het gaat erom dat leeftijd_van in een bepaalde range zit. (gelijk aan of groter dan leeftijdvan en gelijk aan of kleiner dan leeftijdtot)
Maar leeftijdtot moet ik een een range zitten (gelijk aan of groter dan leeftijdvan en gelijk aan of kleiner dan leeftijdtot)

[Verwijderd]

Ik denk dat de laatste inderdaad de beste is. Bedankt iedereen voor het meedenken, hier volgt de java code zoals deze gebruikt zal worden:

code:

if(_leeftijd_tot != 0 && _leeftijd_van !=0) {
             sqlString += "\nAND    ";       
             sqlString += "((rg.leeftijd_van <= "+ _leeftijd_van + " AND rg.leeftijd_tot >= " + _leeftijd_tot +")";
             sqlString += " OR (rg.leeftijd_van <= "+ _leeftijd_tot + " AND rg.leeftijd_tot >= " + _leeftijd_tot + "))" ;
        }

[bazkar]

Grappig, want met die query krijg je dus record "van 14 tot 15" niet terug als er "van 12 tot 16" ingetikt wordt als zoekwaarde. Ik begin langzaam maar zeker te denken dat je zelf niet precies weet wat je terug wilt hebben.....

In mijn database hierboven had ik met opzet de invoerwaarde en de database zo gekozen dat alle mogelijkheden erin zaten. Ok, nog een keer:

Zoekoptie 13 tot 16.

• Record helemaal kleiner dan de search: van 3 tot 5
• Record helemaal groter dan de search: van 18 tot 20
• Record wat de search omvat: van 12 tot 18
• Record wat helemaal binnen de search ligt: van 14 tot 15
• Record wat de search aan de voorkant overlapt: van 12 tot 15
• Record wat de search aan de achterkant overlapt: van 15 tot 18
• Record identiek aan de search: van 13 tot 16

In je reply dacht ik dat je gezegd had dat je hiervan alleen de records die identiek waren aan de search (13 tot 16) of die helemaal binnen de search lagen (14 tot 15) terugwilde. Dit blijkt dus niet zo te zijn. Ok nog 1 keer dan: Welke van bovenstaande records in het lijstje zou je terug willen bij bovenstaande search?? Met je huidige query, die van m.vanderlee, krijg je (als je de tikfout leeftijd_tot->leeftijd_van eruit haalt in de eerste regel) in ieder geval nummers 3, 5, 6 en 7 terug (maar dus NIET nummer 4).

Als je 4 ook terugwilt, dan heb je nodig:

code:

if(_leeftijd_tot != 0 && _leeftijd_van !=0) {
             sqlString += "\nAND    ";       
             sqlString += "rg.leeftijd_van <= "+ _leeftijd_tot + " AND rg.leeftijd_tot >= " + _leeftijd_van ;
}

Dit was mijn optie 3 van ver hierboven, waarbij de OR alleen een AND moest zijn, stomme fout van mij

[ Voor 22% gewijzigd door bazkar op 11-12-2006 18:13 ]

[Verwijderd]

bazkar schreef op maandag 11 december 2006 @ 18:06:
Grappig, want met die query krijg je dus record "van 14 tot 15" niet terug als er "van 12 tot 16" ingetikt wordt als zoekwaarde. Ik begin langzaam maar zeker te denken dat je zelf niet precies weet wat je terug wilt hebben.....

Lijkt mij ook een vreemde waarde om terug te krijgen. 14 en 15 ligt namelijk wel tussen 12 en 16. Maar ik denk dat het belangrijk is dat 12 niet tussen 14 en 15 ligt.

Stel dat het gaat om films, die geschikt zijn voor mensen van 12 tot 16 jaar, dan zijn films voor 14 tot 15 jaar niet geschikt voor de 12 of 13-jarige personen.

Het is dus een beetje afhankelijk wat voor data de topicstarter precies wilt zien.

[bazkar]

Verwijderd schreef op dinsdag 12 december 2006 @ 17:04:
[...]

Lijkt mij ook een vreemde waarde om terug te krijgen. 14 en 15 ligt namelijk wel tussen 12 en 16. Maar ik denk dat het belangrijk is dat 12 niet tussen 14 en 15 ligt.

Stel dat het gaat om films, die geschikt zijn voor mensen van 12 tot 16 jaar, dan zijn films voor 14 tot 15 jaar niet geschikt voor de 12 of 13-jarige personen.

Het is dus een beetje afhankelijk wat voor data de topicstarter precies wilt zien.

Nee ok, maar het lijkt me sterk dat je in het geval van films wel het record "van 15 tot 18" terug wilt hebben als je zoekt op "van 12 tot 16" en dat record krijg je wel terug met jouw query als ie in de database zit.... Vandaar ook mijn vraag of de TS wel precies weet wat ie wel en niet terug wil hebben...