Avatar billede mccannon Nybegynder
21. april 2003 - 11:49 Der er 19 kommentarer og
1 løsning

Problemer med sql

Hejsa

Okay,jeg har en vare database med en masse vare i, problemet er at feks når der skal listes fælge så skal der kun listes en af hver type..

eksempler på typer:
- BBS RC
- BBS RK
- BSS RK II

SQL sætningen lige nu ser sådan ud .:
SQL = "SELECT tblbmwnrmodel.bmwnr, tblbmwnrmodel.modelnr, vare.partnr, vare.del, vare.kategori, vare.beskrivelse,"
    SQL = SQL & " vare.billed, vare.specialbmrk, vare.detailpris, vare.vaegt, vare.tilbud, vare.oprettet, vare.topsellers, tblproducent.prodnavn FROM (tblbmwnrmodel"
    SQL = SQL & " INNER JOIN vare ON tblbmwnrmodel.bmwnr = vare.partnr)"
    SQL = SQL & " LEFT JOIN tblproducent ON vare.prodid = tblproducent.prodid"
    SQL = SQL & " WHERE tblbmwnrmodel.Modelnr = '" & Modelnr & "'"
    SQL = SQL & " AND vare.kategori = " & kategori
    SQL = SQL & " ORDER BY " & sort

please help :)

mch
McCannon
Avatar billede terry Ekspert
21. april 2003 - 11:51 #1
SELECT DISTINCT
or
DISTINCTROW
Avatar billede mccannon Nybegynder
21. april 2003 - 11:52 #2
mangler lige at tilføje feks BBS RC er i flere udgaver dvs størrelser som feks
- BBS RC 8x17
- BBS RC 8.5x18
- BBS RC 8.5x17

sådan er det for alle fælgene
Avatar billede mccannon Nybegynder
21. april 2003 - 11:52 #3
Hi Terry

ive tried that, didnt work :(
Avatar billede terry Ekspert
21. april 2003 - 11:53 #4
The problem is it will still return duplicates IF some fields are different. So you may need to remove fields which produce different values.
Avatar billede terry Ekspert
21. april 2003 - 11:54 #5
Your JOINS will very likely produce a number of records for each "fælge" and they MUST be DISTINCT (the same)
Avatar billede mccannon Nybegynder
21. april 2003 - 11:57 #6
Hmm well then im in trouble, since i need the fields which do produce different values....
Avatar billede mccannon Nybegynder
21. april 2003 - 12:00 #7
you can see it here :
www.nikis-place.com

click shop -> pick a model(like E46) -> click rims at the bottom
Avatar billede terry Ekspert
21. april 2003 - 12:01 #8
sounds like it :o)
but if you want to see only one of each type WHY do you only want to see one if there are different values?

You could show for example the "main type" and then under that show each different value, if you understand what I mean?
Avatar billede terry Ekspert
21. april 2003 - 12:03 #9
You could show for example the "BS Challenge" ......
Avatar billede terry Ekspert
21. april 2003 - 12:04 #10
yes thats what I would do, a sub line for each size/price
Avatar billede mccannon Nybegynder
21. april 2003 - 12:05 #11
well because teh owner of teh site wants it :-)

the thing is, when they are listed in the category, they should listed like one for each type, then when they click on a link they will be taken to a page when details on the specific type of rim are displayed and then there will be a dropdown list thingy where they can chose the size of the rim.
Avatar billede terry Ekspert
21. april 2003 - 12:05 #12
You can still use the same SQL.
Avatar billede terry Ekspert
21. april 2003 - 12:07 #13
well the way it is now you can NOT get around having "duplicates".
Avatar billede mccannon Nybegynder
21. april 2003 - 12:12 #14
let me see if i get this :
you suggests that in the menu under rims we have some sort of categorys like
rims
  - BBS CH
  - BSS RK

and so on
and then when peopel click the BBS CH link alle the different types of CH will be listed ?
Avatar billede terry Ekspert
21. april 2003 - 12:14 #15
if you had the picture of the rim and only "BS Challenge" beside it. Then the text with the description.
Then for each size a line containing
8,5x17 $390.25
8,5x18 $400.00
Avatar billede terry Ekspert
21. april 2003 - 12:16 #16
So you will get only one picture and one or more lines with size an dprice etc.
it would also fill less room on the screen
Avatar billede mccannon Nybegynder
21. april 2003 - 12:18 #17
hmm and i can do that with the same sql ?
Avatar billede terry Ekspert
21. april 2003 - 12:27 #18
Yes you should be able to, thats if for example "BS Challenge" is in a field on its own WITHOUT the size. Otherwise your going to have to change your dB! (normalise).
So the first record you would use the data to show the picture and description etc. and the first size/price line. Then you loop through the remaing records (for that rim) and display the other size/price lines.
You could also have two recordsets (SQL) one for the rim data and one for the size/price lines. You have an outer loop for displaying the rim data nd an inner loop displaying the size/price data.
Avatar billede mccannon Nybegynder
21. april 2003 - 12:35 #19
Yup..
ive just recieved permition to change the structure of the database so each rime type like BBS CH and BSS RK gets it's own uniqe part no. and then make a new table with teh sizes in that should make it a bit easier :)

tahnk you for your help :)
Avatar billede terry Ekspert
21. april 2003 - 12:44 #20
your welcome :o)
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester