Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: Select from 3 tables to display unique data for each particular Case No

    Hi Friends,
    I have 3 tables from which i have to display a set of records, for one particular attribute .say : Case no.
    But when i am writing the select statement, all the rows of that particular Case no are retrieved. and i am unable to repair the query, acc to my needs.
    ---------------------------------------------------------
    my tables are
    1. CASEFILING table - from where COMPLAINANT , RESPONDENT,COMPL_ADV,RESP_ADV to retrieve
    2. COURTROOM table - from where CASESTAGEID to be retrieved
    3. CASESTAGE table- from where CASESTAGE to be retrieved for a specific CASESTAGEID (from COURTROOM table)

    CASEFILING , COURTROOM table have pkey as CASE_ID_IN
    ---------------------------------------------------------

    Another thing, in COURTROOM table, there can be many records wrt only one CASE_ID_IN .But they have different HEARINGNO, which could be 1 ,2 .....and so on.

    So while retrieving the data, i was using MAX(HEARINGNO).
    But still i can retirive only one row, for only one caseno. and no result, for other case nos.

    Pls Help
    Thanks in advance
    royz4u

  2. #2
    Join Date
    Apr 2008
    Posts
    29
    could you paste your select statement, please ?

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    SELECT "Tax"."COURTROOM"."CASE_ID_IN","Tax"."CASEFILING". "COMPLAINANT","Tax"."CASEFILING"."COMPLNT_ADVOCATE ",
    "Tax"."CASEFILING"."RESPONDENT","Tax"."CASEFILING" ."RESPONDT_ADVOCATE","Tax"."CASEFILING"."DTOFFILIN G",
    "Tax"."COURTROOM"."DTOFHEARING ,"Tax"."COURTROOM"."CASESTAGEID"
    FROM "Tax"."CASEFILING" INNER JOIN "Tax"."COURTROOM" ON "Tax"."CASEFILING"."CASE_ID_IN"="Tax"."COURTROOM". "CASE_ID_IN"



    This is my select statement, if u want i can send the table structure ,along with few dummy data as well

    Thankz
    royz4u

  4. #4
    Join Date
    Apr 2008
    Posts
    29
    Quote Originally Posted by royz4u
    SELECT "Tax"."COURTROOM"."CASE_ID_IN","Tax"."CASEFILING". "COMPLAINANT","Tax"."CASEFILING"."COMPLNT_ADVOCATE ",
    "Tax"."CASEFILING"."RESPONDENT","Tax"."CASEFILING" ."RESPONDT_ADVOCATE","Tax"."CASEFILING"."DTOFFILIN G",
    "Tax"."COURTROOM"."DTOFHEARING ,"Tax"."COURTROOM"."CASESTAGEID"
    FROM "Tax"."CASEFILING" INNER JOIN "Tax"."COURTROOM" ON "Tax"."CASEFILING"."CASE_ID_IN"="Tax"."COURTROOM". "CASE_ID_IN"



    This is my select statement, if u want i can send the table structure ,along with few dummy data as well

    Thankz
    royz4u
    Have you tried adding "WHERE Tax.CASEFILING.CASE_ID_IN = <Your_case_no>" to that query ?

  5. #5
    Join Date
    Nov 2006
    Posts
    82
    show example table

Posting Permissions

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