How do I ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I ...

Author

Topic

Johnny1985
Starting Member

26 Posts

Posted - 2008-09-06 : 05:58:46

Hello !

-----------
Ports table
-----------
SwitchID
PortID
PortName
VLAN_ID
VLAN_Name

-----------
VLANs table
-----------
SwitchID
VLAN_ID
VLAN_Name

I have a stored procedure that accepts @switchID and @portID in order to retrieve results from Ports table. The problem is that I need to get the VLAN_Name from the VLANs table also (without creating a new @vlanID variable of course). I've tried INNER JOIN on SwitchID but it then returned also the VLAN_Names which don't belong to the port.

I need to have the port's VLAN_ID seperated so that I can query the VLANs table to get the right record.
Therefore I was wondering if there's a way to seperate the SP into two:
1) Will get the port record based on the variables given,
2) Will get the VLAN record based on the 1)'s result set VLAN_ID field.

I would really appreciate any help what-so-ever .
Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-06 : 07:04:09

Why are you storing the VLAN_Name in the ports table? It's VLans table as well. Assuming the VLAN_ID is the primary key of the VLANs table, that's what should appear in the Ports table, with a appropriate foreign key.

Likewise, why's the Swithc_ID in both tables. Unless it's a foreign key (in which case the VLANID isn't) it should only be in one of those tables.

Assuming that the VLAN_ID is the foreign key between the two tables (which is in no way clear from what you said) this should get what you want.

SELECT V.VLAN_Name, p.* FROM Ports p inner join VLANs V ON p.VLAN_ID = V.VLAN_ID

--
Gail Shaw
SQL Server MVP

Johnny1985
Starting Member

26 Posts

Posted - 2008-09-06 : 07:20:02

Oh sorry, you're right ..
Let me rephrase:

-----------
Ports table
-----------
SwitchID Primary Key 1
PortID Primary Key 2
PortName
VLAN_ID

-----------
VLANs table
-----------
SwitchID Primary Key 1
VLAN_ID Primary Key 2
VLAN_Name

This design was due to the fact that there may be more than one switch and each one might have the same VLAN_ID or PortID, for example:

SwitchID PortID PortName VLAN
1 1 Fa 1/1 2
2 3 Fa 1/3 2

SwitchID VLAN VLAN_Name
1 2 RB Pov
2 2 Test

I want to have a result like:
SwitchID PortID PortName VLAN VLAN_Name

Thanks for helping out !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-06 : 23:44:16

[code]SELECT p.SwitchID, p.PortID, p.PortName, v.VLAN, v.VLAN_Name
FROM Ports p
INNER JOIN VLANs v
ON v.SwitchID=p.SwitchID
AND v.VLAN=p.VLAN [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources