Complex SQL Query

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • hisabir
    New Member
    • Dec 2006
    • 3
    #1

    Complex SQL Query

    hi,
    here is the structure of db

    CREATE TABLE [LOCATION] (
    [Id] VARCHAR(38),
    [ParentId] VARCHAR(38),
    [Name] VARCHAR(50))
    INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
    VALUES ('{000E12CF-0C0A-4991-8A38-6C1B1EA28A56}', '{35542804-DD2A-45EC-B7C9-1E3F358FEA23}', 'Singapore')
    GO

    INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
    VALUES ('{006C9BE7-E711-4DCB-8141-782A36F6DEFC}', '{A3BB1EBD-D17D-4465-BB9A-B3622E826707}', 'Nevada')
    GO

    INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
    VALUES ('{00B46FFB-091F-4DF2-8635-67056E60EC2D}', '{18EACAAA-178B-47BB-80C3-F4438ED490F6}', 'Qatar')
    GO
    and go on...
    CREATE TABLE [SECTOR] (
    [ID] VARCHAR(38),
    [ParentId] VARCHAR(38),
    [Name] VARCHAR(255))
    GO
    INSERT INTO [SECTOR] ([ID], [ParentId], [Name])
    VALUES ('{04DD96C5-F838-49EA-8F32-610B926C6E9C}', '{B934848B-F2E0-4BCB-809C-65B312FAC066}', 'Library')
    GO

    INSERT INTO [SECTOR] ([ID], [ParentId], [Name])
    VALUES ('{0636DAEF-8055-43E3-9053-4BF59E818829}', '{423ED5A4-5499-4115-8519-6D6D76FD084C}', 'Other Engineering')
    GO
    and ...

    CREATE TABLE [Website] (
    [ID] VARCHAR(38),
    [HomePage] VARCHAR(512))
    GO
    INSERT INTO [Website] ([ID], [HomePage])
    VALUES ('{16D04933-52D2-45F7-9FCB-1A1A7F5EB253}', 'www.yahoo.com' )
    GO

    INSERT INTO [Website] ([ID], [HomePage])
    VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', 'www.hotmail.co m')
    GO
    ....

    CREATE TABLE [Web2Location] (
    [Web_ID] VARCHAR(38),
    [Location_ID] VARCHAR(38))
    GO

    INSERT INTO [Web2Location] ([Web_ID], [Location_ID])
    VALUES ('{16D04933-52D2-45F7-9FCB-1A1A7F5EB253}', '{1B79459C-371D-4472-BBCF-578B6C950463}')
    GO

    INSERT INTO [Web2Location] ([Web_ID], [Location_ID])
    VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{70476DF7-B4B8-417E-ACE8-735CEFB71197}')
    GO
    ...

    CREATE TABLE [Web2Sector] (
    [Web_ID] VARCHAR(38),
    [Sector_ID] VARCHAR(38))
    GO
    INSERT INTO [Web2Sector] ([Web_ID], [Sector_ID])
    VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{0C020B48-580C-4389-B4E3-2EDBD88A4877}')
    GO

    INSERT INTO [Web2Sector] ([Web_ID], [Sector_ID])
    VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{12150FF7-5323-4F26-8EE3-BBF25110C48D}')
    GO

    CREATE TABLE [WebAccount] (
    [ID] VARCHAR(38),
    [Web_ID] VARCHAR(38),
    [Login] VARCHAR(255),
    [Pass] VARCHAR(255))
    GO
    INSERT INTO [WebAccount] ([ID], [Web_ID], [Login], [Pass])
    VALUES ('{00BB7207-1EB1-497E-9FFA-446531FA97CB}', '{817011C3-4E7F-4BD7-B2A6-B5C057A4A7C4}', 'Usman', 'pass')
    GO

    INSERT INTO [WebAccount] ([ID], [Web_ID], [Login], [Pass])
    VALUES ('{4199B81B-AC1E-4DA9-9C5E-25482DDB3840}', '{26D04433-52D2-45F7-9FCB-1A1A7F5EB359}', 'test', 'test')
    GO
    =============== ==End of query portion======== ============

    Now what I want is, I have location and sector IDs and I want a list/records from WebAccount table that are relevent to given location and sector IDs matching from Web2Location and Web2Sector tables.

    Please let me know if it make sense and thanks in advance.
    Tags: None
    • amitpatel66
      Recognized Expert Top Contributor
      • Mar 2007
      • 2358
      #2
      Originally posted by hisabir
      hi,
      here is the structure of db

      CREATE TABLE [LOCATION] (
      [Id] VARCHAR(38),
      [ParentId] VARCHAR(38),
      [Name] VARCHAR(50))
      INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
      VALUES ('{000E12CF-0C0A-4991-8A38-6C1B1EA28A56}', '{35542804-DD2A-45EC-B7C9-1E3F358FEA23}', 'Singapore')
      GO

      INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
      VALUES ('{006C9BE7-E711-4DCB-8141-782A36F6DEFC}', '{A3BB1EBD-D17D-4465-BB9A-B3622E826707}', 'Nevada')
      GO

      INSERT INTO [LOCATION] ([Id], [ParentId], [Name])
      VALUES ('{00B46FFB-091F-4DF2-8635-67056E60EC2D}', '{18EACAAA-178B-47BB-80C3-F4438ED490F6}', 'Qatar')
      GO
      and go on...
      CREATE TABLE [SECTOR] (
      [ID] VARCHAR(38),
      [ParentId] VARCHAR(38),
      [Name] VARCHAR(255))
      GO
      INSERT INTO [SECTOR] ([ID], [ParentId], [Name])
      VALUES ('{04DD96C5-F838-49EA-8F32-610B926C6E9C}', '{B934848B-F2E0-4BCB-809C-65B312FAC066}', 'Library')
      GO

      INSERT INTO [SECTOR] ([ID], [ParentId], [Name])
      VALUES ('{0636DAEF-8055-43E3-9053-4BF59E818829}', '{423ED5A4-5499-4115-8519-6D6D76FD084C}', 'Other Engineering')
      GO
      and ...

      CREATE TABLE [Website] (
      [ID] VARCHAR(38),
      [HomePage] VARCHAR(512))
      GO
      INSERT INTO [Website] ([ID], [HomePage])
      VALUES ('{16D04933-52D2-45F7-9FCB-1A1A7F5EB253}', 'www.yahoo.com' )
      GO

      INSERT INTO [Website] ([ID], [HomePage])
      VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', 'www.hotmail.co m')
      GO
      ....

      CREATE TABLE [Web2Location] (
      [Web_ID] VARCHAR(38),
      [Location_ID] VARCHAR(38))
      GO

      INSERT INTO [Web2Location] ([Web_ID], [Location_ID])
      VALUES ('{16D04933-52D2-45F7-9FCB-1A1A7F5EB253}', '{1B79459C-371D-4472-BBCF-578B6C950463}')
      GO

      INSERT INTO [Web2Location] ([Web_ID], [Location_ID])
      VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{70476DF7-B4B8-417E-ACE8-735CEFB71197}')
      GO
      ...

      CREATE TABLE [Web2Sector] (
      [Web_ID] VARCHAR(38),
      [Sector_ID] VARCHAR(38))
      GO
      INSERT INTO [Web2Sector] ([Web_ID], [Sector_ID])
      VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{0C020B48-580C-4389-B4E3-2EDBD88A4877}')
      GO

      INSERT INTO [Web2Sector] ([Web_ID], [Sector_ID])
      VALUES ('{26D04433-52D2-45F7-9FCB-1A1A7F5EB123}', '{12150FF7-5323-4F26-8EE3-BBF25110C48D}')
      GO

      CREATE TABLE [WebAccount] (
      [ID] VARCHAR(38),
      [Web_ID] VARCHAR(38),
      [Login] VARCHAR(255),
      [Pass] VARCHAR(255))
      GO
      INSERT INTO [WebAccount] ([ID], [Web_ID], [Login], [Pass])
      VALUES ('{00BB7207-1EB1-497E-9FFA-446531FA97CB}', '{817011C3-4E7F-4BD7-B2A6-B5C057A4A7C4}', 'Usman', 'pass')
      GO

      INSERT INTO [WebAccount] ([ID], [Web_ID], [Login], [Pass])
      VALUES ('{4199B81B-AC1E-4DA9-9C5E-25482DDB3840}', '{26D04433-52D2-45F7-9FCB-1A1A7F5EB359}', 'test', 'test')
      GO
      =============== ==End of query portion======== ============

      Now what I want is, I have location and sector IDs and I want a list/records from WebAccount table that are relevent to given location and sector IDs matching from Web2Location and Web2Sector tables.

      Please let me know if it make sense and thanks in advance.
      Try this:

      [code=sql]

      SELECT a.* FROM WebAccount a, Web2Location l
      WHERE a.web_id=l.web_ id
      AND l.location_id = '{70476DF7-B4B8-417E-ACE8-735CEFB71197}'
      UNION ALL
      SELECT a.* FROM WebAccount a, Web2Sector S
      WHERE a.web_id=s.web_ id
      AND s.sector_id = '{12150FF7-5323-4F26-8EE3-BBF25110C48D}'

      [/code]

        Comment

        • hisabir
          New Member
          • Dec 2006
          • 3
          #3
          Originally posted by amitpatel66
          Try this:

          [code=sql]

          SELECT a.* FROM WebAccount a, Web2Location l
          WHERE a.web_id=l.web_ id
          AND l.location_id = '{70476DF7-B4B8-417E-ACE8-735CEFB71197}'
          UNION ALL
          SELECT a.* FROM WebAccount a, Web2Sector S
          WHERE a.web_id=s.web_ id
          AND s.sector_id = '{12150FF7-5323-4F26-8EE3-BBF25110C48D}'

          [/code]
          thanks amitpatel66, this help me a lot

            Comment

            • hisabir
              New Member
              • Dec 2006
              • 3
              #4
              Originally posted by amitpatel66
              Try this:

              [code=sql]

              SELECT a.* FROM WebAccount a, Web2Location l
              WHERE a.web_id=l.web_ id
              AND l.location_id = '{70476DF7-B4B8-417E-ACE8-735CEFB71197}'
              UNION ALL
              SELECT a.* FROM WebAccount a, Web2Sector S
              WHERE a.web_id=s.web_ id
              AND s.sector_id = '{12150FF7-5323-4F26-8EE3-BBF25110C48D}'

              [/code]
              hi once again, I was going to implement this when i realize it doesn't solve the issue. how? lets see
              suppose i select location India and sector IT(IDs) so it means query should return only accounts of sector IT that are located in India. But as it is now, it returns all accounts from IT sector located anywhere and combine them with India location accounts.
              will you plz help me to solve this issue

                Comment

                • amitpatel66
                  Recognized Expert Top Contributor
                  • Mar 2007
                  • 2358
                  #5
                  Originally posted by hisabir
                  hi once again, I was going to implement this when i realize it doesn't solve the issue. how? lets see
                  suppose i select location India and sector IT(IDs) so it means query should return only accounts of sector IT that are located in India. But as it is now, it returns all accounts from IT sector located anywhere and combine them with India location accounts.
                  will you plz help me to solve this issue
                  Tryh this:

                  [code=sql]

                  SELECT a.* FROM WebAccount a, Web2Location l,Web2Sector S
                  WHERE a.web_id=l.web_ id
                  AND a.web_id = s.web_id
                  AND l.location_id = '{70476DF7-B4B8-417E-ACE8-735CEFB71197}'
                  AND s.sector_id = '{12150FF7-5323-4F26-8EE3-BBF25110C48D}'

                  [/code]

                    Comment

                    Previous template Next
                    Working...