Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    53

    Unanswered: joining table- please help...

    i'm stuck with this problem since last 2 weeks. i really don't know how to solve it. i have tb_aduan which have 2 relationship with othe 2 table that is tbstudent and staffIT. what i want to do is i want to retrieve all info from tb aduan and view it. my problem is in tb_aduan there is a field named id_user where tb_aduan connected with those 2 table that i mention above. now the system will have to check first whether those id_user came from which table because i want to view the names where i will get either from tbstudent or staffIT. i have done INNER JOIN but it always give me error. yesterday i try the coding below:

    Dim Recordset1__MMColParam
    Recordset1__MMColParam = "1"
    If (Request.QueryString("kemaskini2") <> "") Then
    Recordset1__MMColParam = Request.QueryString("kemaskini2")
    End If
    %>
    <%
    Dim str
    Dim nama
    Dim rs
    Dim Recordset1
    Dim Recordset1_numRows

    Set Recordset1 = Server.CreateObject("ADODB.Recordset")
    Recordset1.ActiveConnection = MM_sambungan_STRING
    Recordset1.Source = "SELECT * FROM tb_aduan WHERE id_aduan=" + Replace(Recordset1__MMColParam, "'", "''") + ""
    Recordset1.CursorType = 0
    Recordset1.CursorLocation = 2
    Recordset1.LockType = 1
    Recordset1.Open()

    if (Recordset1.Fields.Item("id_user"))>"90000000" then
    str="SELECT * From tbstudent where tbstudent.pelNoPelajar= '" & Recordset1.Fields.Item("id_user")& "' "
    nama=str("pelNamaPelajar")
    else
    str="SELECT * From staffIT where staffIT.nostaffuitm='" & Recordset1.Fields.Item("id_user")& "' "
    nama=str("namastaffuitm")
    end if

    Recordset1_numRows = 0
    %>

    the if statement is correct. i've check it. my problem now is how i want to view those info in tbstudent or staffIT

    i really need help on this because i've been stuck with this problem and i'm now out of ideas

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    you are going to have a problem here
    Code:
    if (Recordset1.Fields.Item("id_user"))>"90000000" then
    try
    Code:
    if (Clng(Recordset1.Fields.Item("id_user")))>90000000 then
    I assume these lines
    Code:
    nama=str("pelNamaPelajar")
    nama=str("namastaffuitm")
    are meant to be using the value you got from your second query.... but you haven't done the second query yet, just written the sql statement...
    try
    Code:
    if (Recordset1.Fields.Item("id_user"))>"90000000" then
    str="SELECT pelNamaPelajar From tbstudent where tbstudent.pelNoPelajar= '" & Recordset1.Fields.Item("id_user")& "' "
    else
    str="SELECT namastaffuitm From staffIT where staffIT.nostaffuitm='" & Recordset1.Fields.Item("id_user")& "' "
    end if
    Set Recordset2 = Server.CreateObject("ADODB.Recordset")
    Recordset2.ActiveConnection = MM_sambungan_STRING
    Recordset2.Source = str
    Recordset2.CursorType = 0
    Recordset2.CursorLocation = 2
    Recordset2.LockType = 1
    Recordset2.Open()
    if not recordset2.eof then
      nama = recordset2(0).value 
    else
      nama = "no matching record"
    end if

  3. #3
    Join Date
    Mar 2004
    Posts
    53

    cannot work

    the suggestion that u gave still cannot work. is there any other way to do this.please help me

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    when you say "cannot work" you are not really giving me a lot of information to help you. what problems/errors are you encountering?

    Perhaps you could attach the whole asp page and I could look over it for you.

  5. #5
    Join Date
    Jan 2003
    Location
    Scotland
    Posts
    17
    What kind of DB are you using? I had a problem with an inner join in VB with Oracle. I hunted and hunted until someone told me Oracle8i does not support inner joins. Basically I binned the idea and wrote the SQL another way. What is the error you are getting?

Posting Permissions

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