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
ADVERTISEMENT
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
Apr 25, 2008
does any one know about sql intersect
View 2 Replies
View Related
Aug 7, 2007
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
View 1 Replies
View Related
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
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
Mar 19, 1999
Am I hallucinating or does SQL Server 6.5 not support the EXCEPT and INTERSECT operators?
View 1 Replies
View Related
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
Oct 3, 2007
hi
can any one help me to solve this question?
View 5 Replies
View Related
Jul 20, 2005
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
View 1 Replies
View Related
Oct 7, 2015
I am trying to update all records in #newtable that exist in #mastertable. I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable This was my syntax to show the records that exist in both tables, how can I change this to an update statement? The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value
So update #newtable set [alreadyexists] = 'yes'
select uno, mucha, pablo, company from #Newtable
intersect
select uno, mucha, pablo, company from #mastertable
View 5 Replies
View Related
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
View Related
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
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
Nov 14, 2007
Hi people!!!
First I'll introduce my situation.
I have the folowing tables:
1.- Table "Codes", DataBase "COD", server 1001
2.- Table "Codes", DataBase "COD", server 1002
Both servers run SQL Server 2000 Edition.
What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.
I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.
Anyone can help me?
Thanks,
Bob
P.S.: Please!!!!!!!!!
View 6 Replies
View Related
Jun 5, 2015
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:
Id
Source
FirstField
Secondfield
1
Source1
Product 3 name
Product 3 description
[code]...
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)
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 7 Replies
View Related
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
May 29, 2015
Link : [URL] .....
This provides a good example for my situation. In this example, you will see a Movie dimension with four attributes; Genre, Language, Movie, and Theme. I have a similar setup except mine is Top Level Hierarchy>Categories>Values which are all under the one hierarchy.
My Question: I have the dimension setup as a multi-value parameter in one of my reports. When I filter on a value in Genre and in Language, it provides all values from that genre and all values from that language. I really only want the values that include both.
Genre - Western: Movie1, Movie2, Movie3
Language - English: Movie2, Movie4, Movie5
If I filter on Western and English, I get Movie1-5 when all I really want is Movie2 only. Is there any way to have this do an Intersect within the same dimension or do I have to build each one out into its own dimension?
View 10 Replies
View Related
May 12, 2015
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
View 2 Replies
View Related
Oct 26, 2006
i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!
View 1 Replies
View Related
Dec 10, 2007
We are using a modeling technique called Anchor Modeling in our data warehouses. You can read more about the technique itself at our homepage http://www.intellibis.se, where we have published a fact sheet and a recently held presentation (TDWI European conference). One of the features with this technique is its simple way to historize data. This is done by having a fromDate column which together with the surrogate key will yield a unique combination. On the tables that has this kind of historization we add a primary key, which in turn will create a clustered index, with the following specification (surrogateKey asc, fromDate desc). This will physically order data on the storage media according to the specificed columns and ordering. Now I move on to create a "latest view" of this table which does a subselect to find the latest version for every surrogateKey using max(fromDate). Should not the optimizer now figure out that data is ordered so that the latest version always comes first for every surrogateKey, hence any sorting would be unneccessary? If I look at the actual execution plan after running a query that uses the view there is a sort in the plan, but the cost is always 0%. Does this mean that it did not sort the data, or that it did call a sorting routine, but it actually took very little time to do the sorting? If so, is there a reason that is has to do the sorting or could it have been left out by an even smarter optimizer?
I would also like to applaud the people behind the optimizer, since it will figure out which tables are in fact necessary to query and eliminate others, even if I have left joined them into the view I am using. This speeds up performance and makes anchor modeling feasible. Unfortunately optimizers from other vendors seem to have trouble doing this...
Regards,
Lars
View 2 Replies
View Related
Mar 11, 2008
I've been racking my brain all day and I finally decided to ask for help. I've got two tables with rows from the first that need to be sorted by the second. The problem is that the rows don't always exist in the second table. I've tried various forms of INNER, LEFT, RIGHT, OUTER, LEFT OUTER, CROSS, etc., etc., etc. and nothing (oh yeah UNION too). Every time I get close, I lose the records that don't have matches.
Something close-
SELECT A.IDDoc, B.First
FROM A
LEFT JOIN B
ON A.IDDoc = B.IDDoc
WHERE B.Dept = 'A'
ORDER BY B.First
Example Data
Table A
IDDoc Document
---------------------------
1 1467.doc
2 8722.doc
3 A47F.doc
4 88DQ.doc
5 ABCD.doc
Table B
IDDoc Dept First
----------------------------
1 A John
2 A Bob
3 A Ralph
4 A Diane
Results I Want
IDDoc First
-------------------
5 NULL
2 Bob
4 Diane
1 John
3 Ralph
Any help is appreciated. If I've posted in the wrong forum, please feel free to direct me to a better one.
Thanks in advance!
Jim
View 3 Replies
View Related
Oct 12, 2006
SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006
View 2 Replies
View Related
Mar 18, 2004
I am trying to select a record from a table where it has the smallest priority
how would you go about doing this
is there a cool sort command or is there a select command syntax that can do this
thanks
View 3 Replies
View Related
Aug 31, 2005
I've made this example and it loads a picture into a database. (MsSql )Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"Is it supposed to be like this or is it supposed to be reemoved after .net is finished??Code snip_______________________________________________________
Dim conn As New SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")
Dim cmd As New SqlCommand("Select * from tab_bild", cnn)
Try
conn.Open()
Dim myDatareader As SqlDataReader
myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Do While (myDatareader.Read())
Response.ContentType = myDatareader.Item("PersonImageType")
Response.BinaryWrite(myDatareader.Item("PersonImage"))
Loop
conn.Close()
Response.Write("Picture info succesfully retrieved")
Catch SQLexc As SqlException
Response.Write("Read failed, Reason: " & SQLexc.ToString())
End Try
End Sub________________________________________________________________Please can someone explain this for me or sort this out for me.All help is welcome even if its only points me too a direction.RegardsTombola
View 3 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 2 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 1 Replies
View Related
Jul 20, 2000
Hi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.
How can i change the sort order to 42.
I am using sql server 6.5
Thanks
Srinivas
View 4 Replies
View Related
Jan 13, 2000
We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?
View 2 Replies
View Related
Sep 21, 2000
I have a table that most of the data has the same value, but there are only a few that do not match that value. I want to populate a listbox with all values from the table, but I'd like to have the majority listed first, followed by the others (the few that don't matach). What's the best way to approach this with SQL?
Thanks.
View 3 Replies
View Related
Sep 20, 2000
HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?
HELP
View 1 Replies
View Related
Oct 5, 1999
Is there a query you can run against a 7.0 server to return the chracter set and sort order.
View 1 Replies
View Related
Oct 18, 1999
I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.
Thanks,
Rob.
View 2 Replies
View Related
