Combine Tables. [Leetcode Problem]

-

  1. Combine Tables. [Leetcode problem] 


Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

 

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

Ref: https://leetcode.com/problems/combine-two-tables/

 Solution:
 A common mistake would be to use INNER JOIN and not LEFT (or RIGHT) JOIN and ending up omitting records with no addresses but as it is asked to include those records as well, we will need to use LEFT (or RIGHT) JOIN.

Also, since these selected columns are unique among both the tables, I omitted the table prefix from all the columns in SELECT clause.


Query: 
Select Firstname, LastName, City, State 
FROM Person p
   LEFT JOIN address a
         ON p.PersonId = a.PersonId;

Comments

Post a Comment

Popular posts from this blog

Priority Scheduling Algorithm Java Program.

-
import java.util.Scanner; public class priority{ public static void main(String args[]) { Scanner s = new Scanner(System.in); int x,n,p[],pp[],bt[],w[],t[],awt,atat,i; p = new int[10]; pp = new int[10]; bt = new int[10]; w = new int[10]; t = new int[10]; //n is number of process //p is process //pp is process priority //bt is process burst time //w is wait time // t is turnaround time //awt is average waiting time //atat is average turnaround time System.out.print("Enter the number of process : "); n = s.nextInt(); System.out.print("\n\t Enter burst time : time priorities \n"); for(i=0;i<n;i++) { System.out.print("\nProcess["+(i+1)+"]:"); bt[i] = s.nextInt(); pp[i] = s.nextInt(); p[i]=i+1; } //sorting on the basis of priority for(i=0;i<n-1;i++) { for...
Read more

Implement UNIX system calls like ps, fork, join, exec family, and wait for process management (use shell script/ Java/ C programming).

-
//A small example to show how to use execvp() function in C. We will have two .C files , EXEC.c and execDemo.c and we will replace the execDemo.c with EXEC.c by calling execvp() function in execDemo.c . //EXEC.c  #include<stdio.h>  #include<unistd.h>  int main()   {        int i;       printf("This  is EXEC.c, called by execvp() ");        printf("\n");       return 0;   }  Now,create an executable file of EXEC.c using command  gcc EXEC.c -o EXEC //execDemo.c #include<stdio.h> #include<stdlib.h>  #include<unistd.h>  int main()  {     //A null terminated array of character      //pointers       char *args[]={"./EXEC",NULL};       execvp(args[0],args);     /*All statements are ignored after execvp() call as this whole process(execDemo.c) is replaced by ...
Read more

Implement a class CppArray which is identical to a one-dimensional C++ array (i.e., the index set is a set of consecutive integers starting at 0) except for the following : 1. It performs range checking. 2.It allows one to be assigned to another array through the use of assignment operator. 3.It supports a function that returns the size of the array.

-
#include<iostream> #include<math.h> using namespace std; class array {      int a[10],b[10],i,j,temp,n;           public:             void accept();             void display();             void range();             void sort();             void exchange();             int size(); }; void array::accept() {     cout<<"How many Elements you want to insert into the array=\n";     cin>>n;     cout<<"Enter the array Elements=\n";     for(i=0;i<n;i++)        {             cout<<...
Read more