How to explain a not very complex query to an SQL beginner

Posted on 2011-04-24
Medium Priority
Last Modified: 2012-05-11
Consider the following:

(item_id int(11),
 category_id int(11)

(category1_id int(11),
  category2_id int(11),
  relation varchar(20)

Then we have the query that uses item1_id and item2_id as inputs:

select cr.relation
from category_relations cr, item_categories ic1, item_categories ic2
where ic1.item_id=item1_id
and ic2.item_id=item2_id
and cr.category1_id=ic1.category_id
and cr.category2_id=ic2.category_id;

How would you best explain the need for calling item_categories twice to an SQL beginner?  Things like this has become pretty second-nature to me, but having to explain it in simple terms suddenly got me stumped.  Thanks.
Question by:johanntagle
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 35457873
It's not really clear from your query why you're linking to that table twice, unless you're just trying to validate the integrity of both sides of the relationship in the category_relations table.

If that's the case, then I'd just explain it like that - you have a row in the table representing a relationship, but you don't know what two categories its between (since you only have IDs) unless you link back to the item_categories table to get more information. Since you have two different relationships, you need to link to the categories table two different times - one for each side of the relationship.

Is that clear, or am I missing the point of what you're trying to explain?
LVL 24

Author Closing Comment

ID: 35457885
Clear enough!  Thanks!

Featured Post

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.

Question has a verified solution.

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

Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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