Loading ...
Sorry, an error occurred while loading the content.

urgent:) please help

Expand Messages
  • universe_is_in_me
    hi fellows! i have to make a medical diagnosis system as my java project:) i m using SQL as a back hand data base. now the initial tables which i have made
    Message 1 of 10 , Feb 17, 2004
    View Source
    • 0 Attachment
      hi fellows!

      i have to make a medical diagnosis system as my java project:)

      i'm using SQL as a back hand data base.

      now the initial tables which i have made are:

      1) Disease Table

      Attributes:
      1) Disease Name .
      2) Disease ID .


      2) Table # 2 is "Symptoms:"

      Attributes are:

      1) SymptomID .
      2) Symptom name .


      Question:

      now the problem is this that there is many to many relationship
      b/w Symptoms and diseases.

      i'm not understanding that what attributes i shall add or
      how i link the two tables together ..

      please kindly help me...................
      it's really urgent.

      best wishes and regards.
    • Richard Rogers
      you need to create a join table that contains the combinations of all diseases and symptoms. ... From: universe_is_in_me [mailto:universe_is_in_me@yahoo.com]
      Message 2 of 10 , Feb 17, 2004
      View Source
      • 0 Attachment
        Message
        you need to create a join table that contains the combinations of all diseases and symptoms. 
         
        -----Original Message-----
        From: universe_is_in_me [mailto:universe_is_in_me@...]
        Sent: Tuesday, February 17, 2004 12:04 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] urgent:) please help

        hi fellows!

        i have to make a medical diagnosis system as my java project:)

        i'm using SQL as a back hand data base.

        now the initial tables which i have made are:

        1) Disease Table

        Attributes:
        1) Disease Name .
        2) Disease ID .


        2) Table # 2 is "Symptoms:"

        Attributes are:

        1) SymptomID .
        2) Symptom name .


        Question:

        now the problem is this that there is many to many relationship
        b/w Symptoms and diseases.

        i'm not understanding that what attributes i shall add or
        how i link the two tables together ..

        please kindly help me...................
        it's really urgent.

        best wishes and regards.






        NOTICE: This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. The contents of this e-mail are confidential. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Please virus check all attachments to prevent widespread contamination and corruption of files and operating systems!
      • Ronda
        To elaborate a bit on that.. You will need a third table .. diseaseSymptons or something like that. In it you will have three fields ID DiseaseID SymptonID So
        Message 3 of 10 , Feb 17, 2004
        View Source
        • 0 Attachment
          Message

          To elaborate a bit on that….

           

          You will need a third table …. diseaseSymptons or something like that.  In it you will have three fields

           

          ID

          DiseaseID

          SymptonID

           

          So in your disease table you might have :

           

          1 Scoliosis

          2 Eczema

          3 Epilepsy

           

          In your symptoms table you might have

           

          1 Severe Headaches

          2 Spine Curvature

           

           

          In your third table you would have

          1 1 2

          2 1 1

          3 3 1

           

           

          That is assuming that you have spine curvature and severe headaches with Scoliosis (the first number is the unique ID of the diseaseSymptoms table)

           

          HTH

          Ronda

           

           

           

          -----Original Message-----
          From: Richard Rogers [mailto:rrogers@...]
          Sent: Tuesday, February 17, 2004 1:39 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: RE: [SQLQueriesNoCode] urgent:) please help

           

          you need to create a join table that contains the combinations of all diseases and symptoms. 

           

          -----Original Message-----
          From: universe_is_in_me [mailto:universe_is_in_me@...]
          Sent: Tuesday, February 17, 2004 12:04 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] urgent:) please help

          hi fellows!

          i have to make a medical diagnosis system as my java project:)

          i'm using SQL as a back hand data base.

          now the initial tables which i have made are:

          1) Disease Table

          Attributes:
          1) Disease Name .
          2) Disease ID .


          2) Table # 2 is "Symptoms:"

          Attributes are:

          1) SymptomID .
          2) Symptom name .


          Question:

          now the problem is this that there is many to many relationship
          b/w Symptoms and diseases.

          i'm not understanding that what attributes i shall add or
          how i link the two tables together ..

          please kindly help me...................
          it's really urgent.

          best wishes and regards.






          NOTICE: This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. The contents of this e-mail are confidential. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Please virus check all attachments to prevent widespread contamination and corruption of files and operating systems!

        • Michael Gerholdt
          The initial tables you have made should each be base tables, meaning unique ID on each disease in disease table, and each symptom in symptom table. Next make a
          Message 4 of 10 , Feb 17, 2004
          View Source
          • 0 Attachment
            The initial tables you have made should each be base tables, meaning unique
            ID on each disease in disease table, and each symptom in symptom table.

            Next make a third table which will be the table that joins them

            DiseaseSymptoms Table
            DiseaseID, SymptomID

            here you can have each DiseaseID many times, each SymptomID many times.



            > now the initial tables which i have made are:
            >
            > 1) Disease Table
            >
            > Attributes:
            > 1) Disease Name .
            > 2) Disease ID .
            >
            >
            > 2) Table # 2 is "Symptoms:"
            >
            > Attributes are:
            >
            > 1) SymptomID .
            > 2) Symptom name .
            >
          • John Warner
            Many to Many relationships have to have an intervening table Table 3: -name me meaningfully for your project Cols: Table3_Index (PK) -give me a meaningful name
            Message 5 of 10 , Feb 17, 2004
            View Source
            • 0 Attachment
              Many to Many relationships have to have an intervening table

              Table 3: -name me meaningfully for your project

              Cols:
              Table3_Index (PK) -give me a meaningful name
              SymptomID (FK)
              Disease ID (FK) -I would lose the space in this name in ALL tables


              Now table 3 can have many symptoms and many diseases tied together.

              Disease(s)
              Flu D1
              Cold D2

              Symptom(s)
              Fever S1
              Runny Nose S2


              Table 3
              1 D1 S1
              2 D1 S2
              3 D2 S2

              HTH

              John Warner
              mailto:john@...

              > -----Original Message-----
              > From: universe_is_in_me [mailto:universe_is_in_me@...]
              > Sent: Tuesday, February 17, 2004 1:04 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] urgent:) please help
              >
              >
              > hi fellows!
              >
              > i have to make a medical diagnosis system as my java project:)
              >
              > i'm using SQL as a back hand data base.
              >
              > now the initial tables which i have made are:
              >
              > 1) Disease Table
              >
              > Attributes:
              > 1) Disease Name .
              > 2) Disease ID .
              >
              >
              > 2) Table # 2 is "Symptoms:"
              >
              > Attributes are:
              >
              > 1) SymptomID .
              > 2) Symptom name .
              >
              >
              > Question:
              >
              > now the problem is this that there is many to many
              > relationship b/w Symptoms and diseases.
              >
              > i'm not understanding that what attributes i shall add or
              > how i link the two tables together ..
              >
              > please kindly help me...................
              > it's really urgent.
              >
              > best wishes and regards.
              >
              >
              >
              >
              >
              >
              >
              > ------------------------ Yahoo! Groups Sponsor
              > ---------------------~--> Buy Ink Cartridges or Refill Kits
              > for your HP, Epson, Canon or Lexmark Printer at MyInks.com.
              > Free s/h on orders $50 or more to the US & Canada.
              http://www.c1tracking.com/l.asp?cid=5511
              http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/m7folB/TM
              ---------------------------------------------------------------------~->


              Yahoo! Groups Links
            • C. Schmitz
              Hi all, from a many to many relationship I need to select all records from table 1 that match ALL records of table b. Here s my setup: tblRawMaterials
              Message 6 of 10 , Feb 18, 2004
              View Source
              • 0 Attachment
                Hi all,

                from a many to many relationship I need to select all records from table 1
                that match ALL records of table b. Here's my setup:

                tblRawMaterials (materialID int PK, material_name nvarchar(200))
                tblProperties (propertyID int PK, property_name nvarchar(200))
                tblRawMaterials_Properties_rel (ID int PK, materialID int, propertyID int)

                if I have 10 properties, I need to select only those raw materials that are
                related to all 10 properties, not those that match 1,3, or even 9
                properties... so a simple WHERE materialID IN () or WHERE EXISTS (SELECT
                materialID FROM tblRawMaterials_Properties_rel) won't do.

                Any ideas?

                TIA

                Chris
              • hansve2000
                Is relational division. Try something like so: SELECT MaterialID FROM tblRawMaterials_Properties_Rel GROUP BY MaterialID HAVING COUNT(DISTINCT PropertyID) =
                Message 7 of 10 , Feb 18, 2004
                View Source
                • 0 Attachment
                  Is relational division. Try something like so:

                  SELECT MaterialID
                  FROM tblRawMaterials_Properties_Rel
                  GROUP BY MaterialID
                  HAVING COUNT(DISTINCT PropertyID) =
                  (SELECT COUNT(*)
                  FROM tblProperties)

                  If you correctly make primary key or just UNIQUE constraint of
                  tblRawMaterials_Properties_Rel as (MaterialID,PropertyID) then above
                  DISTINCT option is not needed.

                  Hans.

                  --- In SQLQueriesNoCode@yahoogroups.com, "C. Schmitz" <info@p...>
                  wrote:
                  > Hi all,
                  >
                  > from a many to many relationship I need to select all records from
                  table 1
                  > that match ALL records of table b. Here's my setup:
                  >
                  > tblRawMaterials (materialID int PK, material_name nvarchar(200))
                  > tblProperties (propertyID int PK, property_name nvarchar(200))
                  > tblRawMaterials_Properties_rel (ID int PK, materialID int,
                  propertyID int)
                  >
                  > if I have 10 properties, I need to select only those raw materials
                  that are
                  > related to all 10 properties, not those that match 1,3, or even 9
                  > properties... so a simple WHERE materialID IN () or WHERE EXISTS
                  (SELECT
                  > materialID FROM tblRawMaterials_Properties_rel) won't do.
                  >
                  > Any ideas?
                  >
                  > TIA
                  >
                  > Chris
                • Daniel Wilson
                  What do you mean by related? For instance, do you have 10 properties and you want to find which material numbers have those 10 properties but no more or is
                  Message 8 of 10 , Feb 18, 2004
                  View Source
                  • 0 Attachment
                    What do you mean by related? For instance, do you have 10 properties
                    and you want to find which material numbers have those 10 properties but
                    no more or is more okay. Or are you trying to find similar materials
                    based on if 10 of their properties are the same.

                    Thanks,
                    Daniel

                    -----Original Message-----
                    From: C. Schmitz [mailto:info@...]
                    Sent: Wednesday, February 18, 2004 4:24 AM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: [SQLQueriesNoCode] SELECT problem


                    Hi all,

                    from a many to many relationship I need to select all records from table
                    1 that match ALL records of table b. Here's my setup:

                    tblRawMaterials (materialID int PK, material_name nvarchar(200))
                    tblProperties (propertyID int PK, property_name nvarchar(200))
                    tblRawMaterials_Properties_rel (ID int PK, materialID int, propertyID
                    int)

                    if I have 10 properties, I need to select only those raw materials that
                    are related to all 10 properties, not those that match 1,3, or even 9
                    properties... so a simple WHERE materialID IN () or WHERE EXISTS (SELECT
                    materialID FROM tblRawMaterials_Properties_rel) won't do.

                    Any ideas?

                    TIA

                    Chris






                    Yahoo! Groups Links







                    This message was scanned by McAfee Webshield.
                  • C. Schmitz
                    Hi Hans, thanks for your input, but, unfortunately, I can t use your solution. In my description I simplified the situation... actually both tblProperties and
                    Message 9 of 10 , Feb 20, 2004
                    View Source
                    • 0 Attachment
                      Hi Hans,

                      thanks for your input, but, unfortunately, I can't use your solution. In my
                      description I simplified the situation... actually both tblProperties and
                      tblRawMaterials_Properties_rel have more fields that need to match.
                      tblProperties is kind of a "user wish list" which properties he wants to
                      check. It also contains values. Now I have to list all RawMaterials that
                      match listed Properties with all given values. There might be RawMaterials
                      in tblRawMaterials_Properties_rel that match the property, but do not match
                      the values. So I have to check that, too. Any ideas??

                      Thank you

                      Chris


                      > -----Urspr√ľngliche Nachricht-----
                      > Von: hansve2000 [mailto:hansve2000@...]
                      > Gesendet: Mittwoch, 18. Februar 2004 14:37
                      > An: SQLQueriesNoCode@yahoogroups.com
                      > Betreff: [SQLQueriesNoCode] Re: SELECT problem
                      >
                      >
                      > Is relational division. Try something like so:
                      >
                      > SELECT MaterialID
                      > FROM tblRawMaterials_Properties_Rel
                      > GROUP BY MaterialID
                      > HAVING COUNT(DISTINCT PropertyID) =
                      > (SELECT COUNT(*)
                      > FROM tblProperties)
                      >
                      > If you correctly make primary key or just UNIQUE constraint of
                      > tblRawMaterials_Properties_Rel as (MaterialID,PropertyID) then above
                      > DISTINCT option is not needed.
                      >
                      > Hans.
                      >
                      > --- In SQLQueriesNoCode@yahoogroups.com, "C. Schmitz" <info@p...>
                      > wrote:
                      > > Hi all,
                      > >
                      > > from a many to many relationship I need to select all records from
                      > table 1
                      > > that match ALL records of table b. Here's my setup:
                      > >
                      > > tblRawMaterials (materialID int PK, material_name nvarchar(200))
                      > > tblProperties (propertyID int PK, property_name nvarchar(200))
                      > > tblRawMaterials_Properties_rel (ID int PK, materialID int,
                      > propertyID int)
                      > >
                      > > if I have 10 properties, I need to select only those raw materials
                      > that are
                      > > related to all 10 properties, not those that match 1,3, or even 9
                      > > properties... so a simple WHERE materialID IN () or WHERE EXISTS
                      > (SELECT
                      > > materialID FROM tblRawMaterials_Properties_rel) won't do.
                      > >
                      > > Any ideas?
                      > >
                      > > TIA
                      > >
                      > > Chris
                      >
                      >
                      >
                      >
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                    • hansve2000
                      I am sorry I do not understand your writing. You must give actual example to get actual answer: DDL, sample data and desired result set. You can use WHERE
                      Message 10 of 10 , Feb 20, 2004
                      View Source
                      • 0 Attachment
                        I am sorry I do not understand your writing. You must give actual
                        example to get actual answer: DDL, sample data and desired result
                        set. You can use WHERE clauses in both outer query and subquery and
                        you can correlate the 2 queries also.

                        Hans.

                        --- In SQLQueriesNoCode@yahoogroups.com, "C. Schmitz" <info@p...>
                        wrote:
                        > Hi Hans,
                        >
                        > thanks for your input, but, unfortunately, I can't use your
                        solution. In my
                        > description I simplified the situation... actually both
                        tblProperties and
                        > tblRawMaterials_Properties_rel have more fields that need to match.
                        > tblProperties is kind of a "user wish list" which properties he
                        wants to
                        > check. It also contains values. Now I have to list all
                        RawMaterials that
                        > match listed Properties with all given values. There might be
                        RawMaterials
                        > in tblRawMaterials_Properties_rel that match the property, but do
                        not match
                        > the values. So I have to check that, too. Any ideas??
                        >
                        > Thank you
                        >
                        > Chris
                        >
                        >
                        > > -----Urspr√ľngliche Nachricht-----
                        > > Von: hansve2000 [mailto:hansve2000@y...]
                        > > Gesendet: Mittwoch, 18. Februar 2004 14:37
                        > > An: SQLQueriesNoCode@yahoogroups.com
                        > > Betreff: [SQLQueriesNoCode] Re: SELECT problem
                        > >
                        > >
                        > > Is relational division. Try something like so:
                        > >
                        > > SELECT MaterialID
                        > > FROM tblRawMaterials_Properties_Rel
                        > > GROUP BY MaterialID
                        > > HAVING COUNT(DISTINCT PropertyID) =
                        > > (SELECT COUNT(*)
                        > > FROM tblProperties)
                        > >
                        > > If you correctly make primary key or just UNIQUE constraint of
                        > > tblRawMaterials_Properties_Rel as (MaterialID,PropertyID) then
                        above
                        > > DISTINCT option is not needed.
                        > >
                        > > Hans.
                        > >
                        > > --- In SQLQueriesNoCode@yahoogroups.com, "C. Schmitz" <info@p...>
                        > > wrote:
                        > > > Hi all,
                        > > >
                        > > > from a many to many relationship I need to select all records
                        from
                        > > table 1
                        > > > that match ALL records of table b. Here's my setup:
                        > > >
                        > > > tblRawMaterials (materialID int PK, material_name nvarchar
                        (200))
                        > > > tblProperties (propertyID int PK, property_name nvarchar(200))
                        > > > tblRawMaterials_Properties_rel (ID int PK, materialID int,
                        > > propertyID int)
                        > > >
                        > > > if I have 10 properties, I need to select only those raw
                        materials
                        > > that are
                        > > > related to all 10 properties, not those that match 1,3, or
                        even 9
                        > > > properties... so a simple WHERE materialID IN () or WHERE
                        EXISTS
                        > > (SELECT
                        > > > materialID FROM tblRawMaterials_Properties_rel) won't do.
                        > > >
                        > > > Any ideas?
                        > > >
                        > > > TIA
                        > > >
                        > > > Chris
                        > >
                        > >
                        > >
                        > >
                        > >
                        > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        > >
                        > >
                      Your message has been successfully submitted and would be delivered to recipients shortly.