SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query several joining tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBRTaylor
Starting Member

United Kingdom
5 Posts

Posted - 05/26/2013 :  17:12:30  Show Profile  Reply with Quote
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

United Kingdom
5 Posts

Posted - 05/26/2013 :  18:33:07  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/27/2013 :  00:11:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/27/2013 :  01:24:45  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 05/27/2013 :  01:27:57  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/27/2013 :  01:41:30  Show Profile  Reply with Quote
No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000