Tag: join

Microsoft SQL Server – Recursive Join

Using Microsoft SQL Server 2008 R2, I am trying to recursively join monthly data from 12 queries into 1 query and results table. I know how to join the results if I write out the query 12 times and join each one but the query is 16 lines long already, so I am looking for […]

FULL OUTER JOIN with temp tables

declare @Tax Table ( RowNumber int , FirstName nvarchar(50), MiddleName nvarchar(50), LastName nvarchar(50), SSN nvarchar(50), EmployeeCode nvarchar(50), TaxName nvarchar(50), Amount decimal(18,2), GrossPay decimal(18,2), CompanyId int, EmployeeId int ) INSERT into @Tax select row_number() OVER (PARTITION BY E.EmployeeId order by E.EmployeeId ) as RowNumber,FirstName,MiddleName,LastName,SSN,EmployeeCode,TaxName,TC.Amount,dbo.[GrossIncomeCalculation](E.EmployeeId) as GrossPay ,E.CompanyId,E.EmployeeId from Employee as E cross apply ( select TT.* […]

Capture current and previous values, across multiple fields in a self joined table

I’m trying to create a report that essentially redisplays the log tables for better readability, so all changes are captured in two columns, one Original Value and one Current Value column with a attribute column labelling what was changed. I’ve created a Temp Table for SQL Server for ease of testing. declare @T table (Utility_ID […]

SQL Combine two tables in a View

I have three tables (simplified table diagram above). From these three tables I would like to create a view that would be the combination of TableA and TableB (all rows from tableA and all from TableB with no duplicates). The catch is I don’t want the foriegn keys from TableA but instead I want the […]

Dynamically joining tables that may not exist

What’s a good way to dynamically create tables, then join together all the tables that I have created? Background I’m trying to write a stored procedure that collects data from various parts of the database and presents it for use. For example if I wanted to send invoices to both Alice and Bob, this stored […]

Table Join between Servers

I need to compare result sets from different servers. The tables I want data from share a primary key. Question: Because I have a primary key, can I query data using a simple join (ie ‘Server1.db1.dbo.table1.primarykeyfield = Server2.db2.dbo.table2.primarykeyfield) without having to use the stored proc (sp_addlinkedserver) ?

how to work with self join in sql?

I have 2 tables in sql 1. SELECT TOP 1000 [UserId] ,[UserName] ,[Password] ,[FirstName] ,[LastName] ,[Password_Expiry] ,[Modified] ,[ModifiedBy] ,[CreatedBy] ,[Type] ,[Privileges] FROM [Paramount].[dbo].[UserProfile] 2. SELECT TOP 1000 [Id] ,[UserId] ,[ClockInClockOutTypeId] ,[CreatedDate] FROM [Paramount].[dbo].[ClockInClockOut] i need to retrieve data form both tables like- [UserName]| Date [CreatedDate]|[CreatedDate](just time in) as TimeIn | [CreatedDate](just Time Out) as time […]

complex datatable should be created by select statement

I need to write an sql select statement and I got stuck. I am trying to write an sql for a complex GRID. The grid is supposed to be used to enter budget values. here is the way my grid is supposed to look like. you can see that there are MAIN rows, and each […]

SQL Server 2012 Many Parents Join Tables?

I have a single table (“#detail” in example below) that will have a child relationship to one of three tables (always one table and no others). For example, #detail rows ‘aaaa’ and ‘bbbb’ need a column value that relates to #metainfo1,row1 and row ‘cccc’ needs to be related to #metainfo2.row1 and ‘dddd’ needs to be […]

How to JOIN on my Result Set and a Third Table?

I’m trying to figure out how to perform a JOIN where I return all the records from my Result set (query 1) below where a.ssan = c.ssn: Query 1: SELECT a.memno, a.name, a.addr1, a.addr2, a.city, a.state, a.zip, a.sex, a.lname, a.ssan, b.addr1 as old_addr1, b.addr2 as old_addr2, b.city as old_city, b.state as old_state, b.zip as old_zip, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.