1
votes

Comment puis-je joindre deux tables (toutes deux dérivées de l'auto-jointure) pour créer une troisième table?

J'essaie de résoudre la question https://sqlzoo.net/wiki/Self_join , se joint à la question n ° 10 spécifiquement qui dit que:

Trouvez les itinéraires impliquant deux bus qui peuvent aller de Craiglockhart à Lochend. Montrez le bus no. et compagnie pour le premier bus, le nom de l'arrêt pour le transfert et le bus no. et compagnie pour le deuxième bus. ».

J'ai mon code et il me donne l'erreur en disant:

DUPLICATE nom de colonne 'num'

Voici mon code:

 SELECT * FROM
    (SELECT *
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num
    JOIN stops stopa ON (a.stop = stopa.id)
    JOIN stops stopb ON (b.stop = stopb.id)
    WHERE stopa.name = 'Craiglockhart') big  
                              /* [big] table Gives all buses from 
    craiglockhart */

    JOIN


    (SELECT *
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num
    JOIN stops stopa ON (a.stop = stopa.id)
    JOIN stops stopb ON (b.stop = stopb.id)
    WHERE stopa.name = 'Lochend') small
                              /*[small] Gives all buses from Lochend */
    ON big.b.stop = small.b.stop
                             /*Trying to join the two tables on the basis of 
    the matching values from [big].b.stop field with [small].b.stop */

Je ne sais pas si cela donnera le résultat souhaité. Si c'est le cas, est-ce un moyen efficace? sinon quelqu'un peut-il m'aider à écrire et à m'expliquer comment cela fonctionnera merci?

sql mariadb

0 commentaires

3 Réponses :

0
votes

À propos de DUPLICATE COLUMN num :

En cas d'auto-jointure, vous avez un double jeu de colonnes (y compris la colonne num ): un de route a et l'autre de route b

(SELECT a.*
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num

Vous devez remplacer * par les noms de champs ne permettent pas de dupliquer les noms de champs

(SELECT * -- double set of columns from table [route]
    FROM route a JOIN route b 
    ON a.company = b.company AND a.num = b.num

ou même mieux - ne soyez pas paresseux et écrivez chaque champ nécessaire par son nom

2 commentaires

Salut, en fait SELECT * FROM route une route JOIN b ON a.company = b.company ET a.num = b.num cela fonctionne sans aucune erreur.

Oui. Cela fonctionne tout seul mais ne peut pas fonctionner dans subselect

1
votes

Tout d'abord, vous avez fait ceci: ON a.company = b.company ET a.num = b.num

-> il renvoie la même valeur pour la colonne num - donc c'est un doublon.

Utilisez des index - par exemple: a.num & b.num :

SELECT DISTINCT x.num, x.company,x.name,y.num,y.company
FROM (

select a.num as num, a.company as company, stopb.name as name
FROM route a
JOIN route b
ON a.company = b.company AND a.num = b.num
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart') x

JOIN

(select a.num as num, a.company as company, stopb.name as name
FROM route a
JOIN route b
ON a.company = b.company AND a.num = b.num
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Lochend') y

ON x.name = y.name 

ORDER BY x.num

ON big.b.stop = small.b.stop supprime également une erreur

bonne réponse:

SELECT a.num
FROM route a JOIN route b 
ON a.company = b.company AND a.num = b.num

--OR

SELECT b.num
FROM route a JOIN route b 
ON a.company = b.company AND a.num = b.num

0 commentaires

1
votes

Voici une solution qui est marquée «Â Bonne réponseÂ» par sqlzoo.

Cela fonctionne en sélectionnant d'abord toutes les lignes qui s'arrêtent à Craiglockhart, et, d'autre part, toutes celles qui s'arrêtent à Lochend. Chacune de ces recherches nécessite deux JOIN (arrêts + itinéraire).

Enfin, la requête localise tous les arrêts appartenant aux deux lignes, en utilisant un JOIN spécial avec des conditions EXISTS.

SELECT
    r1.num,
    r1.company,
    s3.name,
    r2.num,
    r2.company
FROM 
    stops s1
    INNER JOIN route r1 ON r1.stop = s1.id
    INNER JOIN stops s2 ON s2.name = 'Lochend'
    INNER JOIN route r2 ON r2.stop = s2.id
    INNER JOIN stops s3
        ON EXISTS (
            SELECT 1 
            FROM route
            WHERE 
                num = r1.num 
                AND company = r1.company
                AND stop = s3.id
        )
        AND EXISTS (
            SELECT 1 
            FROM route
            WHERE 
                num = r2.num 
                AND company = r2.company
                AND stop = s3.id
        )
WHERE 
    s1.name = 'Craiglockhart'

0 commentaires