Problemas de consulta tipo LEFT JOIN

[pucambtot]

Hola,
tengo poca experiencia en programación y me he atascado haciendo un consulta a varias tablas que antes resolvia con LEFT JOIN pero ahora ya no.

Necesito ayuda, por favor.

Se trata de una tabla principal llamada coches donde cada coche puede tener 2 pilotos (aunque en verdad son mas). La cuestion es que antes cuando solo me planteaba 1 no tenia problemas y lo resolvia con un LEFT JOIN por ejemplo :

SELECT
coche_id, coche_modelo, coche_piloto1_id,
pilotos.piloto_id,
pilotos.piloto_nombre
FROM coches
LEFT JOIN pilotos ON coches.coche_piloto1_id = pilotos.piloto_id
WHERE v=1

Asi luego podia mostrar el nombre del piloto en la ficha del vehiculo.


Pero... ¿y si en vez de 1, son mas? 2, 3, 4.... Como lo hago?


EJEMPLOS TABLAS.

Tabla --> coches
Campos :
coche_id
coche_modelo
coche_piloto1_id
coche_piloto2_id
coche_piloto3_id...etc

Tabla --> pilotos
Campos :
piloto_id
piloto_nombre



Gracias y un saludo.

[TolaWare]

Primero que nada, te convendría usar una tabla asociativa entre pilotos y coches.
Algo así

Tabla --> pilotos_coches
Campos:
coche_id // llave foránea que apunta a la tabla coches
piloto_id // llave foránea que apunta a la tabla pilotos (Debe ser la llave primaria)

De esa manera puedes asociar cualquier cantidad de pilotos a un coche. El campo piloto_id debe ser la llave primaria de la tabla (o por lo menos debe ser un campo UNIQUE) de manera que un piloto aparezca una sola ves en la tabla y por lo tanto esté asociado a solo un coche.

La consulta que deseas la puedes expresar con la instrucción INNER JOIN. EL único problema es que tendrías que obtener los pilotos de cada coche en consultas distintas.

SELECT pilotos_coches.coche_id , pilotos_coches.piloto_id , pilotos.nombre
FROM pilotos INNER JOIN pilotos_coches ON pilotos_coches.piloto_id = pilotos.piloto_id
WHERE pilotos_coches.coche_id = id_coche_a_buscar;

Donde id_coche_a_buscar contiene el id del coche a buscar .

[pucambtot]

Gracias TolaWare por tu rápida respuesta.
Pero si he de hacer una consulta diferente para cada piloto, (que de momento son 5) y tengo otros campos con la misma problematica (por ejemplo 'patrocinadores'), necesitaria un montón de consultas no?

Existe alguna manera de hacerlo todo en una sola SELECT? Los Alias servirian de algo en un caso así? Mas ideas?

Mil graciasssss

[gnzsoloyo]

En realidad tendrías que hacer una consulta escalonada, donde cada tabla derivada te de losregistros que cumplen con cada segmento y luego unir todo. Por ejemplo:

SELECT T1.coche_id , T1.piloto_id , T1.nombre, T2.patrocinador_id
FROM (SELECT PC.coche_id , PC.piloto_id , P.nombre FROM pilotos as P INNER JOIN pilotos_coches as PC USING (piloto_id) WHERE PC.coche_id = id_coche_a_buscar) as T1
INNER JOIN
(SELECT PT.patrocinador_id , P.piloto_id FROM pilotos as P INNER JOIN patrocinadores_pilotos as PT USING (piloto_id)) as T2 ;

La condicion necesaria para esto es:
1. Cada tabla derivada tiene que tener un alias.

2. Los campos pivote de los INNER JOIN deben estar en ambas derivadas, y en ambas primarias dentro de cada subconsulta. Los campos pivote de las derivadas pueden no ser los mismos que los de las primarias, las primaria se relacionan entre sí y las derivadas entre derivadas.

3. Los campos pivote en el SELECT final deben indicarse si o si de dónde se toman o habrá un error de campo ambiguo.

Notarás que usé la cláusula USING y no la cláusula ON. USING es más rápida que ON en MySQL pero requiere que los nombres de los campos en ambas tablas sean iguales. ON se usa fundamentalmente cuando los nombres de los campos son distintos. Además USING permite indicar un conjunto de campos a igualar en una sola sentencia.
Además es muy util usar los alias en los campos para cortar el código yhacerlo más legible.