Pants @ SQL

[AndyApp]

Hi

Can someone help me please. My SQL isn't up to scratch. I'm having to bring back data from a number of tables that are linked via IDs etc.

The main table 'Profiles' is linked to 'RegionChosen'. There can be many regions to one profile. There's also a 'FieldChosen' table which is also linked to 'Profiles' and can have many to one.

When I'm getting the results back it's bringing back an entry from Profiles for every entry they've made in 'RegionChosen' and 'FieldChosen'. I've tried LEFT OUTER JOINS, RIGHT OUTER JOINS, FULL JOINS but to no avail.

So if for one profile a user has chosen 10 fields and 5 regions it's bringing back that profile 10 times in the results along with the rest. I've also tried DISTINCT but that didn't work either.

 

[bborissov]

Can you post some data and desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[AndyApp]

here's the SQL i've currently got:

SELECT Profiles.ID, Profiles.CompName, Profiles.CompOutline, Profiles.CountryID, lkp_Country.ID AS CountryID, lkp_Region.CountryID AS RegionCountryID, lkp_Region.ID AS RegionID, FieldChosen.ProfileID, FieldChosen.TrainerRefinedJoinID, lkp_TrainerRefinedJoin.ID AS TrainerRefinedJoinID, lkp_TrainerRefinedJoin.TrainerCoreID, lkp_TrainerRefinedJoin.TrainerRefinedID, Users.ListingType FROM Profiles INNER JOIN lkp_Country ON Profiles.CountryID = lkp_Country.ID INNER JOIN lkp_Region ON lkp_Country.ID = lkp_Region.CountryID INNER JOIN FieldChosen ON Profiles.ID = FieldChosen.ProfileID INNER JOIN lkp_TrainerRefinedJoin ON FieldChosen.TrainerRefinedJoinID = lkp_TrainerRefinedJoin.ID INNER JOIN Users ON Profiles.UserID = Users.ID WHERE (Profiles.Live = 1) AND (lkp_TrainerRefinedJoin.TrainerRefinedID = " & intSubSubject & ") AND (lkp_TrainerRefinedJoin.TrainerCoreID = " & intSubject & ") AND (Profiles.CountryID = " & intCountry & ") AND (Users.ListingType = 2 OR Users.ListingType = 1) AND (lkp_Region.ID = " & intRegion & ") ORDER BY Users.ListingType DESC, NEWID()

Depending on what the user chooses this can return up to 300 records which is a pain when there's only about 15 profiles in the DB!