MySQL Double Joins

devices table
device_id (PK) | device_name

site_contacts table
device_id (FK) | contact_name | contact_email

site_address table
device_id (FK) | address

Select specific info from 2 tables without having to pull all info from all 3 tables:
################################################
SELECT contact_name, contact_email, address
FROM site_contacts INNER JOIN devices
ON site_contacts.device_id = devices.device_id
INNER JOIN site_address
ON site_address.device_id = devices.device_id
WHERE devices.device_name = 'fake_router.mrn';
################################################


LEFT (aka OUTER) JOIN:
Returns all records from the "LEFT" table (the table listed after the "FROM" clause) even the records that have no matching row in the "RIGHT" table (the table listed after the "LEFT JOIN" clause).

LEFT JOIN NOTES:
If both tables in a left join have the same column name, you can simplify your query with the following:
################################################
SELECT *
FROM devices LEFT JOIN site_address
USING (device_id);
################################################

*Note: In this case, all records from the 'devices' table would be returned even if there were no corresponding record in the 'site_address' table.


INNER (aka CROSS) JOIN:
Only returns matching row records that exist in 2 or more tables
################################################
SELECT device_name, address
FROM devices, site_address
WHERE devices.device_id = site_address.device_id;
################################################

*Note: in this case, only records from the 'devices' table that have matching records in the 'site_address' table will be returned (or vice-versa). The order of the tables listed in your query will not matter (because you are performing an INNER JOIN).

Secure HD Erase

How to REALLY erase a hard drive by ZDNet's Robin Harris -- You may already know that “deleting” a file does nothing of the sort. But did you know that your disk drive has a built-in system for the secure erasure of data? No? Then read on. What do you mean “delete” doesn’t delete? File information is maintained in a directory so your operating system can find it. All that [...]

Read Any File Line by Line

Option Explicit

Dim objFSO
Dim filePath,arrLines(),y,strComputer

Set objFSO = CreateObject("Scripting.FileSystemObject")

filePath = "ip_list.txt"

readEntireFile(filePath)

For y=0 To UBound(arrLines)
strComputer = arrLines(y)
MsgBox strComputer,0,"Array Element: " & y
Next


'*******************************************************
Function readEntireFile(path)

Const ForReading = 1
Dim myFile, x
Set myFile = objFSO.OpenTextFile(path, ForReading, False)
x=0

Do While myFile.AtEndOfStream <> True
ReDim Preserve arrLines(x)
arrLines(x) = myFile.ReadLine
x=x+1
Loop

myFile.Close

readEntireFile = arrLines

End Function
'*********************************************|

Increase Icon Cache / Remember Folder View / Registry Favorites

--------------------------------------------------------------
"HKLM\SOFTWARE\Microsoft\Windows\Current Version\Explorer"
Max Cached Icons
Reg_SZ or DWORD
value can be set from 100 to 4096
-------------------------------------------------|

*** FORCE REMEMBER ICON TYPE ***
--------------------------------------------------------------
Windows Registry Editor Version 5.00


[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer]
"Max Cached Icons"=dword:000007d0
; 000007d0 = 2000 decimal
-------------------------------------------------|

*** FORCE REMEMBER FOLDER VIEWS ***
--------------------------------------------------------------
Windows Registry Editor Version 5.00


[-HKEY_CURRENT_USER\Software\Microsoft\Windows\ShellNoRoam\BagMRU]
[-HKEY_CURRENT_USER\Software\Microsoft\Windows\ShellNoRoam\Bags]

[HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell]
"BagMRU Size"=dword:00001f40
; 00001f40 = 8000 decimal

[HKEY_CURRENT_USER\Software\Microsoft\Windows\ShellNoRoam]
"BagMRU Size"=dword:00001f40

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]
"NoSaveSettings"=dword:00000000

;Look up Disabled Desktop icon on M.E. PC
;http://www.kellys-korner-xp.com/xp_tweaks.htm
-------------------------------------------------|

*** REGISTRY FAVORITES ***
--------------------------------------------------------------
Windows Registry Editor Version 5.00


[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Applets\Regedit\Favorites]
"RecycleBinIcon"="My Computer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\CLSID\\{645FF040-5081-101B-9F08-00AA002F954E}\\DefaultIcon"

"HKLMRun"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run"

"Time Servers"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\DateTime\\Servers"

"HKLM Home"="My Computer\\HKEY_LOCAL_MACHINE"

"IE Restrictions"="My Computer\\HKEY_CURRENT_USER\\Software\\Policies\\Microsoft\\Internet Explorer"

"HKUAdminRun"="My Computer\\HKEY_USERS\\*** GUID ***\\Software\\Microsoft\\Windows\\CurrentVersion\\Run"

"HKCU Run"="My Computer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Run"

"HKLM WSH"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows Script Host\\Settings"

"HKLM RunOnce"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\RunOnce"

"IE Proxy Settings"="My Computer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings"

"Group Policy SIDS"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Group Policy\\*** GUID ***"

"View Source Editor"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Internet Explorer\\View Source Editor"

"Local NTP Server"="My Computer\\HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\W32Time\\Parameters"

"Change NIC MAC"="My Computer\\HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Control\\Class\\{4D36E972-E325-11CE-BFC1-08002bE10318}\\0001"

"EnableOpLocks1"="My Computer\\HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\lanmanserver\\parameters"

"OpLocksEnabled2"="My Computer\\HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\MRxSmb\\Parameters"

"Classic Shell/Disable Quicklaunch"="My Computer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Policies\\Explorer"

"Enable DCOM"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Ole"

"ODBC.INI"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI"

"Max Cached Icons"="My Computer\\HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Explorer"

"Min Outlook to SysTray"="My Computer\\HKEY_CURRENT_USER\\Software\\Microsoft\\Office\\10.0\\Outlook\\Preferences"
------------------------------------------------|

Timer HTA

<html>
<head>
<title>Elapsed Time Timer Countdown Countup Count</title>

<HTA:APPLICATION
ID="objTimer"
APPLICATIONNAME="Timer"
SCROLL="yes"
SINGLEINSTANCE="no"
WINDOWSTATE="normal">
</head>

<script language="vbs">
Dim x : x = 1
Dim iTimerId

Sub Window_Onload
Const iWIDTH = 525
Const iHEIGHT = 250
Window.ResizeTo iWIDTH, iHEIGHT
Window.MoveTo (Screen.availWidth-iWIDTH)/2,_
(Screen.availHeight-iHEIGHT)/2
last.InnerHTML = "Current Time: " & now() & " -- Last Modified: " & document.lastModified
End Sub

Function myTime
timerArea01.innerHTML = x
If x=60 Then
stopTimer
timerArea01.innerHTML = "<a style='color:red;'>DONE!</a>"
End If
x=x+1
End Function

Sub stopTimer
window.clearInterval(iTimerId)
End Sub

Sub RunScriptHTARefresh
StopTimer
Location.Reload(True)
End Sub

Function startTimer
iTimerId=window.setInterval("myTime", 1000)
End Function

' (window.innerWidth/2)px
</script>
<BODY>

<center>
<span id="timerArea01" style="font: bold 3 em/2 arial, helvetica, sans-serif;"
name="timerarea">
</span>
</center>

<input id=start01 class="button" type="button"
value="Start" name="start" style="position:absolute; left:10px; top:10px"
onClick="startTimer">

<input id=stop01 class="button" type="button"
value="Stop" name="stop" style="position:absolute; right:10px; top:10px"
onClick="stopTimer">

<input id=refresh01 class="button" type="button"
value="Refresh" name="refresh" style="position:absolute; left:10px; bottom:20px;"
onClick="RunScriptHTARefresh">

<a id="last" style="position:absolute; bottom:5px; font:bold 0.5em/1 'courier new', helvetic, serif;"></a>
</body>
</html>

Easy Copy

<a style="font: 0.75em/1 'courier new', serif; color: black">

Write NewLine Local File/Enum Folders

Option Explicit

Dim objShell,objFSO,objWMIService
Dim arrFolders(),rmtFile()
Dim strComputer,folder01,folder02,y,temp

'strComputer="tower" '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@remote WMI@@@@@@@
strComputer = "."

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

folder01 = "c:\bootdrv\alohaqs" 'change this for server
folder02 = "\\tower" 'remote WMI

ReadRmtFolder folder01,arrFolders

' ~~~~~~~~~~ this section for remote WMI @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'For y=0 To UBound(arrFolders)
' ReDim Preserve rmtFile(y)
' temp = objFSO.BuildPath(arrFolders(y),"\ftp.ucf")
' rmtFile(y) = folder02 & Replace(temp,"c:","")
'Next
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ @@@@@@@@@@@@@@@@@

' ~~~~~~~~~~ this section to run locally only
For y=0 To UBound(arrFolders)
ReDim Preserve rmtFile(y)
temp = objFSO.BuildPath(arrFolders(y),"\ftp.ucf")
rmtFile(y) = temp
Next
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For y=0 To UBound(rmtFile)
MsgBox rmtFile(y)
If objFSO.FileExists(rmtFile(y)) Then
readEntireFile(rmtFile(y))
Else
MsgBox "no file there!",0,rmtFile(y)
End If
Next

quitscript



'******************************************************************************
Function readRmtFolder(path,arr)

Dim x,y,colSubfolders,objFolder
x=0

Set colSubfolders = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='" _
& path & "'} " _
& "WHERE AssocClass = Win32_Subdirectory " _
& "ResultRole = PartComponent")

For Each objFolder in colSubfolders
ReDim Preserve arr(x)
arr(x) = objFolder.Name
x=x+1
Next

End Function
'*******************************************************************|

'******************************************************************************
Function readEntireFile(path)

Const ForReading = 1
Dim myFile,x,arrLines(),t,z
Set myFile = objFSO.OpenTextFile(path,ForReading,False)
x=0

If myFile.AtEndOfStream = True Then
MsgBox "Nothing in this file!",0,path
Exit Function
End If

Do While myFile.AtEndOfStream <> True
t = myFile.ReadLine
ReDim Preserve arrLines(x)
arrLines(x) = t
x=x+1
Loop

myFile.Close

writeNewTextToFile path,arrLines

End Function
'*******************************************************************|

'******************************************************************************
Function writeNewTextToFile(path,arr)

Const ForWriting = 2
Dim myFile,x
Set myFile = objFSO.OpenTextFile(path,ForWriting,False)
x=0

Do
If (InStr(1,arr(x),"user blank1 blank2",1)) Then
MsgBox "I'm here!"
myFile.WriteLine "user BLANK1 BLANK2"
Else
myFile.WriteLine arr(x)
End If
x=x+1
Loop Until x=(UBound(arr)+1)

myFile.Close

End Function
'*******************************************************************|
'******************************************************************************
Function quoteMe(x)

quoteMe = Chr(34) & x & Chr(34)

End Function
'*******************************************************************|
'******************************************************************************
Function singleMe(x)

singleMe = Chr(39) & x & Chr(39)

End Function
'*******************************************************************|
'******************************************************************************
Function dbbl

dbbl = vbCr & vbCr

End Function
'*******************************************************************|
'******************************************************************************
Sub quitscript

MsgBox "Quitting Now"
Wscript.Quit

End Sub
'*******************************************************************|

Query Index Service

Option Explicit
Dim objShell
Dim command,srchWord,fileType,strToCopy
Set objShell = CreateObject("WScript.Shell")
command = "mmc %windir%\system32\ciadv.msc"
'advanced query sample = @all keyboard & (@filename = *.url)
srchWord = askme("Enter search word","Search For...")
fileType = askme("Enter file type" & dbbl & "Example: *.vbs","File Type...")
strToCopy = "@all " & srchWord & " & (@filename = " & fileType & ")"
copyToClipboard(strToCopy)
shellRun command,Null

'******************************************************************************
Sub copyToClipboard(vartext)
Dim objIE
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate("about:blank")
objIE.document.parentwindow.clipboardData.SetData "text", vartext
objIE.Quit
End Sub
'*******************************************************************|

'******************************************************************************
Function shellRun(cmd,path)
objShell.Run cmd,3,False
End Function
'*******************************************************************|

'******************************************************************************
Function askMe(x,y)
askMe = InputBox(x,y)
End Function
'*******************************************************************|

'******************************************************************************
Function quoteMe(x)
quoteMe = Chr(34) & x & Chr(34)
End Function
'*******************************************************************|

'******************************************************************************
Function dbbl
dbbl = vbCr & vbCr
End Function
'*******************************************************************|

'*******************************************************
Sub quitscript
MsgBox "Quitting Now"
Wscript.Quit
End Sub
'*********************************************|

Double LOJ #2

strSQL = "SELECT DISTINCT i.ItemId " _
 & "FROM Item AS i " _
 & "LEFT OUTER JOIN ModifierItem AS m " _ 
 & "ON i.ItemId = m.ItemId " _
 & "LEFT OUTER JOIN SubMenuItem AS s "_
 & "ON i.ItemId = s.ItemId " _
 & "WHERE (m.ItemId IS NULL) AND (s.ItemId IS NULL) " _
 & "ORDER by i.ItemId" 

Left Outer Join SQL

SELECT DISTINCT i.ItemId
FROM Item AS i
LEFT OUTER JOIN ModifierItem AS m
ON i.ItemId = m.ItemId
LEFT OUTER JOIN SubMenuItem AS s
ON i.ItemId = s.ItemId
WHERE (m.ItemId IS NULL) AND (s.ItemId IS NULL)

strSQL = "SELECT DISTINCT i.ItemId " _
 & "FROM Item AS i " _
 & "LEFT OUTER JOIN ModifierItem AS m " _ 
 & "ON i.ItemId = m.ItemId " _
 & "LEFT OUTER JOIN SubMenuItem AS s " _
 & "ON i.ItemId = s.ItemId " _
 & "WHERE (m.ItemId IS NULL) AND (s.ItemId IS NULL)"