Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: 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 friends
    Peace out
    Mr joka

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show sample rows from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sample rows? you know, so we can see the data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  8. #8
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    is it a one-to-many relationship or a many-to-many relationship?

    can the same form be used by more than one customer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2006
    Posts
    55
    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
    Peace out
    Mr joka

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do me a favour, please script out your tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •