SQL JOIN? BETWEEN? ON?

i have two tables carModels and plates

carModels = modelID(int,primary key), makeID(int), modelName(varchar), startYear(int), endYear(int)

plates = plateID(int), plateName(varchar)

When i insert a variable eg @modelID into the query. I want to return from the query the platedID and plateNames from the plates tables. Between the startYear and the endYear of the carModel table for the specific modelID record of the @modelID

but i don't know which JOIN statement or Between or ON syntax to use

ps im using this as a table adapter in Vis Web Dev 05 Express (if that makes a difference)

please can anyone help

thanks

Gaz

Answer this question

SQL JOIN? BETWEEN? ON?

TimL
thanks very much again

it worked!
Lazo
thanks for your reply

the relation for the data is that on the carModel table the startYear and the endYear are int values which link to the plateID of the plates table

when i enter a modelID into the query i want it to return all the years that the car model was being made from the plates table

so a user can select the make then model of the car and then the query returns all the years the model of that car was made so they can select the year/registration plate that their car was made

thanks for your help

GazCorb
adam11235
Data eg

Variable

@model = 716

carModel

carModelID = 716, makeID = 42, modelName = Megane Coupe, startYear = 53, endYear = 72

plates

plateID = 53, plateName = 1996/N; plateID = 54, plateName = 1996/P; plateID = 55 plateName = 1997P ... plateID = 70, plateName = 2002/52; plateID = 71, plateName = 2003/03; plateID = 72, plateName = 2003/52

Query

SELECT plateID, plateName

FROM plates JOIN carModels

WHERE carModels.carModelID = @model (716)

BETWEEN carModels.startYear(53) and carModels.endYear(72)

would result in the following being out

53,1996/N

54, 1996/P

55, 1997/P ...

... 70, 2002/52

71, 2003/03

72, 2003/52

Any Ideas
Ashraf Gawdat
OK, I think I sorted that one out:

SELECT plateID, plateName
FROM plates,carModels
WHERE carModels.carModelID = @model AND
plateid BETWEEN carModels.startYear and carModels.endYear

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Sandeep Saxena
thanks very much

i'll try it out when i get home from work

i'll let you know how it goes

all the best

Gaz
pg53
YOu write JOIN between Models and plates, but on which columns to you want to join on As I see it from your sample data, there is no relation between these two entities.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
Placebo
I don’t see any driect relation between these two tables, perhpas you can post a sample of the data to make this more clearer.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---
SQL JOIN? BETWEEN? ON?

Answer this question