SQL Server 2012 :: INTERSECT Over INNER JOIN

Jan 13, 2014

These two T-SQL statements return the same results.

If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN

-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER

USE AdventureWorks2008R2;
GO
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;

USE AdventureWorks2008R2;
GO
SELECT DISTINCT Production.WorkOrder.ProductID
FROM Production.Product
INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID

View 9 Replies


ADVERTISEMENT

Is Inner Join An UNION / INTERSECT / EXCEPT

Oct 3, 2007

hi
can any one help me to solve this question?

View 5 Replies View Related

Simple JOIN, INTERSECT Query

Apr 25, 2006

Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid

View 1 Replies View Related

UNION / INTERSECT / EXCEPT In SQL Server 2000

Sep 21, 2006

Hi,I'm coming back to Sql Server after 4 years away, using other RDBMS,and there's a few things I'm struggling to remember how to do (if Icould do them in the first place...)Main amongst those is EXCEPT syntax.In DB2, if I have two sets of data and I want to exclude the second setfrom the first, I can do:SELECT col1, col2, col3, ... colNFROM table1EXCEPTSELECT col1, col2, col3, ... colNFROM table2;But SQL Server balks at this. I've had a quick look in the T-SQL helpfor EXCEPT, but I didn't find that particularly enlightening. Anypointers as to how I should be doing this?ThanksJames

View 5 Replies View Related

SQL Server 2008 :: Queries To Simulate INTERSECT And EXCEPT But With Records In Same Table

Jun 4, 2015

Here's the scenario. I have a table (let's call it MyTable) that consists of four fields: Id, Source, FirstField, and SecondField, where Source only takes one of two values: Source1 and Source2.

The records in this table look as follows:

I need to return, using 3 different T-SQL queries:

1) Products that exist only in Source2 (in red above)

2) Products that exist only in Source1 (in green above)

3) Products that exist both in Source1 and Source2 (in black above)

For 1) so far I've been doing something along the lines of

SELECT * FROM MyTable WHERE Source=Source1 AND FirstField NOT IN (SELECT DISTINCT(FirstField) FROM MyTable WHERE Source=Source2)

Not being a T-SQL expert myself, I'm wondering if this is the right or more efficient way to go. I have read about INTERSECT and EXCEPT, but I am a little unclear if they could be applied in this case out of the box.

View 5 Replies View Related

SQL Server 2012 :: Two Tables - Join One To Many

Oct 11, 2015

I have two tables, INCIDENT and PEOPLE

INCIDENT has two fields, ID and Date
PEOPLE has three fields, INC_ID [foreign key to INCIDENT.ID], PersonIteration, Complaint

There is always only one incident, but there can be one or more people involved in each incident.

I'd like to retrieve incidents, but have it include information from all involved person on one line.

No matter how I have tried to join these tables, I am ending up with one record each for each person involved in an incident, like so

ID PersonIteration Complaint
1 1 Head
1 2 Neck
1 3 Shoulder

I would like to join in this fashion:

ID PersonIteration1 Complaint1 PersonIteration2 Complaint2
1 1 Head 2 Neck

Is there any way to do this?

View 5 Replies View Related

SQL Server 2012 :: Syntax On A Join

Oct 23, 2015

What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.

SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear

[code]...

View 9 Replies View Related

SQL Server 2012 :: Join On Different Data Types

Jun 11, 2014

We have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.

Casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.

We are talking a few hundred million rows of data in each table.

Temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.

View 6 Replies View Related

SQL Server 2012 :: Multiple ON Clauses In One Join?

Jun 18, 2014

I came across this structure today and haven't seen it before:

SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2

I can follow along until the second "ON".

View 9 Replies View Related

SQL Server 2012 :: Adding A Conditional Join?

Aug 16, 2015

I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).

the join is like -
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date
FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)
ON User_Count_Id = Analyst_Id_fk
WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana
ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk
)

Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.

View 4 Replies View Related

SQL Server 2012 :: Conditional Join Between Two Tables

Oct 4, 2015

I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.

Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.

What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?

--- First Create two tables
Create table tabA
(cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100))
Insert into tabA values (NULL,'A1','A2')
Select * from tabA
create table tabB

[Code] .....

View 7 Replies View Related

SQL Server 2012 :: Product Join - How To Avoid

Oct 27, 2015

This query below is giving product join for me, is there a way to avoid this?

SELECT DISTINCT a.RevID, indexdate, transadate
FROM temp1 AS a
INNER JOIN temp2 AS d ON transdate BETWEEN indexdate-60 AND indexdate+60
)

View 5 Replies View Related

SQL Server 2012 :: Join Two Dynamic Pivot Tables

Dec 11, 2013

I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.

I can't set them both up as CTEs and query, the statement gets truncated.

I can't use a temp table because those get dropped when the query finishes.

I can't use a real table because the insert statement gets truncated.

Do I have any other good options, or am I in Spacklesville?

View 7 Replies View Related

SQL Server 2012 :: How To Get Primary Key Value If Not Present In Foreign Key Via Join

May 4, 2014

I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:

SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)

This Query returns the following result:

SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April

[code]....

which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.

View 9 Replies View Related

SQL Server 2012 :: QEURY - Join Multiple Columns

Sep 1, 2015

DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))

INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL

[Code] ....

Need query to get the Result as below

PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL

By using case statement we can retrieve the data, but looking for alternate way to do it.

View 4 Replies View Related

SQL Server 2012 :: Multiple Level Join On Same Table?

Oct 10, 2015

i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.

SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case

[code].....

View 1 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Correlated Query To INNER JOIN Or Window Function

Mar 31, 2015

I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.

How can I convert this

SELECT TOP 5
(SELECT SUM(lt2.col3)
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result
FROM dbo.MyTable1 t1
... to an inner join or a sql2012 window function?

By the way, I just added the TOP 5 myself while testing. It's not in the main query.

View 9 Replies View Related

SQL Server 2012 :: Attempting To Replace Not In With Left Outer Join

Apr 15, 2015

I'm attempting to improve a vendor created query.

Here is the original -

SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1

[Code] ....

If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.

View 9 Replies View Related

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

SQL Server 2012 :: Capture Statement From Missing Join Predicate Event?

Jan 27, 2015

After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.

The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.

I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run

select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169

it gets me the SQL being run at that time not the one that causing the event.

View 3 Replies View Related

SQL Server 2012 :: How To Join Tables To Get Only Record With Specific Field Value In A Table

Feb 6, 2015

I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.

There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).

All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”

I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.

That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?

View 6 Replies View Related

SQL Server 2012 :: Generate Flag To Check Whether Join Condition Match Or Not

Oct 12, 2015

I want to join 2 tables, table a and table b where b is a lookup table by left outer join. my question is how can i generate a flag that show whether match or not match the join condition ?

**The lookup table b for column id and country are always not null values, and both of them are the keys to join table a. This is because same id and country can have multiples rows in table a due to update date and posting date fields.

example table a
id country area
1 China Asia
2 Thailand Asia
3 Jamaica SouthAmerica
4 Japan Asia

example table b
id country area
1 China Asia
2 Thailand SouthEastAsia
3 Jamaica SouthAmerica
5 USA America

Expected output
id country area Match
1 China Asia Y
2 Thailand SouthEastAsia Y
3 Jamaica SouthAmerica Y
4 Japan Asia N

View 3 Replies View Related

SQL Server 2012 :: Set Based Method To Insert Missing Records Into Table - Right Join Not Work

Apr 24, 2014

I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)

Table A could look like the following where the multiple employees could have multiple types and multiple stages.

EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2

Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.

Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4

So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.

I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.

I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.

My count would look something like this:

EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2

So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.

The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.

View 9 Replies View Related

SQL Server 2012 :: Have Conditional Join / Union Based On Parameters Passed To Stored Procedure

Jul 15, 2014

I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.

I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.

View 6 Replies View Related

Intersect?

Feb 2, 2004

Dose SQL 2000 support the intersect function? If so....could someone show me an example. If not...how do you work around the problem.

Thanks,
Trey

View 1 Replies View Related

Intersect In Sql

Apr 25, 2008

does any one know about sql intersect

View 2 Replies View Related

SQL Server 2012 :: Join To Find All Records From One Table That Do Not Exist In Other Table

Apr 29, 2014

I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.

What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:

'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002

So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".

View 7 Replies View Related

Intersect Woes.

Oct 18, 2006

I want to be able to intersect many tables. I am building my query from vb code in asp .net based on key fields entered in a search engine box.my query should look like this, which will return the rows that will have the values var, var_2, var_3 in any columns.  All three must be in a row for it to be a hit.  I cant get this to work in ms sql.  I don't know if it supports this feature.select * from t where column1 Like '%var%' or column2 like '%var%' or column3 like '%var%'intersect select * from t where column1 Like '%var_2%' or column2 like '%var_2%' or column3 like '%var_2%'intersectselect * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%'  I also googled around and found a where exists... But cant seem to figure out how to do multiple tables:select * from t where exists (select * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%') i would like to add multiple conditions to the where exists table.  Could anyone please tell me what I am doing wrong.

View 2 Replies View Related

Sort Of Intersect

Feb 27, 2008

I have: 4 tables and 1 table variable.
CCenters (ID, Name)
Campaigns (ID, Name)
Rel (ID, CCenterID, CampaignID) - [many to many]
and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user

performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID
FROM Rel
INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID
 But what I really want are the common centers to the selected campaigns.
 Thanks

View 1 Replies View Related

&#34;Intersect&#34; Operator ...

Aug 22, 2001

How to use "INTERSECT" Operator with SQL Server 2000 ?
I tried this :

select ...
Intersect
select ...

but it doesn't work ! I have an error message : "Incorrect syntaxt near the keyword intersect" !

View 1 Replies View Related

EXCEPT And INTERSECT Operators

Mar 19, 1999

Am I hallucinating or does SQL Server 6.5 not support the EXCEPT and INTERSECT operators?

View 1 Replies View Related

Usage Of INTERSECT - Keep Getting Errors :(

Dec 21, 2003

Hi everyone, I'm trying to run the following script but it keeps giving me errors (Syntax near the work INTERSECT).


SELECT [stuff], [things], [others], [everyone]
FROM [TABLE] JOIN (

(SELECT [stuff]
FROM [TABLE]
WHERE [item] = '1' )

INTERSECT

(SELECT [stuff]
FROM [TABLE]
WHERE [item2] = '1' )

) temp ON temp.[stuff] = [stuff]


NOTE: If I replace the word INTERSECT with UNION it works fine, just that I'm wanting an intersect not a union!!

Can anyone help me out with this?

Andrew

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved