[godonholiday]

hi i have a little join problem i think:

i have a table: cars [carno,teamname,factory,engine]

another table called: Drivers[driverno,fullname,carno,age,nationality]


I want to list all team names whose drivers have a specific nationality.


i currently use a search method were i use a form(uses GET) and a seachstring. I use a URL varialbe called SearchString and then Do a record set for the results.

can anyone help me with this?
thanks in advance
g

[godonholiday]

this is the code im trying but it dont seem to work??

PHP Code:

SELECT *

FROM cars INNER JOIN drivers ON car_no=carno

WHERE nationality like 'DriverSearchString' 

any help would be great thanks
g

[FiRe]

you dont have the same field in both tables such as a primary key so you cant do it!

[NeverMind]

Code:

SELECT
  cars.teamname
FROM
   cars LEFT OUTER JOIN Drivers 
      ON cars.carno=Drivers.carno
WHERE
   Drivers.nationality='$nationality'

try this one..

or you could use a normal join..

Code:

SELECT
  cars.teamname
FROM 
  cars, Drivers
WHERE 
  cars.carno=Drivers.carno
  AND Drivers.nationality='$nationality'

[godonholiday]

cheers mate that worked a treat.

two more thing, if you can help

1) i want to allow the user to search for a car were the factory date is within a user specified range?

will i have to have two text boxes, and ask them to enter two date ie, 03/03/05 and 04/04/06?

how do i do a search with these two strings?

or am i going about it in the wrong way?

2)the average thing i was trying to do, i need to join three tables i think, i need the average age of the drivers and mechanics for a specified team. so thats the driver, mechanic and car tables. any ideas?

thanks again.

g

[NeverMind]

1) i want to allow the user to search for a car were the factory date is within a user specified range?

will i have to have two text boxes, and ask them to enter two date ie, 03/03/05 and 04/04/06?

how do i do a search with these two strings?

what is the type of the date field you have in your database?

2)the average thing i was trying to do, i need to join three tables i think, i need the average age of the drivers and mechanics for a specified team. so thats the driver, mechanic and car tables. any ideas?

you mean you want to get the average of car drivers age by nationality?
this could be done with the same query you used from above, just add the following after SELECT:

Code:

AVG(Drivers.age) AS averageAge

and at the end put:

Code:

GROUP BY Drivers.nationality

so it would be:

Code:

SELECT
  cars.teamname,
  AVG(Drivers.age) AS averageAge
FROM 
  cars, Drivers
WHERE 
  cars.carno=Drivers.carno
  AND Drivers.nationality='$nationality'
GROUP BY
  Drivers.nationality

I didn't quit understand the mechanics part! what does the third table has in it?

[godonholiday]

what is the type of the date field you have in your database?
= its in an sql database as type 'date'

you mean you want to get the average of car drivers age by nationality?
= no, sorry i got that wrong, i want to list the average age of all personal who work for a user specified teamname.

table are: cars[carno,teamname,factorydate,make,sponsor]
drivers[fullname,carno,age,nationality,points]
mechanics[fullname,carno,age,nationality,skill,position]

hope that clears up what im trying to do

thanks for the help
g

[godonholiday]

this is what i have at the min:

[

PHP Code:

SELECT cars.teamname, AVG(drivers.age) AS averageAge

FROM cars, drivers, mechanics

WHERE cars.car_no=drivers.carno AND cars.car_no=mechanics.carno AND  teamname like 'DriverSearchString'

GROUP BY cars.teamname



i dont think this is right and idont know were do i put the searchstring? 

thanks agian

[NeverMind]

Code:

SELECT
  cars.teamname,
  AVG(Drivers.age) AS DriverAverageAge,
  AVG(mechanics.age) AS mechanicsAverageAge
FROM 
  cars, Drivers
WHERE 
  cars.carno=Drivers.carno
  AND cars.teamname='$teamname'
GROUP BY
  cars.teamname

don't use LIKE when you know the exact value of the teamname (or whatever field)

as for date comparision..
you could use:

Code:

SELECT * FROM cars WHERE factorydate > '2005-04-22' AND factorydate < '2005-04-25'

this will fetch everything from "cars" table between the dates 2005-04-22 and 2005-04-25
I assumed the field date's type as DATE (format YYYY-MM-DD)
the user input should be in format YYYY-MM-DD