Archive

Archive for July, 2005

Potato Powered Webserver

July 18th, 2005



Heres one just for the nerds…

You heard right….this “beast” runs at
76KHz and draws its power from 5 potatoes, running at roughly 1 millionth of
the power of a 386 motherboard.

 

http://d116.com/spud/

 

fun fun style='font-size:10.0pt;font-family:Wingdings'>J

 

Rex IT & Software

This have been quiet lately…

July 18th, 2005


And I’m fully aware of it too – sorry :P

 

After coming back from holiday its been hard to get into the
swing of things – I still have over 2000 MSDN blog posts to sort through….

 

 

 

 

 

Rex IT & Software

The day the UNION saved my bacon

July 5th, 2005



namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"
downloadurl="http://www.5iantlavalamp.com/"/>

Sounds weird, right? Not quite :)

 

Imagine if you will, Xerxes sitting at his work desk on a
Saturday LATE afternoon
really pushing to get his work done to meet a looming deadline. At this point
in the story, my current task is to create an SQL query to load the same
information from three separate tables, but return the data as one column in
the result set. (See picture below…

 

face=Arial>

 

 

Okay so we have three tables like in the diagram
above….Tables B and C have a foreign key back to Table A. What I needed
to do was grab a list of ALL *_Name fields in each table and have them returned
in one column (because this “Name” field became one of the columns
in an outer query – ie: this also had to be a subquery.

 

At first it looks like a simple INNER JOIN on A_FK –
A_PK but if you do that then you’ll still end up getting 3 columns in the
SELECT clause…

 

Funnily enough the answer is so simple, but eluded me at the
start….Simple set theory dictates that the result of a UNION operation on
any two sets is the conglomeration of those two sets excluding
duplicates….That means I just had to UNION 3 select statements together,
and viola, all values for that “name” across each table came into
one field…(with a bit of field aliasing and inline table’ing :P)

 

For those interested – here is what the SQL looked
like (for obvious purposes, I cant post the real code, but hopefully this
conveys the point)

 

SELECT

           
A.PK,

           
NAMES.Name

           
D.Field,

           
E.Field,

           
F.Field,

           
G.Field,

FROM

           
A JOIN

           
(

                       
SELECT

face=Arial>A2.A_PK color=blue>AS PK,

face=Arial>A2.A_NAME color=blue>AS Name

                       
FROM A A2

 

                       
UNION color=blue>

 

                       
SELECT

face=Arial>B.B_PK color=blue>AS PK,

face=Arial>B.B_NAME color=blue>AS Name

                       
FROM B

 

                       
UNION color=blue>

 

                       
SELECT

face=Arial>C.C_PK color=blue>AS PK,

face=Arial>C.C_NAME color=blue>AS Name

                       
FROM C

 

           
) AS NAMES color=blue>ON
NAMES.PK = A.PK

           
JOIN D color=blue>ON
D.D_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> w:st="on">E ON
E.E_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> F color=blue>ON F.F_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> G color=blue>ON G.G_PK = A.A_PK

WHERE

           
Blah….

image001.gif
image002.gif

Rex IT & Software