Intersection On A Single Table
Nov 12, 2004
Hi all !
I have a table with no keys (temp table) which looks like this :
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087
001|A|.234
001|B|.032
As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.
Anyone have an idea how I can do it ?
View 7 Replies
ADVERTISEMENT
Jun 3, 2008
Hello all,
I'm new to SQL and my Teach Yourself in Blah Blah Blah book doesn't index anything helpful to my task. I have a single
TABLE ratings (rid INTEGER PRIMARY KEY, mvid INTEGER, uid INTEGER, rating INTEGER)
with about 100,000,000 rows. I would ultimately like to be able to select rows that are the intersection of two mvid on uid or vice versa -- that is, all rows whose uid is present in the set of rows where mvid=a AND in the set where mvid=b (or vice versa). Something like:
ridmviduidrating
----------------------
1123763
2123314
3123442
4211652
5211764
6211445
7535933
8535762
SELECT * FROM ratings INTO @temp_table WHERE mvid=123
SELECT * FROM ratings WHERE uid IN @temp_table AND mvid=211returns
5211764
6211445
FIRST, I don't know how to accomplish this intersection at all and the only idea I've had is querying the first clause of the intersection and storing it in a temporary/variable table then selecting from that and the original table for the second clause (not that I know how to do this), but I'm afraid this will be very inefficient for the >O(n^2) queries I must perform, so SECOND, should I build derived intersection tables from the results so as to have ~O(1) when repeating the queries later, or will SQL be doing sufficient behind-the-scenes magic? Is there an efficient SQL statement that could generate table(s) of the set of intersections if necessary? Thanks
View 20 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Aug 9, 2006
Parwej writes "i have one table, fields are
cs_call_id , fromid , toid , body
----------------------------------
101 1 2 fg
101 3 2 df
101 4 2 fd
101 4 1 dfd
101 3 1 w3e
i would like to find
conversation between
2,3,4
means find the row or 2,3,4 (fromid or toid)"
View 2 Replies
View Related
Jul 23, 2005
I'm trying to build dynamic sql from a string passed by a callingapplication. Let's assume for this discussion that the user can pass astring of letters with these logical operators ("and", "or", and "andnot") seperating them. Each letter can be rebuilt into a sub querythat will search for people in a table by their middle initial. Forexample,X and Y(select SSN from tblPerson where MiddleInitial = 'X') UNION(select SSN from tblPerson where MiddleInitial = 'Y')This seems pretty easy with the and operator (UNION) but how can I do"or" and "and not"? I remember from a SQL class I had 10 years agothat there was an INTERSECTION operator but it appears that T-SQLdoesn't support it. The closest option is EXIST and NOT EXIST butthese can not be simply inserted between two sub queries (I think),they require the user of a where clause. It would obviously work inthe example above but in the more complecated example below it wouldn'tbe an easy replacement of the operators and sub queries.X and (Y or J) and not LSo, the bottom line is that I have no problem replacing the letterswith the appropriate sub query but I'm looking for a way to replace thelogical operator with SQL syntax that will mimic the logical operator.I hope this makes sense. : )Will Wirtz
View 7 Replies
View Related
Nov 8, 2002
Hi,
I have a deadlock situation with index intersection.
Connection1:
Updates a table T1 which has indexes I1 and I2
Connection2:
Selects from T1 (joining many other tables), SQL Server uses index intersection of I1 and I2.
The deadlock error log shows,
Connection1 has exclusive lock on I1 and waits on exclusive lock request on I2
Connection2 has a shared lock on I2 and waits on shared lock request on I1
Connection2 is selected as the deadlock victim.
The transaction isolation level is explicitly set to READ COMMITTED in ADO connection object.
I don't understand why SQL server needs to hold on the shared lock on I1 while waiting for a shared lock on I2.
Is there any index hint to prohibit SQL server from using index intersection other than explictly using INDEX=I1 or I2.
Thanks
View 3 Replies
View Related
Aug 14, 2006
Hi Can anybody explain me what is the difference between inner join and intersection?
I prepare a query but it shows the same results then why we need two functions like this to perform same operation
Thanx-Nagu
View 4 Replies
View Related
Apr 26, 2008
Lets say that Dealers have ZipCodes, and that a Dealer can have more than one zipCode, and we want the list of dealers that have both 90210 and 90211 zip codes. BUT we don't want any dealers that have only one of the two ZipCodes in question
What I want to do is something like this
Select DealerID from DealerZips where Zip = '90210'
intersection
Select DealerID from DealerZips where Zip = '90211'
but I get this error msg:
Line 2: Incorrect syntax near 'intersection'
The following sql is silly, but it does run without error
Select DealerID from DealerZips
intersection
Select DealerID from DealerZips
So I am pretty sure my problem is with the Where clauses.
help!
View 3 Replies
View Related
Apr 17, 2014
Using SQL Server 2008 R2
I'm trying to create a report and chart for a a manufacturing resource's activity for a given period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- Create tables to work with / Source and Destination
CREATE TABLE #Jobs
(
ResourceID int
,JobNo VARCHAR(10)
,startdate SMALLDATETIME
,enddate SMALLDATETIME
[Code] ....
Below is some sample data
|--------------------------J1------------------------------------| running
|----D1-----| |-------D2-------| down
|--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running
|----D1-------| down
|-----------------J1-----------------------| |----D1-------| result
View 4 Replies
View Related
Jul 24, 2015
SSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
View 4 Replies
View Related
May 30, 2007
I'd like to create a report with the folloiwng format:
DATE1 DATE2 DATE3 DATE4 DATE5 [fixed 5 dates across the top, from today to T+5]
THING1 x x x x
THING2 x x x x
THING3 x x x x
THING4 x x x x
my raw data looks like this:
THING1, DATE1, TEXT VALUE 1
THING1, DATE2, TEXT VALUE 2
&c&c.
Now: there may be 0, 1 or several (by which I mean 2-5 max) text values to display at each intersection. If there are zero I'd like it to be blank, if there are one or several, i'd like to display them in a little list within the cell.
Is this possible?
View 1 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
View 5 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT Â
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
  [PSDCode]
, PV.ValuationEffectiveDate
  [EffectiveDate]
, PV.PropertyListAlterationDate
  [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
View 11 Replies
View Related
Sep 29, 2014
i have 6 table in SQL Server and i have created one view and create single table by linking all the table,now i want to join two column like
Column A and Column B = Column C
e.g
A B C
Atul Jadhav Atuljadhav
Vijay vijayvijay
in above exambe column A having firstName and Column B having second name and i want to join this two column in C column "atuljadhav" and if column B is blank then it join A value tow timestriger code as it is auto update column and every time (update, append, modify, delete) it should be update automatic
View 5 Replies
View Related
Nov 1, 2007
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View 7 Replies
View Related
Sep 28, 1999
I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why.
The table only had a few rows of data and only had a few small dependancy tables.
The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long.
Here is the BCP command line that I ran :-
bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n
Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one.
Failing that, is there any way of scripting the transfer of a table from one server to another.
Many thanks.
Dave
View 2 Replies
View Related
Mar 18, 2002
Does anyone know of a way to restore a single table using SQL 7.0? I know that I can build a dummy database and do a database restore, then copy the table from database to database. But....was wondering if there is a way to restore a single table from a backup. Is losing the ability to restore a single table one of the 'features' of 7.0?
View 1 Replies
View Related
Feb 15, 2001
Does anyone know if I can restore an individual table in SQL 7.0?
I know I can DTS from a copy of the database but how can I restore a table directly from a backup file.
Thanks
Ben
View 2 Replies
View Related
Oct 13, 2000
How can I restore just one table from the full backup? It was very easy with 6.5 but I am not sure whether it's possible with 7.0 and if it is possible, how to do it.
Thanks in advance.
View 2 Replies
View Related
Mar 15, 1999
I thought I had posted this question already, but didn't see it in
the list. I apologize if this is a repost.
I am running SQL Server 6.5 SP 4.
I am attempting to load a single table from backup, but continually get
the error about schemas not matching. Interestingly, it comes back with
a status 4, and not the status 3 indicating a mismatch on Ansi_Padding.
The statement I am using is
load table demhist from internal_tape with file=5,nounload
I have tried creating the table from scratch and using select * into...
I have tried both above with both settings of Ansi Padding. The table
I am trying to load contaings char columns which allow nulls. I have
experimented with loading 2 other tables. One of the other tables loads
and one doesn't. The one which does not load also has char columns which
allow nulls, while the one that does load does not have char columns
which allow nulls.
Is it a known problem or limitation on the table load that it cannot reload
tables that contain char columns which allow nulls?
View 4 Replies
View Related
Dec 15, 1998
Hi!
This morning, I needed to restore 1 table to a database because it had
accidentally been deleted (don't ask). Each time I tried to restore the
single table, I would select TOOLS| DATABASE BACKUP/RESTORE| RESTORE
tab from Enterprise Manager, then I select SINGLE TABLE, but am only
given the existing table names as choices to restore. This happens when
I select to restore 'FROM DEVICE' as well.
Am I doing something wrong or is this how MS SQL6.5 is supposed to
work? Am I only able to restore a corrupt table and not a missing
table? I ended up restoring the entire database...
Thanks for your help!
Toni Eibner
View 2 Replies
View Related
Sep 10, 2001
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup?
Thanks,
Christine
View 1 Replies
View Related
Apr 16, 2008
Is there a tool out there to backup only one table at time
in SQL Server 2005 ?
View 9 Replies
View Related
Dec 10, 2007
Is there a way to back up one table at a time?
View 2 Replies
View Related
Jul 20, 2005
We have one user who enters a transaction and then does a single rowupdate (updates all columns but only one is changing - this is due tothe way our sql is generated in the application), at this pointanother user enter a transaction and tries to update the same row (heunderstandably has to sit and wait while he is blocked by the originaluser). The original user then updates the same row again – at thispoint the second user is chosen as a deadlock victim and killed. If Itry and recreate this with any other tables(or pubs) I get my expectedbehaviour of the original user just doing 2 successful updates and thesecond user then completing his update once the original user haseither committed his changes or rolled back. The query plan indicatesthat a drop and insert of the row is happening (this is not the casewith any other tables where we get our expected behaviour). This onlyhappens when the index is clustered - if we use a non-clustered indexit does not occur.Is this expected behaviour? it seems dangerous to me as the firstuser has not commited or rolled back his updates. It was onlyhighlighted by a fault in our application that caused the secondupdate to be executed.I have some thoughts about it being something to do with a row lockbeing relased due to a delete / insest of the row in the second update(we see this in the execution plan).....Any help much appreciated as I am struggling to get my head round howthe second user was ever able to get hold of the resource.
View 1 Replies
View Related
Jan 18, 2008
Hi,
I need to display the datas in a table format. I have placed a table and now i need to use two different datasets for a single table. Those two different datasets are generated based on two different stored procedures. Those stored procedures retrieve data from two different sql tables. Can any one explain me how to use two different datasets for a single table. As far i have tried i was able to fetch only the first record of the other dataset. But i need to fetch all the records of both the dataset. Thanks in advance for any help.
Thanks,
Sangeethaa
View 11 Replies
View Related
Aug 25, 2015
I was writing a query to get the age and the retirement year for all the employees.And thought of using while loop so that I don't have to write IF conditions or case statements for all the ages.
I'm using the AdventureWorks2012 database.And the actual table looks like this.
SELECT * FROM HumanResources.Employee
*NOTE:- These tables are not the complete tables.
BusinessEntityID         JobTitle                 BirthDate       MaritalStatus     Gender
      1                Chief Executive Officer           1963-03-02  S              M
      2         Vice President of Engineering         1965-09-01        S                     F
     3             Engineering Manager          1968-12-13        M              M
     4             Senior Tool Designer              1969-01-23           S             M
[Code] ...
And after I wrote the query to get the age and the retirement year of all the employees I got 70 tables for all the ages from 30 to 70. As the starting age is 30 and the last age is 70 in the table.So,I just want to know how I can settle all the tables into a single table as a sinle result and not as multiple results.
The query for age and retirement year....
DECLARE @Counter INT
DECLARE @Duration INT
DECLARE @Result DATE
SET @Counter=(SELECT MIN(DATEDIFF(YY,BirthDate,GETDATE()))FROM HumanResources.Employee)
SET @Duration=30
[Code] .....
And the result tables.
BusinessEntityID   JobTitle   BirthDate   AGE   MaritalStatus   Gender   Retirement Year69  Â
Production Technician - WC60Â Â Â 1985-05-07Â Â 30Â Â SÂ Â Â MÂ Â Â 2045-08-25 22:36:38.160115Â Â
Production Technician - WC50Â Â Â 1985-07-01Â Â 30Â Â Â SÂ Â Â FÂ Â Â 2045-08-25 22:36:38.160133Â Â
Production Technician - WC40Â Â Â 1985-02-04Â Â Â 30Â Â Â SÂ Â Â MÂ Â Â 2045-08-25 22:36:38.160144
  Â
[Code] ....
And it goes like this for 70 times. So just want to know how I can merge those 70 tables into a single table.
View 2 Replies
View Related
Feb 15, 2006
Hi,
I have a problem. For some reason I needed to restore a single (large) table that shares a filegroup with other tables. I have a full backup of the database. How can I restore my particular table?
-- Srinivas
View 3 Replies
View Related
Jan 3, 2008
Is it possible to restore a single table in SQL. If so does anyone have any idea how to go about it. Thanks in advance for the help.
View 1 Replies
View Related
Aug 19, 2006
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID AuctionID BiderName BidAmount
1 4005 joeblow 100.00
2 4005 janedoe 101.00
3 4005 joeblow 107.00
4 4006 joeblow 100.00
5 4006 janedoe 105.00
6 4006 joeblow 106.00
I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3 4005 joeblow 107.00
6 4006 joeblow 106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
Thanks in advance for your help.
View 4 Replies
View Related
Sep 14, 2006
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
View 2 Replies
View Related
Feb 7, 2007
I would like to get single values from a huge sql server and put it into a table. let's say 4 by 4. How do I do that?
I have a connection string with a select statement that will only return a value. But, I do not know how to put that value into a table.
Thank you.
View 4 Replies
View Related
Dec 28, 2007
Does some know of an easy to understand way to encrypt a single table column? I have never worked with encryption before.
View 1 Replies
View Related
