Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

explain T-SQL query to me

Posted on 2010-08-18
6
Medium Priority
?
617 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33466143
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 668 total points
ID: 33466170
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 668 total points
ID: 33466189
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:Maverick543
ID: 33466191
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 664 total points
ID: 33467082

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
ID: 33467163
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

670 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