requêtes imbriquées, encore ! [Résolu]

[jur466]

Je voudrais lire dans la table "NacPort", les champs "NACPort_IsPartOf", "NACPort_MifIndex" qualifiés "NACPort_IsLearn" = 1 , et dans la table Itf, retrouver le champ "Itf_MifDescr " de ("NACPort_IsPartOf", "NACPort_MifIndex").

Les 3 requêtes ci dessous peuvent elles être réalisées en une requête imbriquée ?

Code :

1
2
3
SELECT * 
FROM NACPort 
WHERE NACPort_IsLearn = 1;

+-------------+------------------+------------------+-----------------+-------------------+---------------------+---------------------+
| NACPort_uid | NACPort_MifIndex | NACPort_IsPartOf | NACPort_IsLearn | NACPort_IsDeLearn | NACPort_AuthDate    | NACPort_ForbDate    |
+-------------+------------------+------------------+-----------------+-------------------+---------------------+---------------------+
|          32 |            10024 | mgth03  |               1 |                 0 | 2011-07-06 15:26:35 | 2000-01-01 01:01:01 |
|          31 |            10001 | mgth99  |               1 |                 0 | 2011-07-04 17:25:29 | 2000-01-01 01:01:01 |
+-------------+------------------+------------------+-----------------+-------------------+---------------------+---------------------+

Code :

1
2
3
SELECT Itf_uid,Itf_MifIndex,Itf_MifName,Itf_MifDescr,Itf_IsPartOf 
FROM Itf 
WHERE Itf_IsPartOf = "mgth03" ;

+---------+--------------+-------------+--------------------+-----------------+
| Itf_uid | Itf_MifIndex | Itf_MifName | Itf_MifDescr       | Itf_IsPartOf    |
+---------+--------------+-------------+--------------------+-----------------+
|  229423 |        10012 | Fa0/12      | FastEthernet0/12   | mgth03 |
|  229421 |        10010 | Fa0/10      | FastEthernet0/10   | mgth03 |
|  229438 |        10501 | Nu0         | Null0              | mgth03 |
|  229435 |        10024 | Fa0/24      | FastEthernet0/24   | mgth03 |
|  229434 |        10023 | Fa0/23      | FastEthernet0/23   | mgth03 |
|  229433 |        10022 | Fa0/22      | FastEthernet0/22   | mgth03 |
|  229432 |        10021 | Fa0/21      | FastEthernet0/21   | mgth03 |
|  229431 |        10020 | Fa0/20      | FastEthernet0/20   | mgth03 |
|  229430 |        10019 | Fa0/19      | FastEthernet0/19   | mgth03 |
|  229429 |        10018 | Fa0/18      | FastEthernet0/18   | mgth03 |
|  229428 |        10017 | Fa0/17      | FastEthernet0/17   | mgth03 |
|  229427 |        10016 | Fa0/16      | FastEthernet0/16   | mgth03 |
|  229425 |        10014 | Fa0/14      | FastEthernet0/14   | mgth03 |
|  229424 |        10013 | Fa0/13      | FastEthernet0/13   | mgth03 |
|  229422 |        10011 | Fa0/11      | FastEthernet0/11   | mgth03 |
|  229420 |        10009 | Fa0/9       | FastEthernet0/9    | mgth03 |
|  229419 |        10008 | Fa0/8       | FastEthernet0/8    | mgth03 |
|  229418 |        10007 | Fa0/7       | FastEthernet0/7    | mgth03 |
|  229417 |        10006 | Fa0/6       | FastEthernet0/6    | mgth03 |
|  229414 |        10003 | Fa0/3       | FastEthernet0/3    | mgth03 |
|  229413 |        10002 | Fa0/2       | FastEthernet0/2    | mgth03 |
|  229412 |        10001 | Fa0/1       | FastEthernet0/1    | mgth03 |
|  229411 |            1 | Vl1         | Vlan1              | mgth03 |
|  229437 |        10102 | Gi0/2       | GigabitEthernet0/2 | mgth03 |
|  229436 |        10101 | Gi0/1       | GigabitEthernet0/1 | mgth03 |
|  229416 |        10005 | Fa0/5       | FastEthernet0/5    | mgth03 |
|  229426 |        10015 | Fa0/15      | FastEthernet0/15   | mgth03 |
|  229415 |        10004 | Fa0/4       | FastEthernet0/4    | mgth03 |
|  229439 |          233 | Vl233       | Vlan233            | mgth03|
+---------+--------------+-------------+--------------------+-----------------+
29 rows in set (0.01 sec)

Code :

1
2
3
SELECT Itf_uid,Itf_MifIndex,Itf_MifName,Itf_MifDescr,Itf_IsPartOf 
FROM Itf 
WHERE Itf_IsPartOf = "mgth03" AND Itf_MifIndex = 10024 ;

+---------+--------------+-------------+------------------+-----------------+
| Itf_uid | Itf_MifIndex | Itf_MifName | Itf_MifDescr     | Itf_IsPartOf    |
+---------+--------------+-------------+------------------+-----------------+
|  229435 |        10024 | Fa0/24      | FastEthernet0/24 | mgth03|
+---------+--------------+-------------+------------------+-----------------+
1 row in set (0.00 sec)

Code :

1
2
3
4
SELECT Itf_uid,Itf_MifIndex,Itf_MifName,Itf_MifDescr,Itf_IsPartOf 
FROM Itf 
WHERE Itf_IsPartOf = "mgth99" 
AND Itf_MifIndex = 10001 ;

+---------+--------------+-------------+-----------------+-----------------+
| Itf_uid | Itf_MifIndex | Itf_MifName | Itf_MifDescr    | Itf_IsPartOf    |
+---------+--------------+-------------+-----------------+-----------------+
|  229412 |        10001 | Fa0/1       | FastEthernet0/1 | mgth99 |
+---------+--------------+-------------+-----------------+-----------------+
1 row in set (0.01 sec)

[ced]

Bonjour,

Une petite jointure ne peut-elle pas suffire ? Pourquoi vouloir imbriquer ?

Code :

1
2
3
4
SELECT NACPort_IsPartOf, NACPort_MifIndex, Itf_MifDescr
FROM NACPort p
INNER JOIN Itf i ON p.NACPort_MifIndex = i.Itf_MifIndex AND p.NACPort_IsPartOf = i.Itf_IsPartOf
WHERE NACPort_IsLearn = 1;

[jur466]

Bon, après moultes essais, cette requêtes semble donner le bon résultat :

Code :

1
2
3
4
5
SELECT Itf_MifName , Itf_MifDescr, Itf_IsPartOf 
FROM Itf,NACPort 
WHERE NACPort_IsLearn = 1 
AND Itf_IsPartOf = NACPort_IsPartOf 
AND Itf_MifIndex = NACPort_MifIndex ;

+-------------+------------------+-----------------+
| Itf_MifName | Itf_MifDescr     | Itf_IsPartOf    |
+-------------+------------------+-----------------+
| Fa0/24      | FastEthernet0/24 | mgth03 |
| Fa0/1       | FastEthernet0/1  | mgth99|
+-------------+------------------+-----------------+
2 rows in set (0.00 sec)

[ced]

Euh, j'voudrais pas avoir l'air, mais j'ai écrit exactement la même chose, avec des jointures normalisée, ce qui est préférable .

?

[jur466]

Envoyé par ced

Euh, j'voudrais pas avoir l'air, mais j'ai écrit exactement la même chose, avec des jointures normalisée, ce qui est préférable .

?

Désolé, j'étais reparti dans mes requêtes et n'avais pas vu votre réponse.
Merci en tout cas.
JM