Left and Right Outer Joins...Awesome

Outer joins return all rows from one of the tables in the FROM clause, this is as long as those rows meet any WHERE search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.

Below is a "Left Outer Join" example that will be explained in extraneousness detail after the code.\/\/\/

 SELECT *  
 FROM Employee   
   LEFT OUTER JOIN Jobs  
   ON Employee.PayGrade = Jobs.PayGrade  

Since this is a LEFT OUTER JOIN so it will show all the "Employee" records, and all the "Jobs" records that have a "PayGrade" that is also in the same as the "Employee" "PayGrade".

Below is a "Right Outer Join" example that will be explained below the code.\/\/\/

 SELECT *  
 FROM Employee   
   RIGHT OUTER JOIN Jobs  
   ON Employee.PayGrade = Jobs.PayGrade    

A RIGHT OUTER JOIN is almost exactly the same as a LEFT OUTER JOIN, but instead of having all the records from the first table stated, "Employee", it will have all the records in "Jobs" instead, showing the "Employee" records with the same "PayGrade" of "Jobs".

More To Come, CA.

An Awesome SQL Assignment-Movie Quotes

Some Awesome assignment, database creation, table creations, and table filling.

 create database MovieQuote  
 GO  
   
 CREATE table actor(  
      actorId int Primary Key Identity(1,1),  
      actorName varchar(50)  
 )  
   
 CREATE table quote(  
      quoteId int Primary Key Identity(1,1),  
      quoteName varchar(50)  
 )  
   
 CREATE table movie(  
      movieId int Primary Key Identity(1,1),  
      movieName varchar(50),  
      xrefActorxQuote int  
 )  
   
 CREATE table xrefActorxQuote(  
      xrefActorxQuoteId int Primary Key Identity(1,1),  
      actorId int,  
      quoteId int  
 )  
 Go  
   
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (1, 1, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (2, 7, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (3, 8, 8)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (4, 9, 9)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (5, 10, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (6, 41, 1)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (7, 42, 10)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (8, 43, 11)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (9, 44, 12)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (10, 45, 12)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (11, 46, 17)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (12, 47, 13)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (13, 48, 14)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (14, 49, 14)  
 INSERT xrefQuotexActor (xrefQuotexActorId, quoteId, actorId) VALUES (15, 50, 15)  
   
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (1, 14, 2)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (2, 15, 2)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (3, 12, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (4, 16, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (5, 17, 5)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (6, 10, 1)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (7, 1, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (8, 8, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (9, 9, 3)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (10, 11, 4)  
 INSERT xrefActorxMovie (xrefActorxMovie, actorId, movieId) VALUES (11, 13, 5)  
   
 INSERT quote (quoteId, quote) VALUES (1, 'Here''s looking at you, kid.')  
 INSERT quote (quoteId, quote) VALUES (7, 'endship.')  
 INSERT quote (quoteId, quote) VALUES (8, 'Play it, Sam. Play ''As Time Goes By.')  
 INSERT quote (quoteId, quote) VALUES (9, 'Round up the usual suspects.')  
 INSERT quote (quoteId, quote) VALUES (10, 'We''ll always have Paris.')  
 INSERT quote (quoteId, quote) VALUES (41, 'Of all the gin joints in all the towns in all the world, she walks into mine.')  
 INSERT quote (quoteId, quote) VALUES (42, 'I''m going to make him an offer he can''t refuse.')  
 INSERT quote (quoteId, quote) VALUES (43, 'Frankly, my dear, I don''t give a damn.')  
 INSERT quote (quoteId, quote) VALUES (44, 'After all, tomorrow is another day!')  
 INSERT quote (quoteId, quote) VALUES (45, 'As God is my witness, I''ll never be hungry again.')  
 INSERT quote (quoteId, quote) VALUES (46, 'Plastics.')  
 INSERT quote (quoteId, quote) VALUES (47, 'Mrs. Robinson, you''re trying to seduce me... Aren''t you?')  
 INSERT quote (quoteId, quote) VALUES (48, 'Toto, I''ve got a feeling we''re not in Kansas anymore')  
 INSERT quote (quoteId, quote) VALUES (49, 'There''s no place like home.')  
 INSERT quote (quoteId, quote) VALUES (50, 'I''ll get you, my pretty, and your little dog too!')  
   
 INSERT movie (movieId, movieName) VALUES (1, 'The Godfather')  
 INSERT movie (movieId, movieName) VALUES (2, 'The Wizard of Oz')  
 INSERT movie (movieId, movieName) VALUES (3, 'Casablanca')  
 INSERT movie (movieId, movieName) VALUES (4, 'Gone with the Wind')  
 INSERT movie (movieId, movieName) VALUES (5, 'The Graduate')  
   
 INSERT actor (actorId, actorName) VALUES (1, 'Humphrey Bogart')  
 INSERT actor (actorId, actorName) VALUES (8, 'Ingrid Bergman')  
 INSERT actor (actorId, actorName) VALUES (9, 'Claude Rains')  
 INSERT actor (actorId, actorName) VALUES (10, 'Marlon Brando')  
 INSERT actor (actorId, actorName) VALUES (11, 'Clark Gable')  
 INSERT actor (actorId, actorName) VALUES (12, 'Vivien Leigh')  
 INSERT actor (actorId, actorName) VALUES (13, 'Dustin Hoffman')  
 INSERT actor (actorId, actorName) VALUES (14, 'Judy Garland')  
 INSERT actor (actorId, actorName) VALUES (15, 'Margaret Hamilton')  
 INSERT actor (actorId, actorName) VALUES (16, 'Rhett Butler')  
 INSERT actor (actorId, actorName) VALUES (17, 'Mr. Maguire')  
   

That thing people do...

 select movieName, actorName, quote  
 From movie m  
      inner join xrefActorxMovie xam  
      on m.movieId = xam.movieId  
        
      inner join actor a  
      on xam.actorId = a.actorId  
        
      inner join xrefQuotexActor xqa  
      on a.actorId = xqa.actorId  
        
      inner join quote q  
      on xqa.quoteId = q.quoteId  
        
 Where actorName = 'Humphrey Bogart'  

Another thing people do...

 Select movieName, actorName, quote  
 From dbo.movie m  
      inner join dbo.xrefActorxMovie xAM  
      on m.movieId = xAM.movieId  
   
      inner join dbo.actor a  
      on xAM.actorId = a.actorId  
   
      inner join dbo.xrefQuotexActor xQA  
      on a.actorId = xQA.actorId  
   
      inner join dbo.quote q  
      on xQA.quoteId = q.quoteId  
        
 where movieName = 'Casablanca'  

The Last thing people do...

 Select actorName, quote, movieName  
 From actor a  
      inner join xrefQuotexActor xqa  
      on a.actorId = xqa.actorId  
        
      inner join quote q  
      on xqa.quoteId = q.quoteId  
        
      inner join xrefActorxMovie xam  
      on a.actorId = xam.actorId  
        
      inner join movie m  
      on xam.movieId = m.movieId  
 Where quote = 'Plastics.'  

More to come, CA. Sometime.

Asp.Net Form Validation...YAY!!!

Form validation in ASP.Net is so much easier than you would think, using just two tags you can check if a text box is null...\/\/\/

 <table>  
    <tr align="right">  
       <td>Description of File: </td>  
       <td>  
         <asp:TextBox id="txtName" runat="server" />  
       </td>  
       <td align="left">  
         <asp:RequiredFieldValidator id="valTxtName" ControlToValidate="txtName" ErrorMessage="Please enter your Name!" runat="server" />  
       </td>  
     </tr>  
     <tr>  
       <td colspan="2" align="center""><asp:Button ID="add" runat="server"   
           Text="Add Old Pic" onclick="add_Click" /></td>  
     </tr>  
 </table>  

As long you use the the "asp" tags, using "asp:TextBox" and set the id property of the text box, this will come into play in the "asp:RequiredFieldValidator". The asp validator can be any place on the form, and can be used to send a message if the validation fails, by still having a null value.

SQL-Ice Cream Assignment--Part 2

The sale, distributors, and inventory queries of an ice cream shop database.\/\/\/

 Select flavName, brandName, saleScoops, salePrice, saleDate  
 From dbo.iceCreamSales S  
      inner join dbo.xrefIceCreamSalesxFlavorBrand xS  
      on S.saleId = xS.salesId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xS.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 Go

This is the sales query, showing the sale, how many scoops, what date, and when a sale of a flavor and brand has happened.

 Select tubsOnHand, flavName, brandName  
 From dbo.iceCreamInventory I  
      Inner Join dbo.xrefIceCreamInventoryxFlavorBrand xI  
      on I.invenId = xI.invenId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xI.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 Where tubsOnHand <= 1  
 Go

This is the Inventory query, showing where there is one or less tubs left on had for all flavor and brands.

 Select distrName, flavName, brandName  
 From iceCreamDistributor D  
      Inner Join dbo.xrefIceCreamDistributorxFlavorBrand xD  
      on D.distrID= xD.distrId  
      inner join dbo.xrefIceCreamFlavorxBrand xF  
      on xD.xrefFlavId = xF.xrefFlavId  
      inner join dbo.iceCreamFlavor F  
      on xF.flavId = F.flavId  
      inner join dbo.iceCreamBrand B  
      on xF.brandId = B.brandId  
 where B.brandId=1 and F.flavId=2  
 Go  

This is the Distriubtors query, found by brand and flavor combination.

SQL-Ice Cream Assignment

The creation of a database, the creation of three tables, inserting so some records into the tables, an inner join to get the brands of a flavor from the tables using a cross reference, and another inner join to get the flavors of a brand from the tables using a cross reference.

 CREATE DATABASE ColdFusion  
 GO  
   
 USE ColdFusion  
 CREATE table iceCreamBrand(  
      brandId int Primary Key Identity(1,1),  
      brandName varchar(50)  
 )  
 CREATE table iceCreamFlavor(  
      flavorId int Primary Key Identity(1,1),  
      flavorName varchar(50)  
 )  
 CREATE table xrefIceCream(  
      flavorId int,  
      brandId int  
 )  
 Go  
   
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (1, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (2, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (3, 1)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (4, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (5, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (6, 2)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (7, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (8, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (9, 3)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (10, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (11, 4)  
 INSERT [xrefIceCream] ([flavorID], [brandID]) VALUES (12, 4)  
 Go  
   
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (1, N'Marshmallow Candy')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (2, N'Jelly Bean')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (3, N'Vanilla')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (4, N'Chocolate')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (5, N'Butter Pecan')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (6, N'Strawberry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (7, N'Cookies and Cream')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (8, N'Coffee')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (9, N'Rocky Road')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (10, N'Chocolate Marshmallow')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (11, N'Cherry')  
 INSERT [iceCreamFlavor] ([flavID], [flavName]) VALUES (12, N'Chocolate Almond')  
 Go  
   
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (1, N'Baskin-Robbins')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (2, N'Ben and Jerry''s')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (3, N'Gay Lea')  
 INSERT [iceCreamBrand] ([brandID], [brandName]) VALUES (4, N'Healthy Choice')  
 Go  
   
 declare @flavorName varchar(50);  
 set @flavorName = 'Vanilla';  
   
 Select brandName, flavName  
 From iceCreamBrand B  
      Inner Join xrefIceCream X  
      on B.brandId=X.brandId  
           Inner join iceCreamFlavor F  
           on F.flavID=X.flavorID  
 Where flavName=@flavorName  
 Go  
   
 declare @brandName varchar(50);  
 set @brandName = 'Baskin-Robbins';  
   
 Select flavName, brandName  
 From iceCreamFlavor F  
      Inner Join xrefIceCream X  
      on F.flavID=x.flavorId  
           inner join iceCreamBrand B  
           on B.brandID = x.brandID   
 where brandName=@brandName  
 Go  

More to come, CA.

ASP.Net Menu Navigation

This time around we are going to look into the "Menu" control aspect of ASP.Net to create a navigation menu for a website. Posted below is a code snip-it of just a "Menu" control...

 <asp:Menu ID="NavigationMenu" runat="server" Orientation="Vertical" StaticDisplayLevels="2" MaximumDynamicDisplayLevels="2">  
     <LevelMenuItemStyles>  
         <asp:MenuItemStyle CssClass="menuLevel1"/>  
         <asp:MenuItemStyle CssClass="menuLevel2"/> 
         <asp:MenuItemStyle CssClass="menuLevel3"/>  
     </LevelMenuItemStyles>  
     <Items>  
         <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home" />
         <asp:MenuItem NavigateUrl="~/Media.aspx" Text="Media"> 
             <asp:MenuItem NavigateUrl="~/Pictures.aspx" Text="Pictures">
                 <asp:MenuItem NavigateUrl="~/PicsNew.aspx" Text="New Pictures" />
                 <asp:MenuItem NavigateUrl="~/PicsOld.aspx" Text="Old Pictures" />
             </asp:MenuItem>
             <asp:MenuItem NavigateUrl="~/Videos.aspx" Text="Videos" />  
         </asp:MenuItem>  
         <asp:MenuItem NavigateUrl="~/About.aspx" Text="About" />  
     </Items>  
 </asp:Menu>  

Now lets take a walk, to start we will look at the first tag, the "asp:Menu" tag, this tag will have everything about your menu control between the start and end tags. There are some very important properties included in this tag, like the "Orientation", "StaticDisplayLevels", and the "MaximumDynamicDisplayLevels" properties, the rest should all be review. To start the "Orientation" property is used to set the menu as a "Vertical" or "Horizontal" menu, as the values imply it will either have the menu bar spread out side-to-side on the web page or down to the bottom of the web page. The next two, the "StaticDisplayLevels" and "MaximumDynamicDispalyLevels" are kinda related in that they can be used to setup if the menu will "pop" out when the cursor is hovered over a menu item that has multiple levels or be "Static" on the page, so you can just click on the menu item. I will get into more of how to take care of this later in the post, but first I will talk about how to format the menu items.

The "LevelMenuItemStyles" tag is used to set the "CSS" formating in the menu by how the levels of the menu are deep. This means you can have a different format for each level of the menu to allow for a fancier menu style. On a note you have to have an "asp:MenuItemStyle" tag for each level of your menu control, if you don't have the same amount of tags setting a style from a "CSS" the missing styles will just be plain, blue text with a transparent background.

Now to the bulk of the "Menu" control, the "Items" tag, this is where all the navigation items of a menu will go. For ever "asp:MenuItem" in between the "Items" start and end tag, you will get click-able link to another page. The two main properties of an "asp:MenuItem" are the "NavigateUrl" property, which tells the link what page to route too, and the "Text" property, that holds the display name of the link. Looking at the example above you can see that some of the tags have the ending "/" inside the tag its self, and the others don't, this is how you make a dynamic menu. By making a start and end tag of a "Menu Item" you can set how many levels deep the menu will go, based on how many times you have nested the "Menu Item".

More to Come, CA.