Intersection On A Single Table
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 Complete Forum Thread with Replies
Related Forum Messages:
Within Table Intersection
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 Replies !
Combine Data In Single Row From Single Table
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 Replies !
Intersection
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 Replies !
SQL Union / Intersection
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 Replies !
Deadlock With Index Intersection
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 Replies !
Need Help Writing The Sp To Get The Intersection Of Resoruce Classes Please?
Hi guys can any one point me to the right direction in writing the stored procedure to get the intersection of records.. Here is the data in my table Code: Resourceclasses ------------------- ResourceClassIdNameType 1Resource Classes6 2All Resources5 4List of Resource5 5Union6 8Union16 7Intersection7 3Set15 6Intersect7 Type 5:Set of Resource classes Type 6:Union of Resource classes Type 7:Intersection of Resource classes ResourceclassMembers ---------------------- ResourceClassIdSubclassIdResourceId 301011 301001 301015 401001 401009 401004 540 530 640 730 760 740 830 870 650 750 I wanted to get the resources for each Resource class based on the Type of Resource class I am really stuck how to write the stored procedure. Any help is greatly appreciated. Thank you
View Replies !
Is It Possible To Use A Where Clause In Select Statments When You Use Intersection?
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 Replies !
Multiple Text Values In Intersection Cells Of Matrix Report
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 Replies !
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
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 Replies !
BCP A Single Table
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 Replies !
Deadlock On Single Table
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 Replies !
Single Table Restore
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 Replies !
Single Table Load
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 Replies !
Single Table Restoration
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 Replies !
Backup A Single Table
I have a table with about 5000000 Ml records that I need to copy to other database. What will be the easy way to do it? Can I backup a single table? Any suggestions!
View Replies !
Restore Single Table
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 Replies !
Restore A Single Table
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 Replies !
Two Different Datasets In A Single Table.
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 Replies !
Restore A Single Table
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 Replies !
How To Check If A Single Value Exists In A Table
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 Replies !
Select 1 Row From Each Group In A Single Table
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 Replies !
Will A Single Row Be Locked Or The Entire Table?
I am giving the following statement outside the begin tran block in a stored procedure: select * from table1 with (holdlock) where column1 = @colValue I have the following two questions regarding above situation: Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held? Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation? Thanks
View Replies !
Grant Access To A Single Table
So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app. Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table? Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is; Is there a quick and easy way of granting a login permissions to a single table only?
View Replies !
SIngle Table Backup/Restore
Hi guys, How to make single table backup / or Restore individual table from the Database backup file.SQL server 6.5 got an option to do this...Anyone know in SQL 7.0 or 2000 I appreciate ur immediate reply! Thanks Rajeev
View Replies !
Single Table BACKUP/RESTORE
Gentlemen: SQL 6.5 used to support single table backup and single table restore from a full backup. I find no such feature in SQL 7.0. Is it definitely no more supported? Best regards and ciao, Andrea
View Replies !
Slow SELECT On Single Table
SELECT * on a 4000 row table is taking more than 12 seconds. Other larger tables are not nearly as slow. I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%. How can I figure out why this is happening? What are some remedies? Thanks for your help.
View Replies !
Can I BCP A Single Colume Into An Exsisting Table?
I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example: table RES column STATUS needs to be updated with the same column from another table (PROP). I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast. Any help would be appreciated. Charlie.
View Replies !
4 Lookups Against Single Large Table
I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table. My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory. Any ideas/suggestions would be appreciated.
View Replies !
Mutiple Table Single Task
I have about 100 different tables that I would like to bring on nightly basis via integration services. SSIS will process the data and send on to its warehouse destination. Is it possible to use a single task to bring in all these tables from within SSIS? and can write failed records to flat files at the sametime? instead of defining a data flow for each table and dealing with hundreds of dataflows just have one task that loop through the list of tables. Wonder how warehouses fed by 100s of tables deal with this kind of scenario?
View Replies !
Splitting Single Row Of Table In To Two Rows
Hi All, I was wondering is there any way by which i can split a single row in my table into two rows in my table in design of my report. In my table there are 16 rows i cant design table with 16 rows as it will increase size of my report body & it will give problem while exporting. Instead what i want to do is , design a table with two header rows first header with first 8 columns & second with remaining 8 columns. The table should look like this //column 1 2 ID NAME AGE SEX ----------------------------------------------------------------------------------- 2 abc 10 M ----------------------------------------------------------------- 3 def 20 M Is it possible Thanks -- Darshan
View Replies !
Date Difference From A Single Table
Hi, I have a table VisitLog pkey customer_id pkey user_id visit_date visit_note Given both id's, I need a report that will list the visit interval. Assuming the rows are entered in ordered. the result should just display a single column with rows filled with date difference. Date Format in example below is dd/MM/yyyy 1, 1, '01/01/07', null 1, 1, '03/01/07', null 1, 1, '08/01/07', null The result should list difference in days. 2 5 Is it possible to do this in a SELECT Statement? Thanks, Max
View Replies !
Convert Single Record To Table
Hi all! I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like? Thanks.
View Replies !
Exporting A Single Table From A Database
Hello, Let me frist start saying that I am no SQL DB guru or have any great knowledge. I am sure this questions is the most basic question posted here. I would like to know how to export a single table from an SQL 2005 DB. I need to export this table from one SQL server to another. Just one table and its contents. Also, I woud like this exported table to be saved as a *.dat file? Thanks in advanced.
View Replies !
How To Copy Single Table From Database
Hello all, I created one Database in my server,n i wanted the same database in my local computer.So i copied it and restored in my database. Now i created another one new table in my server. I want to copy that table into my computer. How can i copy a single table in database and how can i restore(paste) that into my existing databse of my local compuer. Can u please help me to resove this problem. Thanks. rita
View Replies !
Get Child Value From A Single Table Structure
Hi I have a single table which holds a hierarchical a structure. I would like to get all the child ID's from one Stored Proc call. For example I give it an ID in the structure and I would like to give me all the child records that exist to the lowest level for each category found My table structure is [SvcID] [varchar](30) NOT NULL, [Ser_SvcID] [varchar](30) NULL, [SvcDesc] [varchar](100) NOT NULL I know where the root of the hierarchy is because Ser_SvcID will be null. Im not sure how to do this in one stored procedure call Hope the above makes sense Many thanks in advance
View Replies !
Which Is Best? Select From Joins Or Single Table
Hi, I am using a SP which one has lot of Joins(More than 10 tables). For every request from user SP executed, and the DB performance got slow. For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table. I thought if the user selects from single table means the performance will increase. I don't know whether this one is a right solution. I am very new to SQL server. Help me for this Problem. Thanks in advance.
View Replies !
SQL Single Query For Multiple Table
Hi friends, I have three table named as Eventsmgmt,blogmgmt,forummgmt.. Each table contain the common column named as CreatedDateTime.. I want to get the most recent CreationDateTime from these three table in single query.. Plzz help me its urgent Thanks
View Replies !
Help On Mutli Access To A Single Same Table
hi everyone, is that possible to access a table if the table is doing an insert command? im trying to insert data into a data while another function calling select statement on that same table. will the table be lock when its doing an insertion? or its free? sorry about the question structure. cheers, delete469
View Replies !
Single Table Query &<blush&>
I usually write (very simple) queries and export to Excel, then work my magic there. But there's got to be a better way. sampletable CUSTNO PARTNO ABC001 A1 ABC002 A1 ABC002 A2 ABC003 A2 I need to find all CUSTNO that have both PARTNO A1 and A2. select * from sampletable where PARTNO in('A1','A2') returns 2 rows. I really only want a list of unique CUSTNO ... "Only 1 customer has both partno A1 & A2". Thank you, --mark
View Replies !
Which Is Best? Select From Joins Or Single Table
Hi, I am using a SP which one has lot of Joins(More than 10 tables). For every request from user SP executed, and the DB performance got slow. For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table. I thought if the user selects from single table means the performance will increase. I don't know whether this one is a right solution. I am very new to SQL server. Help me for this Problem. Thanks in advance.
View Replies !
|
TRACKER
