linking 2 tables

dear experts,
i have two tables, tblcustomer and tblform. in tblcustomer i have cutomerid PK and i have also a field that link these two tables customername. in tblforms i have formname. customername and formname are not PK and customername has a lot of null values.
my question is that i want to link these two tables in customerid not the name and also to get the forms used by this customer. the thing is that i have to write a join query.
any suggestion to do that?

thanks my friendsplease show sample rows from each table|||in tblcustomer:
customerid PK
customername
address
frname
...
general information

in tblforms:
formid PK
formname
JS
CSS
...

now i want to link frname to formname so i can get the forms used by the user.
thanks|||sample rows? you know, so we can see the data?|||in tblcustomer:
customerid PK int
customername varchar
address varchar
frname varchar
...

in tblforms:
formid PK int
formname varchar
JS varchar
CSS varchar
...

i hope this is what you meant.
is there a way to link the table with the formid not with the formname
thanks|||no, that's not what i meant

here are sample rows --

customers
42, Fred Jones, 123 Sesame Street
43, Tom Smith, 456 Main Street

forms
1035, arwbtyms, n, y
1036, uwnctrsj, y, y

i still have absolutely no idea what you are trying to join|||customers
42, Fred Jones, 123 Sesame Street, tio

forms
1035, tio , n, y

this is the sample rows in my case.
now i want to get the the forms name user by a customer form tio in this example, it will be also nice if i can join these two tables with formid instead of formname.
i hope i make my self clear.
let me know if you have any question and thanks for spending time reading my posts.
thanks|||what i did is that i added a field in thbcustomer called formid but how can i force the relation between these two tables so i can join them on formid?
thanks|||alter table tblcustomer
add constraint this_is_the_link foreign key (formid) references tblform (formid)

by the way, you do not need to force a relation in order to be able to join tables

you can join tables on any columns you wish, whether or not they have a relationship|||ok thanks for the hint, but can you show me the query that will link these two tables by formid so i will get the forms used by a customer?
thanks|||the forms used by a customer? are you suggesting that a customer can have multiple forms? if that is true, then adding formid to tblcustomer is wrong, this would limit each customer to one form|||yes i have too many forms and this is in case i want to see the forms used by a customer, that is why i need to link the tables with formid because a customer can have many forms.

to sum up i need a query that will allow me to see all the forms user by a customer.
how can i do that?
thanks man|||is it a one-to-many relationship or a many-to-many relationship?

can the same form be used by more than one customer?|||it is a one to many relationship because a customer can use just one form but the form can be used by many customers, i think my last phrase was wrong, actually i need to see all the clients using the form. the only firled i have in tblcustomer is frname and the field i have in tblforms is formname.
thanks|||do me a favour, please script out your tables|||CREATE TABLE [tblcustomer] (
[customerID] [int] NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DomainName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Prmform] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

) ON [PRIMARY]
GO

CREATE TABLE [tblforms] (
[formid] [int] IDENTITY (1, 1) NOT NULL ,
[formName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrameSet] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JavaScript] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

here is my script may be now it is clear.
thanks|||alter table tblforms
add constraint unique_name unique (formName)

alter table tblcustomer
add constraint form_link foreign key (Prmform) references tblforms (formName)
you might need to make them the same size VARCHAR|||thanks my friend for the help. but is there a way to link them by formId not formName?|||yes, you would have to add the formid column to the customers table, then make sure it contains appropriate values from the formid column in the forms table|||thanks my friend|||wonder if form is gettign confused for order?
A customer can have many orders, comprisded of many items, which should have one invoice for one order.|||in my case it is like a profile so no items there.
but i'm wondering, when i create the column in customer table how should i filled it with the right information because i already have some form names, i want to select the formid of this form name and insert it into formid in customer table.
any suggestions?
thanks|||I just wandered in from an adjacent universe/time continuum, but unless I missed something, you will need to simply find out which form is associated with each customer and then insert the appropriate formid into the new column in the customer table. At least for all your customer rows that don't have a NULL formname. For those, I guess you'll need to get on the phone and ask them which form they use...or just populate the associated formid with nulls (since you have no idea what formname OR formid they should have).

For the others it would be a simple join, eh? Something along the lines of UPDATE C
SET FormID = F.FormID
FROM dbo.tblcustomer C,
dbo.tblforms F
WHERE C.Prmform IS NOT NULL
AND C.Prmform = F.formNamesql

Linked SQL Server and Windows 2003 Firewall/SCWQL

SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
JohnI suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***

Linked SQL Server and Windows 2003 Firewall/SCWQL

SQL Experts,
1. I have two Windows 2003 Standard Server boxes.
2. Both host MS/SQL Server Standard 2000.
3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server applet.
4. Both servers have Win2k3 Service Pack#1 installed.
5. Both server enable firewall via SCW (Security Config. Wizard). I locked
everything but SQL Server Port (1433).
6. Both use TCP/IP as top (default) network protocol.
7. All Windows XP clients can read/write to and from the two SQL servers
without problem (Windows and SQL Auth. are used).
Problem: Server #2 cannot read data from Server#1 it links to. It said
Sever#1 is not running or access denied. Even if SQL Ports on both boxes are
widely openned. because clients from outside can get to it. Hopwever, if I
disable firewall on Server# 1, then Server# 2 will be able to read data from
#1. I really like the FW in service pack# 1. But if I have to disable it in
order to use "Link Server" (server-to-server) in MS/SQL then this new thing
(FW) in W2k3 is really useless ! Therefore I assume that I am missing
something very important ?
Thanks
John
I suspect it's the way you have your firewall setup, check the following:
INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/kb/287932/
Steve
"WJ" <JohnWebbs@.HotMail.Com> wrote in message
news:udrXXC%23OFHA.2252@.TK2MSFTNGP15.phx.gbl...
> SQL Experts,
> 1. I have two Windows 2003 Standard Server boxes.
> 2. Both host MS/SQL Server Standard 2000.
> 3. Server#2 is linked to Server#1 using SQL/Enterprised Link Server
applet.
> 4. Both servers have Win2k3 Service Pack#1 installed.
> 5. Both server enable firewall via SCW (Security Config. Wizard). I locked
> everything but SQL Server Port (1433).
> 6. Both use TCP/IP as top (default) network protocol.
> 7. All Windows XP clients can read/write to and from the two SQL servers
> without problem (Windows and SQL Auth. are used).
> Problem: Server #2 cannot read data from Server#1 it links to. It said
> Sever#1 is not running or access denied. Even if SQL Ports on both boxes
are
> widely openned. because clients from outside can get to it. Hopwever, if I
> disable firewall on Server# 1, then Server# 2 will be able to read data
from
> #1. I really like the FW in service pack# 1. But if I have to disable it
in
> order to use "Link Server" (server-to-server) in MS/SQL then this new
thing
> (FW) in W2k3 is really useless ! Therefore I assume that I am missing
> something very important ?
> Thanks
> John
>
|||Dear John,
Did you solve your linked server problem ? Now , i am suffer the same
problem. Do you got any solution ?
Thanks a lot
From Agnes
*** Sent via Developersdex http://www.codecomments.com ***