Hi, I am an absolute beginner with sql and could really do with some help with Many to Many relationships. I have a fair knowledge of access and have achieved the results i need there but now need to figure it out for sql server.

I have 3 many to many relationships which i have connected with a joining table which contains the keys of the two tables they connect.

In access to make things easier i have created a union query to get all the data in one place, so column 1 is the cameraID, Column two are the various other tables.

1 have then created another query to which i have added the union query and the camera table. By using the count cameraID function i now get 1 record for each camera instead of the multiple results for each camera because of the entrys in the other tables. I can then search for for which camera has the correct criteria by entering a search in the union query.

I hope this makes sense. Basically i want to be able to search 3 tables connected to a table called cameras via a many to many relation ship but only receive one occurrence of each record in the camera table.

Hope you can help or at least point me in the right direction to find more info. Thanks in advance.Jon

Hi,Ok so i am learning quite a lot tonight, i have been using the query builder in sql express and have inserted the main table (tblcameras) and the two link tables. When i select two fields from cameras and choose to group by tblCamera.ID i get the correct number of records that are in that table. If i add a field from one of the other linked tables i get duplicates until i enter a search criteria.

So i figure that if i want to do a search i need to add the field i need to search, add the search criteria and display the results. Then before doing another search that field needs to be removed.

Is this possible? and i thinking along the right lines?

The SQL i have at the moment without adding in the search criteria is as follows:

You can build WHERE conditions as follows:
DECLARE @Condition1 INT, Condition2 INT;
SELECT *
FROM TableName
WHERE (SearchCriteria1 = @Condition1 OR @Condition1 IS Null)
(SearchCriteria2 = @Condition2 OR @Condition2 IS Null)

You can build WHERE conditions as follows:
DECLARE @Condition1 INT, Condition2 INT;
SELECT *
FROM TableName
WHERE (SearchCriteria1 = @Condition1 OR @Condition1 IS Null)
(SearchCriteria2 = @Condition2 OR @Condition2 IS Null)

You can build WHERE conditions as follows:
DECLARE @Condition1 INT, Condition2 INT;
SELECT *
FROM TableName
WHERE (SearchCriteria1 = @Condition1 OR @Condition1 IS Null)
(SearchCriteria2 = @Condition2 OR @Condition2 IS Null)