Complex SQL Query

[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.com')
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.

[amitpatel66]

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.com')
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:

 
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}'
 
 

[hisabir]

Try this:

 
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}'
 
 

thanks amitpatel66, this help me a lot

[hisabir]

Try this:

 
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}'
 
 

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

[amitpatel66]

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:

 
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}'