Query several joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query several joining tables

Author

Topic

JBRTaylor
Starting Member

5 Posts

Posted - 2013-05-26 : 17:12:30

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

JBRTaylor
Starting Member

5 Posts

Posted - 2013-05-26 : 18:33:07

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:

SELECT COUNT(dbo.tblCameras.ID) AS Expr1, dbo.tblCameras.Name
FROM dbo.tblCodecLink INNER JOIN
dbo.tblCodec ON dbo.tblCodecLink.CodecID = dbo.tblCodec.CodecId INNER JOIN
dbo.tblCameraUseLink INNER JOIN
dbo.tblCameraUse ON dbo.tblCameraUseLink.CameraUsetblID = dbo.tblCameraUse.CameraUseID INNER JOIN
dbo.tblCameras ON dbo.tblCameraUseLink.CameratblID = dbo.tblCameras.ID ON dbo.tblCodecLink.CameratblID = dbo.tblCameras.ID
GROUP BY dbo.tblCameras.Name

Hope you can help.
Jon

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 00:11:42

[code]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)[/code]

Refer Static SQL in this link
http://www.sommarskog.se/dyn-search-2008.html

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:24:45

quote:
Originally posted by bandi

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)

Refer Static SQL in this link
http://www.sommarskog.se/dyn-search-2008.html

--
Chandu

Whilst this may work well for small datasets beware that this might have performance implications for large datasets due to non optimal execution plans

see
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-27 : 01:27:57

quote:
Originally posted by visakh16

quote:
Originally posted by bandi

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)

Refer Static SQL in this link
http://www.sommarskog.se/dyn-search-2008.html

--
Chandu

Whilst this may work well for small datasets beware that this might have performance implications for large datasets due to non optimal execution plans
see
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

Yes visakh.. I know about that thing..
Thank for your advise

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:41:30

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources