Stone Coast Web Design Blog

A jQuery plugin for rendering rich, fast-performing photo galleries

http://www.twospy.com/galleriffic/

Here are some examples of using IN vs. EXISTS

--Customers from Spain who made no orders 

SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
 AND NOT EXISTS
    (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID)
SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
    AND CustomerID NOT IN(SELECT CustomerID FROM Orders 
                          WHERE CustomerID IS NOT NULL)  

  
--Return customer for whom you cannot find any employee from the USA
--For whom you cannot find any order placed for the subject customer and by the select employee                          

SELECT * FROM Customers AS C
WHERE NOT EXISTS
    (SELECT * FROM Employees AS E
     WHERE Country = 'USA'
    AND NOT EXISTS
        (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND O.EmployeeID = E.EmployeeID))    

A self-contained subquery is a query that can run independently of the outer query.  Scalar (single-value) subqueries can appear anywhere in the query where an expression resulting in a scalar value is expected.   A scalar subquery is valid when it returns a single value or no values.  However, if a scalar subquery returns more than one value, a run-time eror will occur.

For example, the subquery returns a single value(1) and the outer query returns all orders with employeeID 1. If this subquery were to return more than one record, say (where lastname like 'd%', then it would return an error.

select orderid, employeeid
from orders
where employeeid = (SELECT employeeid from employees
where lastname like N'davolio')

One way to solve this is to use a technique using GROUP BY and DISTINCT COUNT. This query finds all orders with one of the five employeeIDs, groups those orders by CustomerId and returns
CustomerIDs tht have all five distinct employeeID values in their orders.

select customerID from 
orders
where employeeid in (1,2,3,4,8)
group by customerid
having count(distinct employeeid) = 5

But if you don't know the list of employeeID values in advance or there is a large list of them, then this approach is kind of useless.  to make this statement for dynamic and useful, you can use subqueries instead of literals.

select customerID from 
orders
where employeeid in 
(Select employeeID from employees where country = 'usa')
group by customerid
having count(distinct employeeid) = 
(Select Count(*) from employees where Country = 'usa')

I found this interesting. If any of the following field are NULL, then the concatenated value for DirName is also NULL.   You would think that if the less commonly populated fields of Prefix of Suffix were null, dirfname and dirlname would still be returned but this isn’t the case. 

select prefix + ' ' + dirfname + ' ' + dirlname + ' ' + suffix As DirName from individual

To solve this issue, use the IsNull function.

select isnull(prefix, '') + ' ' + dirfname + ' ' + dirlname + ' ' + isnull(suffix, '') As DirName from individual

Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Nice article: http://addyosmani.com/blog/11-ways-to-increase-your-jquery-performance/

I’m working on a large project for a federal agency and they want to start using some sensitive data on a Web site I’m working on for them.  While SSL is being used, they also require that some of the data being stored in the database be encrypted.  I found a pretty simple to do it on this article: http://www.sql-server-helper.com/functions/string-encryption.aspx

Here is the code:

Encryption

CREATE FUNCTION [dbo].[Encrypt] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
    
    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT
    
    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''
    
    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
        SET @vEncryptedString = @vEncryptedString + 
                                NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
                                @vBaseIncrement + @vIdx - 1)
        SET @vIdx = @vIdx + 1
    END
    
    RETURN @vEncryptedString

END
GO

Decrypt

CREATE FUNCTION [dbo].[Decrypt] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN

DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT

SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''

WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
    SET @vClearString = @vClearString + 
                        CHAR(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) - 
                        @vBaseIncrement - @vIdx + 1)
    SET @vIdx = @vIdx + 1
END

RETURN @vClearString

END
GO

Here are examples of how to use these functions

INSERT INTO USERS values('TestUser1', dbo.ENCRYPT('TestPW1'))

SELECT UserID, dbo.Decrypt(UserPW) AS Password from Users

This cool example allows you to enter some text into a textbox which searches and highlights the results in a Gridview.  Here is a working demo. And another example.

<script type="text/javascript" src="http://ajax.Microsoft.com/ajax/jquery/jquery-1.3.2.js"></script>

    <script type="text/javascript">
        $(function() {
            var $txtBox = $('input[id$=txtSearch]');
            $txtBox.keyup(function(e) {
            searchText();
        });
        
        function searchText() {
            var $txt = $txtBox.val().toLowerCase();
                $(".grid td").removeClass("highlight");
                if ($txt) {
                $(".grid > tbody > tr > td:not(:has(table, tr))")
                .filter(function() {
                return $(this).text().toLowerCase().indexOf($txt) != -1;
                }).addClass('highlight');
                }
            }
            searchText();
        });
</script>
    
</head>
<body>

<form runat="server">
Enter Text Here: <asp:TextBox ID="txtSearch" runat="server" /><br /><br />

<asp:gridview runat="server" CellPadding="4" 
ForeColor="#333333" GridLines="None" 
AllowSorting="false" class="grid" 
id="dgMovies" Width="80%" 
HorizontalAlign="Center" 
Font-Names="Tahoma" 
Font-Size="Small" 
EmptyDataText="No records were found">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />    
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="gray" Font-Bold="True" ForeColor="Black" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:gridview>

</form>

This would be helpful when expanding and collapsing panels.  Here is an example.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <title>jQuery from Microsoft AJAX CDN</title>
        <script type="text/javascript" src="http://ajax.Microsoft.com/ajax/jquery/jquery-1.3.2.js"></script>

        <script type="text/javascript">        
        $(function() {
            $('img[id$=imageSwap]').toggle(
                function() {
                $(this).attr("src", "../images/Hide.gif");
            },
                function() {
                $(this).attr("src", "../images/Show.gif");
                }
                );
            });        
        </script>
    </head>
    <body>


    <h2>Click on the image to swap</h2>
    <br /><br />
    <asp:Image ID="imageSwap" runat="server"
    ImageUrl="~/images/Show.gif" />
    
</body>
</html>

Pretty amazing grid plugin for ASP.NET. He has versions for both ASP.NET and PHP.

http://www.trirand.com/blog/?page_id=5