Solved

explain T-SQL query to me

Posted on 2010-08-18
6
558 Views
Last Modified: 2012-05-10
Can somebody explain this SQL query to me? what is it doing?

DECLARE @CollectionID int      

SELECT @CollectionID= C2.CollectionID
FROM Collections C1
      INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
WHERE C1.Title= '09-0274'
      --AND C1.CompanyID= 1 and C2.Title LIKE 'Vendor-'  + '240635'
      AND C1.CompanyID= 1 and C2.Title LIKE '%240634%'

0
Comment
Question by:troyvw
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
Comment Utility
This query will return the CollectionID from Collections table satisfying the following criteria
1. 'Title' column data should contain '240634'
2. Its parent Collection's CompanyID should be 1
3. Its parent Collection's Title should be '09-0274'

Clear ?
Raj
0
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 167 total points
Comment Utility
This type of JOIN is called 'Self Join' - Joining a table with itself.
http://msdn.microsoft.com/en-us/library/ms177490.aspx

In your case, 'ParentID' column is JOINed to 'CollectionID' column of the same table.

Raj
0
 
LVL 2

Assisted Solution

by:rajeshprasath
rajeshprasath earned 167 total points
Comment Utility
DECLARE @CollectionID int

This statement declares the variable CollectionID.

SELECT @CollectionID= C2.CollectionID
FROM Collections C1
      INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
WHERE C1.Title= '09-0274'
      --AND C1.CompanyID= 1 and C2.Title LIKE 'Vendor-'  + '240635'
      AND C1.CompanyID= 1 and C2.Title LIKE '%240634%'

Then from the above query @CollectionID varible is used to store the CollectionID column of the Collection table.

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records.

This query is used to fetch the collectionids of each partner.

http://databases.about.com/od/sql/a/selfjoins.htm
http://sqlservercodebook.blogspot.com/2008/03/how-to-use-self-join-in-sql-server-2000.html
http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Expert Comment

by:Maverick543
Comment Utility
Here it goes.
The inner join connects collection C1 and C2 on the field ParentID of C2 and the field CollectionID of C1.
It then selects all records from the joined collection where CompanyID = 1, Title (from C2) contains '2406234' somewhere in the field and the Title of C1 = '09-0274'
The line starting with the -- is just a comment.
The way the query is written it is assumed there will be only 1 return row. And of that row the content of the field CollectionID is stored in a local variable @CollectionID
It looks like this query is part of a large SQL script (or Stored Procedure), I am correct?
Regards
Marco
0
 
LVL 13

Assisted Solution

by:khairil
khairil earned 166 total points
Comment Utility

Hi,

let me break it up in line number. For more clear version, we will replace line 2 with select all columns, instead of select into variables.

1. DECLARE @CollectionID int      

2. SELECT C2.*
3. FROM Collections C1
4. INNER JOIN Collections C2 on C1.CollectionID = C2.ParentID
5. WHERE C1.Title= '09-0274'
6. --AND C1.CompanyID = 1 and C2.Title LIKE 'Vendor-'  + '240635'
7. AND C1.CompanyID = 1
8. and C2.Title LIKE '%240634%'
===================================================
Here is some sample data

Collections
=============================================
CollectionID  ParentID   Title      CompanyID
1                 0               AB2406341  1
2                  1          09-0274    1
3             1          09-0274    3
4             2          AD2406341  4
5             2          XF856332   5
6             3          QW85632    6


> Line 1. Declare a variable type integer

> Line 2. The real state here is "SELECT @CollectionID = C2.CollectionID", which mean put a value of CollectionID return by statements 3 - 7 into variable @CollectionID (value return must be in integer). Note; @CollectionID is not just 'a' value, but an array of value which each element represent by @CollectionID. Simply said @CollectionID have collection of values.

Line 3 & 4. Self joining a "Collections" table with another instance of "Collections" table with condition where first instance (C1) of "Collections" table's ColleciontID must be same with the second instance (C2) of "Collections" table's ParentID. Running sentance 1 - 4 on sample data will return result from instance of "Collections" tables C2 as below:

C2
============================================
CollectionID  ParentID  Title      CompanyID
2             1         09-0274    1
3             1         09-0274    3
4             2         AD2406341  4
5             2         XF856332   5
6             3         QW85632    6

First and second row return because there is CollectionID with value 1 in Collections tables.

Third and forth row return because there is CollectionID with value 2 in Collections table

Fith row return because there is CollectionID with value 3 in Collections table.


> Line 5. We add one more condition, we want instance of Collections table C1 to be more specific, which is only get data that have Title EQUAL to '09-0274'.

Running this statement alone:

SELECT C1.* FROM Collections C1 WHERE C1.Title = '09-0274'

Will return two rows, notice that CompanyID is different - means that it is not the same row:

C1
=============================================
CollectionID  ParentID   Title      CompanyID
2                  1          09-0274    1
3                  1          09-0274    3

Now combine with the first condition, we want ParentID in C2 to be equal to CollectionID in C1. Three rows from C2 fit this condition which are:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4
5             2         XF856332   5
6             3         QW85632    6

Both have ParentID of 2 & 3, which exist in C1 CollectionID.

> Line 6. Is commented by --. Anything commented will not effect the query.

> Line 7. We add one more condition, third condition. Beside of having C1's Title equal to '09-0274', we also want that it have CompanyID value 1.

Having condition, we can first simplify the statement as follow:

SELECT C1.* FROM Collections C1 WHERE C1.Title = '09-0274' AND C1.CompanyID = 1

Which will return only a row, which satisfy the company must have Title EQUAL to '09-0274' AND CompanyID EQUAL 1:
C1
=============================================
CollectionID  ParentID   Title      CompanyID
2                  1          09-0274    1

Now combine with the C2 result in Line 5 above. Compare the ParentID of C2 with the CollectionID of C1 return above. Only two rows return for C2, which have ParentID equal to 2, which equal to C1 CollectionID of 2. There is no C1 CollectionID equal to 3, so last row of C2 get rejected. Result of C2 now are:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4
5             2         XF856332   5


I hope you have get the clear picture now.

> Line 8. Forth condition. We want to filter C2 further more. We want only data that have '240634' inside it's Title. From result return by C2 above, only 1 row fulfill the condition:

C2
============================================
CollectionID  ParentID  Title      CompanyID
4             2         AD2406341  4

Only this line have '240634' in it's Title.

OK. Now return back to real statement - that want to us to return only CollectionID into @CollectionID variable. The result in this sample is an array of one:

@CollectionID
=============
4


*Hope this can make you understand better. Enjoy.


0
 
LVL 13

Expert Comment

by:khairil
Comment Utility
Here is an MS Access sample for you.

I have add one more column for ID (you can ignore it if you want to).
I use CollectionID column instead of variable here.

I also remove the commented statement and replace like state from '%240634%' to '*240634*', MS Access using * as %.


Collections.mdb
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now