Jump to content
Larry Ullman's Book Forums

Don'T Understand This Sql Statement


Recommended Posts

Around page 197, Larry starts explaining some of the SQL statements that will be used in the stored procedures, but there's one thing I don't get.

 

You see how he uses the short forms "gc" and "s" and "sc" throughout?

 

 

SELECT	gc.description, gc.image, CONCAT("C", sc.id) AS sku, 
CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name, 
sc.stock 
FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id 
INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id 
WHERE general_coffee_id=cat AND stock>0 

 

I understand that "gc" refers to general coffees, but I don't understand how the database understands this query because when the tables were created, there were no short forms used. For example, the table for general coffees was created thus

 

 

CREATE TABLE `general_coffees` (
 `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `category` varchar(40) NOT NULL,
 `description` tinytext,
 `image` varchar(45) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `type` (`category`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

so I would expect a query that uses "gc.description" to fail because how does it understand the "gc"?

 

thanks if you can help.

Link to comment
Share on other sites

Hi MikeMikeMike

 

Take note of this line

FROM specific_coffees AS sc

And

INNER JOIN general_coffees AS gc

 

This is where the shortcut so to speak is created so the database recognises 'gc' as the table 'general coffees'.

 

;)

  • Upvote 1
Link to comment
Share on other sites

So you can designate the shortcut (in the third line) after it's already been used (in the first line)?

 

 

 

Hi MikeMikeMike

 

Take note of this line

FROM specific_coffees AS sc

And

INNER JOIN general_coffees AS gc

 

This is where the shortcut so to speak is created so the database recognises 'gc' as the table 'general coffees'.

 

;)

Link to comment
Share on other sites

I am no expert (newbie!), trust me, but I would guess that the SELECT portion of the query using the shortcut/aliases doesn't really come into play until all the other parameters/conditions are meted out (FROM, INNER JOIN, WHERE, etc.) Once all the conditions of the query are set in place, then it will go about trying to "understand" the elements being selected. That's my uneducated guess, and maybe someone else will chime in to verify or dispute that.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...