Delete or Select Records Which Is In Or Not In A View

MySQL

Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:

DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)

An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey

Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.

For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey

Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?

T-SQL

Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:

SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)


http://www.electrictoolbox.com/article/mysql/cross-table-delete/

Add A Linked Server In SQL Server

The system stored procedure sp_addlinkedserver can be used to created a linked server represent a remote SQL Server. This also can be used when SQL query needs to access multiple databases located in different servers.

Only following parameters are essential:

EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;

If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.

To establish the access:

EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';

This would created linked server for all database users. When use it, just like this:

SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO


http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

Form And Table In HTML

In general, entire form must be completely included into a single table cell (single TD element). For format purpose, one might put form head/declaration outside the table and input items spread over cells. Put entire form with in a table but spread over several rows (TR elements), it will fail.

In addition, if one uses DHTML, form outsider table may not work in some browser, such as IE.

Conclusion: Put entire form completely with in a single table cell will be the best practice.


http://www.cs.tut.fi/~jkorpela/forms/tables.html

String Prefix 'N' In T-SQL

This is to enforce the string passed as Unicode NCHAR, NVARCHAR or NTEXT datatype, as opposed to CHAR, VARCHAR or TEXT. SQL Server only supports UTF-16 Unicode.

http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
http://databases.aspfaq.com/database/what-datatype-should-i-use-for-my-character-based-database-columns.html

Code Conventions: Transact-SQL

There is a great article about code conventions of Transact-SQL:

http://msdn.microsoft.com/en-us/library/ms177563(v=SQL.105).aspx

Transact-SQL Syntax Conventions (Transact-SQL)

Enforcing Width Of Cell <td> In HTML Table

It is hard to atomically break down long string such as URL to ensure the width in HTML. Here is a solution.

Within the cell or <td> section, cover your contents with a div section which shall include a width style property:

<div style="width:100px">

If that is not good enough, include this into your CSS:

word-wrap: break-word;

Parentheses () Outsider And After A Function - Nested Object Namespacing

It looks like:

; (function()
{ ...
})()

There are three purposes of it:

1. The function is anonymous so it can't be called the usual way. The outer brackets have to be there so that it can be called using brackets to enable the parameter feeding:

; (function(str){alert(str)}("test"))

2. Someone may to extend above-mentioned purpose to make the calling this anonymous function immediately after after the function definition, which is ofter called Self-ting Temporary Function. In this case, it is often with empty parameter.

3. Let us look at following example first:

; var myApp = {}
; myApp.message = 'hello'
; myApp.sayHello = function()
{ alert(this.message);
}
; myApp.sayHello() // works because "this" refers to myApp object.
; var importedfn = myApp.sayHello
; importedfn() // error because "this" refers to global object.

The lesson to be learned here is that this should never refer to an object being used as a namespace because it leads to confusion about importing identifiers from that namespace. People use nested object namespacing to avoid the identifier collisions. According to Peter Michaux, it is unnecessarily complex when the goal is simply avoiding identifier collisions.

Conclusion: Avoid this practice if you have other choice.


http://peter.michaux.ca/articles/javascript-namespacing
http://ejohn.org/apps/learn/