explain T-SQL query to me

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%'

troyvwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
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
Rajkumar GsSoftware EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rajeshprasathCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Maverick543Commented:
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
khairilCommented:

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
khairilCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.