Wednesday, June 26, 2013

Facebook Graph API

https://graph.facebook.com/jessestay

https://graph.facebook.com/jessestay?metadata=1

https://graph.facebook.com/jessestay?fields=name,picture

https://graph.facebook.com/icentrisdevs?fields=name,picture,friends&oauth_token=425581657554925|jssQVK-ENgAFILVt5-XVQRSYbl4
(To get the oauth_token - go to Access Token tool - https://developers.facebook.com/tools/access_token/)

Javascript Test Console & Examples:
https://developers.facebook.com/tools/console/

Posting a photo to a Wall:
http://stackoverflow.com/questions/3361507/facebook-js-sdk-how-to-post-an-image-on-wall
(see HBK's answer)

Posting a Canvas element as a Photo to Facebook's Wall:
https://coderwall.com/p/4qpmfw
    -- https://github.com/lukasz-madon/heroesgenerator
        -- uses code from here - http://stackoverflow.com/questions/5292689/sending-images-from-canvas-elements-using-ajax-and-php-files

Phonegap & Facebook

Options:

1. FacebookConnect plugin for Phonegap Build
https://github.com/phonegap-build/FacebookConnect

2. Phonegap-Facebook-plugin
https://github.com/phonegap/phonegap-facebook-plugin

3. without using native plugin  (added on Jun 12 2013)
https://github.com/studiosoton/faceGap
Jquery PhoneGap Facebook Connect Plugin - without native plugin
This plugin connects to facebook without using native plugin (Android, iOS, etc)
Plugin working mode "Site Login with Facebook"

Sunday, June 23, 2013

Creating a new Phonegap app to work with in Eclipse

http://docs.phonegap.com/en/2.2.0/guide_getting-started_android_index.md.html

After Steps 1-3, for each project we have to repeat Step 4.

1. Navigate to where 'create.exe' is: 
C:\Windows\System32> cd C:\dev\phonegap-2.8.1\lib\android\bin

2. C:\dev\phonegap-2.8.1\lib\android\bin> create   C:\dev\PGAndroid2 com.psjplearn.PGAndroid2   PGAndroid2

C:\dev\PGAndroid2 = folder to create for new project
com.psjplearn.PGAndroid2 = package name (com.companyname.appname)
PGAndroid2 = project name

3. Add files to assets\www folder



Javascript - scope, closures

Sunday, June 16, 2013

Saturday, June 15, 2013

Backbone.js - 1

OOP using Classic Javascript vs Backbone.js:
JS Bin
Backbone Models: JS Bin

Frameworks vs Libraries, Backbone.js

From Pluralsight's course on Backbone.js
"Frameworks call your code , while your code calls Libraries...
Another way to think of it is - Frameworks control how you design you app...they specify things like, 'where' you put 'what' files, what you can do, and how you do it..
It's great for getting started, and it's great for getting things done quickly, but it is terrible for flexibility..
Libraries provide some useful features, and then you are on your own.
Backbone for example, provides help with a number of common user interface features, but does not help at all with how you design your application. It's both its strength and weakness." 
...
Backbone is a library of tools..

Running a .apk file in Android Emulator

From :
http://stackoverflow.com/questions/3480201/how-do-you-install-an-apk-file-in-the-android-emulator
http://stackoverflow.com/questions/4449540/failure-install-failed-already-exists-when-i-tried-to-update-my-application

Pre-requisite - install Java & ADT bundle from http://developer.android.com/sdk/index.html

Download the .apk file (from Phonegap Build)

In console - go to \sdk\platform-tools directory (It will have adb.exe)
C:\dev\adt-bundle-windows-x86_64-20130522\sdk\platform-tools> adb install -r PGApp1.apk
(-r --> required to re-install the same app)

Note: It is taking a really long time (several minutes) for the Emulator to start on Windows 7 64 bit machine (Have seen similar complaints in StackOverflow questions)

Thursday, June 13, 2013

Javascript at LinkedIn

HTML5 Canvas related

http://garage.socialisten.at/2012/12/new-adventures-in-html5-part-1-html5-canvas/
We briefly considered doing everything Canvas-related in this app in plain JavaScript, but since we wanted to use layers, events and transitions (all of which are not available in the Canvas API), we opted to use the Canvas framework KineticJS.
http://www.html5canvastutorials.com/

HTML5 Canvas - put 2 images beside each other

JS Bin
JS Bin
JS Bin
In the above code, toDataURL() is giving an error because of security restrictions. Observe the alert() does not fire.

Wednesday, June 12, 2013

Javascript - namespace/module example


Method1: (This is what I found in an example)
--------
var SILLY = (function (module) {
      
      module.DoIt = function (resultObject) {
          resultObject.prepend(Date() + '
'); }; return module; } (SILLY || {}); // "{}" means "new Object()". It is a shortcut NOTE: The last line could be : (SILLY || {}) => (SILLY || (new Object()))); ----- Method2: (This is how we would write the above code in C#/Java style) --------- var SILLY = (function() { if (SILLY === undefined) { var SILLY = new Object(); SILLY.DoIt = function (resultObject) { resultObject.prepend(Date() + '<br >'); }; } return SILLY; }());
jsbin link for full example - http://jsbin.com/ahuqow/1/edit

Phonegap learning - jQuery Mobile, Handlebars.js example

Saturday, June 8, 2013

Kendo UI & Phonegap

Mobile Development & Phonegap related

Javascript libraries:

From http://www.adobe.com/devnet/phonegap/articles/phonegap-apps-powered-by-developercom.html

Reuse Existing Tools

Since PhoneGap applications leverage Web technologies, there are countless existing libraries or frameworks that you can leverage to accelerate application development. Whether it is a solution accelerator framework like jQuery or Zepto, a UI toolkit like Twitter Bootstrap, jQuery Mobile, or Sencha, or a data visualization library like Raphael.js, Highcharts, or RGraph.js, there are lots of resources both open source and commercially that you can leverage within your applications.
Solution Accelerator Frameworks - jQuery, Zepto
UI Toolkits - Twitter Bootstrap, jQuery Mobile, Sencha
Data Visualization Libraries - Raphael.js, Highcharts, RGraph.js

3rd party Database options:

1. Parse.com -
http://www.adobe.com/devnet/phonegap/articles/using-parse-with-phonegap-a-marriage-made-in-awesome.html - Dec 2012

2. Database.com -
http://www.adobe.com/devnet/phonegap/articles/phonegap-apps-powered-by-developercom.html - June 2012

Push Notifications:

Below plugin allows the App to receive push notifications on both Android and iOS devices:
https://github.com/phonegap-build/PushPlugin
Tutorial - http://devgirl.org/2013/01/24/push-notifications-plugin-support-added-to-phonegap-build/

Above github link also mentions a Ruby Gem called "pushmeup"  (See section titled "Test Environment")
(Use - to push notifications directly from your desktop during development)

Phonegap:
 https://github.com/phonegap/phonegap/wiki

BEM methodology for UI organization

http://bem.info/method/definitions/
BEM Architecture is mentioned on Adobe's topcoat.io page.

Sublime Text shortcuts

Toggle SideBar - Ctrl K,B
Layout - Alt Shft 1 (or Alt Shft 2) 

Friday, June 7, 2013

Phonegap related

http://www.tricedesigns.com/2013/01/18/my-workflow-for-developing-phonegap-applications/
One other advantage of PhoneGap build is that it lets designers/developers build mobile applications without having to install any developer tools. If you want to compile a PhoneGap app for iOS, but are on Windows – just use PhoneGap build and you won’t need Xcode or a Mac.
Good beginner's tutorial for Phonegap & Phonegap Build - http://www.youtube.com/watch?v=ZgUAT7CIhsY
Need to try this tutorial by Christophe Coenraets - http://coenraets.org/blog/phonegap-tutorial/

Monday, June 3, 2013

Rails: Get currently logged in user details

On Dev machine - rails console - the below gives the details of the logged in user (i.e. myself)

User.find(:all, :conditions => ["current_sign_in_ip != '' "])

(Found it here - https://groups.google.com/forum/?fromgroups#!topic/plataformatec-devise/tOKU_jcWNp0 )

Sunday, June 2, 2013

console settings (for PYR project)

To see all the routes displayed by "rake routes" (on Windows console):

1) rake routes | grep #

2) The output of the above command is fairly long, so, to see the whole output, we have to change the console's properties to something similar to the below 


Wednesday, May 8, 2013

html5please.com

html5please.com - advices on polyfills

HTML5 Please is a community project from the people behind HTML5 Boilerplate, Modernizr & CSS3 Please.


Saturday, April 27, 2013

DBNotes app

An example project that I did as part of my Rails training -

Code on Github - https://github.com/theAcadian/DBNotes

Demo - http://screencast.com/t/UXaEGpEBduO

Github's code analysis shows that code is 70% Javascript, 30% Ruby.
UI was done using Bootstrap components.

Friday, April 12, 2013

git commands 1

To follow a project on GitHub (for e.g. my DBNotes project) - copy the repository and take updates when needed

1. To copy a repository from github.com -
     we have to 'clone' it
     Suppose we run the below command (observe that 'current directory' is C:\Sites)
     C:\Sites > git clone git@github.com:theAcadian/DBNotes.git

Result of above command will be to create a directory called "DBNotes"

2. To get updates -
     C:\Sites > cd DBNotes
   C:\Sites\DBNotes > git pull git@github.com:theAcadian/DBNotes.git

3. To run the project (these are Rails commands) -
    C:\Sites\DBNotes > bundle install
    C:\Sites\DBNotes > rake db:migrate
    C:\Sites\DBNotes > rails s

 4. Open localhost:3000 in browser.

Friday, March 29, 2013

ifttt

Yahoo Pipes, ifttt.com (If This Then That)

Friday, March 22, 2013

SignalR & Shopping Cart

Markdown - tool for webwriters

Markdown is a text-to-HTML conversion tool (for web writers).

http://en.wikipedia.org/wiki/Markdown

Dino Esposito on "conventions"

Dino Esposito on "conventions"

"Conventions when not well explained, understood, and known, are quite dangerous. They do not favor readable code and even may create more issues than they would solve in development scenarios where awareness of conventions is not high. Too many, and too deep conventions, look undistinguishable from magic. Personally, I find acceptable the level of conventions you find in ASP.NET MVC; much less the extreme level of conventions you find in Web API."
This is from his article comparing Web API vs ASP.Net MVC Controllers vs WCF - https://www.simple-talk.com/dotnet/.net-framework/building-a-public-http-api-for-data/

Saturday, March 16, 2013

Javascript code - Bookmarklet that loads jQuery and then executes some jQuery commands

RunSomejQueryCommands() - Put whatever jQuery statements that need to be run after jQuery finishes loading
Note: the code loads jQuery into the page only if it is Not already there
Below example - in google.com page, clicking on the bookmarklet will load jQuery and put the text "jQuery loaded...Inserted via jQuery" into the Search box
javascript:if (typeof jQuery == 'undefined') { var s=document.createElement('script');s.setAttribute('src', '//ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js');document.getElementsByTagName('body')[0].appendChild(s);} var WaitTilljQueryLoads = function() { if (typeof jQuery != 'undefined') { clearInterval(timer);RunSomejQueryCommands(); }}; var timer = setInterval(WaitTilljQueryLoads);void(s);var RunSomejQueryCommands = function() {$('[name=q]').val('jQuery loaded...Inserted via jQuery');};

Saturday, March 9, 2013

Zen Coding (Plugins)

Plugins are available for Visual Studio, Notepad++ etc..

To use with Sublime Text - https://github.com/sergeche/emmet-sublime
(Github link has instructions on how to install and use it)


From http://en.wikipedia.org/wiki/Zen_Coding:

Zen Coding uses a specific syntax in order to expand small snippets of code, similar to CSS selectors, into full-fledged HTML code.[2] For example, the sequence
div#page>div.logo+ul#navigation>li*5>a
expands into
<div id="page">
        <div class="logo"></div>
        <ul id="navigation">
                <li><a href=""></a></li>
                <li><a href=""></a></li>
                <li><a href=""></a></li>
                <li><a href=""></a></li>
                <li><a href=""></a></li>
        </ul>
</div>
Intro by author - http://coding.smashingmagazine.com/2009/11/21/zen-coding-a-new-way-to-write-html-code/

EDIT (Mar 31 2013) -
Zen Coding is now Emmet - emmet.io
Documentation links -
http://docs.emmet.io/
Abbreviations Syntax - http://docs.emmet.io/abbreviations/syntax/
Actions - http://docs.emmet.io/actions/
Cheat Sheet - http://docs.emmet.io/cheat-sheet/

Wednesday, February 20, 2013

Mobile - HYBRID App-Building Tools (as of Dec 2012)

From the document - "Going Hybrid - How hybrid apps are accelerating HTML5 adoption" by BusinessInsider.com
  • PhoneGap
  • PhoneGap Build (Adobe)
  • Titanium (Appcelerator)
  • Kendo UI (Telerik)
  • Sencha

Tuesday, February 5, 2013

Real-Time Collaborative Editors


http://collabedit.com/97msj
http://piratepad.net/Arw934fJ2O
http://piratepad.net/ep/pad/view/Arw934fJ2O/latest

http://highscalability.com/

Tuesday, January 29, 2013

Javascript tool (drawing images and saving captions using Raphaeljs)


Admin mode: (can View/Add/Remove/Edit)
CODE: http://jsfiddle.net/theacadian/s4Xjg/56/
DEMO: http://jsfiddle.net/theacadian/s4Xjg/56/show

User mode: (can only View)
CODE: http://jsfiddle.net/theacadian/s4Xjg/54/
DEMO: http://jsfiddle.net/theacadian/s4Xjg/54/show

Libraries used - jQuery UI, Raphaeljs, jQuery
Made use of the "lasso" extension found in - http://stackoverflow.com/questions/4649293/lasso-tool-in-javascript


Saturday, January 19, 2013

Javascript - Lasso functionality 1 (jQuery extension, Canvas element)

Combined the below examples to draw & paint an area (polygon) selected by the user (Used HTML5 Canvas element to draw & paint the polygon; Have to try Raphaeljs to do the same thing)
http://stackoverflow.com/questions/4649293/lasso-tool-in-javascript
http://stackoverflow.com/questions/4839993/how-to-draw-polygons-on-an-html5-canvas


Tuesday, November 20, 2012

Javascript to detect Arabic characters

http://stackoverflow.com/questions/4446244/how-to-check-if-any-arabic-character-exists-in-the-string-javascript
(Other related links are in Diigo)

function hasArabic(character)
{
var arabic = /[\u0600-\u06FF]/; // http://stackoverflow.com/questions/4446244/how-to-check-if-any-arabic-character-exists-in-the-string-javascript
return (arabic.test(character));
}

function hasEnglish(s)
{
var english = /[a-zA-Z]/;
return english.test(s);
}

function hasNumbers(s)
{
var numbers = /[0-9]/;
return numbers.test(s);
}

Sunday, November 4, 2012

Important difference between 'function statements' and 'function expressions' in Javascript

EXAMPLE 1 - select the "RESULT" tab to execute the code
When the below code runs, "Yes" is printed, but not "No".
The reason is - before the code runs, "function statements" are interpreted and added to the execution stack/context, but the same is not done for "function expressions"
(Book - JavaScript Succinctly, Pg 102)

Therefore, the order in which functions are invoked & defined, becomes important when using "function expressions"
(Have to read more about this)


EXAMPLE  2 - using jQuery - $(document).ready()
Click on "RESULT" to see the result

Saturday, November 3, 2012

To enable .Net 3.5, 3.0,2.5 in Windows 8

http://helpdeskgeek.com/windows-8/install-net-framework-3-5-3-0-2-0-on-windows-8/

Excerpt:

"With Windows 8, .NET Framework 4.5 comes installed by default. Since .NET 4.5 is an in-place upgrade of 4.0, you can automatically run any applications that are geared towards .NET Framework 4.5 or 4.0. That’s fine, but what if you need to run programs that require older versions of .NET Framework? What’s the lowest version you can run?

In Windows 8, .NET 3.5, 3.0 and 2.5 come with the OS, you just have to enable it. This is really nice because you don’t have to go and download .NET 3.5 from the Microsoft website. In this post, I’ll show you how to enable the older versions of the .NET Framework in Windows 8......."

Facebook - developer links

Sunday, October 28, 2012

Saturday, October 27, 2012

Windows 8 App Framework diagram

This image is from Pluralsight's course - Introduction to Building Windows 8 Applications

Thursday, October 25, 2012

Native apps, Mobile Web apps, Hybrid apps


  • From http://icenium.com/community/blog/icenium-team-blog/2012/06/14/what-is-a-hybrid-mobile-app-
  • Native apps are built for a specific platform with the platform SDK, tools and languages, typically provided by the platform vendor (e.g. xCode/Objective-C for iOS, Eclipse/Java for Android, Visual Studio/C# for Windows Phone).
  • Mobile Web apps are server-side apps, built with any server-side technology (PHP, Node.js, ASP.NET) that render HTML that has been styled so that it renders well on a device form factor.
  • Hybrid apps, like native apps, run on the device, and are written with web technologies (HTML5, CSS and JavaScript). Hybrid apps run inside a native container, and leverage the device’s browser engine (but not the browser) to render the HTML and process the JavaScript locally. A web-to-native abstraction layer enables access to device capabilities that are not accessible in Mobile Web applications, such as the accelerometer, camera and local storage.

Sunday, September 30, 2012

Standup meetings, Asking, Mistakes - different opionion from Mike Hill

By "lose the 3 questions" - he is asking us to do away with the 3 questions - 'what did i do yesterday, what i am planning to do today, any blocking issues'

By "do not solve anything" - he says "we do not solve problems in standups, we raise them"

And he also mentions that the last 2 points - - "make asking a safe thing to do" , "make mistaking a safe thing to do",
which he calls "values", are "really critical". He says "we dont want our team not to make mistakes... we want to our team to make mistakes and repair them, in good cheer..." and adds "we want things to be pretty safe inside the team"

Full link to his talk - http://www.infoq.com/presentations/Individual-Coach

Tuesday, September 18, 2012

Referencing github from jsfiddle

From http://stackoverflow.com/questions/9841026/reference-github-file-in-jsfiddle :


-- And yes, it is, just get the raw copy. Take the URL (https://github.com/minitech/Sprint2/blob/master/sprint.js), addraw. before github, and remove the blob/ part:
https://raw.github.com/minitech/Sprint2/master/sprint.js
-- You can also just replace /blob/ with /raw/ and Github will 301 you to the correct url.

Sunday, September 9, 2012

Dialectic vs Debate

From http://en.wikipedia.org/wiki/Dialectic:
Dialectic (also dialectics and the dialectical method) is a method of argument for resolving disagreement that has been central to Indian and European philosophy since antiquity. The word dialectic originated in ancient Greece, and was made popular by Plato in the Socratic dialogues. The dialectical method is dialogue between two or more people holding different points of view about a subject, who wish to establish the truth of the matter by dialogue, with reasoned arguments.[1] Dialectics is different from debate, wherein the debaters are committed to their points of view, and mean to win the debate, either by persuading the opponent, proving their argument correct, or proving the opponent's argument incorrect – thus, either a judge or a jury must decide who wins the debate. Dialectics is also different from rhetoric, wherein the speaker uses logos, pathos, or ethos to persuade listeners to take their side of the argument.

Friday, September 7, 2012

Recursive lambda expression

void Main()
{
///////////////////////////////////////////////
// BOOK - C# Unleashed 4.0
// Chapter, Page - Chap 18 Events, Pg 859-861

/*
///////////////////////////////////////////////
// PROBLEM - recursive lambda expression
//
Func fac2 = n => n == 0 ? 1 : n * fac2(n - 1); // will not work
fac2(5).Dump();
*/

///////////////////////////////////////////////
// SOLUTION - initialize the lambda expression variable to null before assigning it the actual lambda expression
//
Func fac = null;
fac = n => n == 0 ? 1 : n * fac(n - 1); // will work
Console.WriteLine(fac(5));
}





Action finishedHandler = () => {
Console.Beep();
// Finished has been called: we can unhook both Tick and Finished now.
countDown.Tick -= tickHandler;
countDown.Finished -= finishedHandler;
};

Action finishedHandler; // this variable is unassigned!
Action __temp = () => {
Console.Beep();
// Finished has been called: we can unhook both Tick and Finished now.
countDown.Tick -= tickHandler;
countDown.Finished -= finishedHandler;
};

Monday, September 3, 2012

LINQPad - Adding references

To get access to HttpUtility -
From http://stackoverflow.com/questions/5015363/linqpad-4-doesnt-know-about-httputililty-how-to-resolve

1. You need to a reference System.Web.dll. Press F4 (References) and add a reference to that DLL.
2. Or, if you have an autocompletion license, just type your original query: HttpUtility.UrlPathEncode("Make sure"); and open the smart-tag that automatically appears. It will present a menu option to add the reference to System.Web.dll and import the System.Web namespace in a one-fell-click!

Friday, July 20, 2012

Taskkill

Taskkill /F /IM notepad.exe
/F => Forcefully terminate
/IM => image name of process e.g. notepad.exe, note*.exe (i.e. Wildcard '*' is supported)

Taskkill /?

Thursday, July 5, 2012

Fixed table header -using HTML and CSS only

From - http://www.scientificpsychic.com/blogentries/html-and-css-scrolling-table-with-fixed-heading.html

Excerpt from above (with some added formatting) -
It is possible to have a table with a scrolling body and fixed header with only HTML and CSS.

The solution is to use 3 tables.
    • An outer table consists of 2 rows
    • The top row contains a table for the heading and the bottom row contains a <div> containing the table for the scrollable body. 
    • The outer table and the <div> container have to be 25 pixels wider than the tables for the heading and the body to allow room for the vertical scroll bar. The column widths of the tables are defined through CSS.
Click on HTML tab and Result tab

Monday, May 28, 2012

javascript console - To get value of an element from an iframe

var d = ($$('iframe')[0]).contentDocument; d.getElementsByName('ID')[0]

Saturday, May 26, 2012

Title: Become a Javascript Console Power-User



To be able to access, from Console, elements in an iframe:
1. get id of iframe -  

2. use the cd() command -

Wednesday, May 16, 2012

DateTime.Now.CompareTo() method

var dt1 = DateTime.Now; while (DateTime.Now.CompareTo(dt1.AddSeconds(10)) <= 0) { }

Tuesday, May 15, 2012

Store Form Values bookmarklet

Drag & Drop the following bookmarklets to 'store' and 'load' a form's values.
Form Values - Store
Form Values - Load

Monday, March 19, 2012

Search stored procedure's text

http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

USE AdventureWorks
GO

--Searching for Empoloyee table
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'AND   OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GO 

Wednesday, February 29, 2012

Display all "display:none" elements

$("*").filter(function() { return $(this).css("display") == "none" }).css("display","")

Sunday, February 19, 2012

HTTP Caching 101, Sebastien Lambla

HTTP Caching 101, Sebastien Lambla (@serialseb) of CodeBetter.com


http://vimeo.com/36409207


Sunday, February 12, 2012

More examples using Correlated Subquery

More examples using Correlated Subquery-

In my opinion, although Method 1 has the extra select statement at the top, it conveys the logic of the query better than Method 2.

In Method 2, we basically have pushed the 'join' statement inside the outer query.


Method 1:



SELECT 
A.*, Challenge.* 
FROM 
(SELECT CE1.Challenge_ID,Question1,Question2,Question3,Question4,Question5,Question6
FROM Challenge_Enter as CE1
WHERE CE1.ID = 
(SELECT max(CE2.ID) 
From Challenge_Enter as CE2
WHERE CE2.CustomerID = 1123607 AND CE2.Challenge_ID = CE1.Challenge_ID AND Challenge_ID > 0)
) A
INNER JOIN 
Challenge 
on A.Challenge_ID = Challenge.ID



Method 2:



SELECT Challenge.*,CE1.Challenge_ID,Question1,Question2,Question3,Question4,Question5, Question6
FROM Challenge_Enter as CE1
INNER JOIN Challenge on CE1.Challenge_ID = Challenge.ID 
WHERE CE1.ID = 
(SELECT max(CE2.ID) 
From Challenge_Enter as CE2
WHERE CE2.CustomerID = 1123607 AND CE2.Challenge_ID = CE1.Challenge_ID AND Challenge_ID > 0)

Correlated Subquery, Common Table Expression example


Here are 3 different ways to write the same query.



The difference between using Self-Join method, CTE method, and Correlated Subquery method --> in Correlated Subquery method, I could use the ID column (which is the primary key column); but we cannot use ID in Group By (since it will be unique for each row), so we cannot use it in the Self-Join & CTE methods 
Hence, while writing Correlated Subqueries, we 'probably' have to focus on this --> returning the Primary Key of the outer query's table from the subquery
(This has to be verified by looking at examples)

From the table Challenge_Enter,
find out all the details of the latest Challenge to which the Customer 1123607 has entered into.

Self-Join
---------
SELECT
xx.*, Question1, Question2, Question3, Question4, Question5, Question6
FROM
Challenge_Enter yy
INNER JOIN
(SELECT
CustomerID, Challenge_Id, max(EnterDate) AS EnterDate
FROM
Challenge_Enter
WHERE
customerid = 1123607 AND Challenge_ID > 0
GROUP BY
Challenge_Id, CustomerID
) xx
ON yy.Challenge_ID = xx.Challenge_ID
WHERE
yy.EnterDate = xx.EnterDate

Common Table Expression
-----------------------
WITH
max_entries (Challenge_ID, maxEnterDate) As
(
SELECT
Challenge_id, max(EnterDate) maxEnterDate
from
Challenge_Enter
where CustomerID = 1123607 AND Challenge_ID > 0
group BY
Challenge_ID
)
SELECT
*
FROM
max_entries
INNER JOIN
Challenge_Enter
ON
max_entries.Challenge_ID = Challenge_Enter.Challenge_ID AND
max_entries.maxEnterDate = Challenge_Enter.EnterDate


Correlated Subquery
-------------------
SELECT
Challenge_ID,Question1,Question2,Question3,Question4,Question5,Question6
FROM
Challenge_Enter as CE1
WHERE
ID = (SELECT max(CE2.ID) From Challenge_Enter as CE2 WHERE CE2.CustomerID = 1123607 AND CE2.Challenge_ID = CE1.Challenge_ID AND Challenge_ID > 0 )

Followers

Blog Archive