Trying To Identify A Unique Record On Two Separate Tables
Oct 31, 2007
Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage.
The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:
ContentGUID > AAAA
Version > 1
ContentGUID > AAAA
Version > 2
And the other table (ContentsUsage)
ContentGUID > AAAA
Version > 1
ContentGUID > AAAA
Version > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index.
Contents
RowGUID (unique key)
ContentGUID
Version
Views
Rating
Comments
................
RowGUID
ContentGUID (unique key)
Version (unique key)
Description
.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble.
Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
Division Type Section Location ModificationDate ------- ----- ------- -------- --------------- 3 4 2 Los Angeles 2/1/05 3 4 2 New York 2/4/05 4 5 1 Los Angeles 2/4/05 3 4 2 Seattle 2/7/05 4 5 1 Dallas 2/6/05 3 4 4 London 2/3/05
I need to remove duplicate records that have the same division,type,section pair by slected the most recent modification date and keeping the data in the rest of the columns. The results of what I want to do would look like:
Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.
Does anyone know how to identify the hottest, most active tables in adatabase?We have hundreds of users hitting a PeopleSoft database with hundredsof tables. We are I/O bound on our SAN, and are thinking of puttingthe hottest tables on a solid state (RAM) drive for improvedperformance. Problem is: which are the hottest tables? Would like todo this based on hard data instead of developer/vendor guesses.Any suggestions are much appreciated.
The database I'm currently working with is very old and some of thetables, SP, and views are not being used. I'm looking for a way toidentify what items are no longer in uses, or what items arecurrently in use.
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.
In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.
Customer ID CustomerAddress Phone Email Account Balance Currency js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR
In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).
As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.
I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?Â
we run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.
I am looking at two tables and would like to learn which, if any objects on same db updates these tables.  I'd even live with any references to these tables. I already know sql agent is out of the picture. I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.
Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs?  What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?
In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts.Â
I don't know that I've ever seen a way to script all triggers before. Procs I can easily script from the object explorer details. But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.
I HAVE THREE TABLE GEN, PARTY, TXN (GEN & PARRTY IS MASTER TABLE AND TXN IS TRANSACTION TABLE) IN TXN TABLE - STORE ALL DATA IN WHICH SAME VOUCHER NUMBER IS REPEATED ACCORDING TO JOURNAL ENTRY) NOW PROBLEM IS THIS AT THE TIME OF DISPLAY THE RECORD ON SCREEN I HAVE TO SHOW ONLY THE UNIQUE VOUCHER NUMBER ONLY..
PLS GUIDE IN THIS QUERY HOW CAN I GET THAT RESULT.
SELECT DISTINCT TXN.VOUCHER, GEN.GLNAME, PARTY.PARTYNAME, TXN.DOCDATE, TXN.GLCODE, TXN.PARTYCODE, TXN.AMOUNT, TXN.VTYPE, TXN.CBCODE, TXN.ADJ, TXN.DC, TXN.NARR FROM (TXN LEFT JOIN GEN ON GEN.GLCODE=TXN.GLCODE) LEFT JOIN PARTY ON PARTY.PARTYCODE=TXN.PARTYCODE WHERE TXN.VTYPE='JV' ORDER BY TXN.VOUCHER
I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!
I am new to T-SQL and triggers Any help will be appreciated
I am trying to change this code to insert firstname, surname (taken from employee table on db A) to firstname, surname on customer table of DB B but also create cust_id on customer table and DB B. currently I am getting all rows of customer.cust_id filled with the same data whenever a new data is inserted into (firstname,surmname of employee table)
Create trigger gen_cust_id ON employee for insert AS Update customer SET cust_id =( SELECT Replicate('0',(4-DATALENGTH(CONVERT(varchar(10),i.id)))) + Convert (varchar(10),i.id) + Substring(i.lastname,1,3) + Substring(i.firstname,1,1) from employee C INNER JOIN inserted i on i.id=c.id) from employee C INNER JOIN inserted i on i.id=c.id
How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:
I am trying to make a where clause using the sql server unique record id that is set automatically when a record is inserted. It is the record id that is unique for one table. In a where clause. What type should go where the _______ is below? Here is the program <%@ page language="vb" %><%@ reference page="try7.aspx" %><% @import namespace="system.data"%><% @import namespace="system.data.sqlclient"%> <html> <head> <title> </title> <script language="vb" runat="server"> sub page_load(sender as object,e as eventargs) response.write(request.queryString("a")) response.write(",") response.write(request.queryString("b")) get1.text=request.queryString("b") end sub sub star(sender as object,e as eventargs) dim connection1 as new sqlconnection("") const q1 as string="update tbltest set code=@code where dxid=@dxid" dim command1 as sqlcommand=new sqlcommand(q1,connection1) command1.parameters.add("@code",sqldbtype.varchar).value=get1.text command1.parameters.add("@dxid",sqldbtype._________).value=request.queryString("a") command1.connection.open() command1.executenonquery() command1.connection.close() end sub </script> </head> <body> <form runat="server"> <asp:textbox id="get1" columns="6" maxlength="6" runat="server" /> <asp:button id="buttonsave" text="click" onclick="star" runat="server" /> </form> </body></html>
I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)
What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.
So that I don't have duplicate candidate ids and i have single row with all column data.
I have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO
We have some tables that we have spread across two databases. The segregation isn’t essential, but the entities involved were disparate enough that we thought it made sense. However, our client app regularly & frequently requires information that can only be answered by queries to tables in both databases. It has been suggested that segregating the tables as we have introduces a performance hit. At this stage, it would be relatively easy to re-combine the tables into one DB.
I am new to SQL but trying to do join a few tables to get result showing showing one row per unique record.
Tables include:-
1. REQ 2. RFQ 3. PO 4. DOCUMENT (contains LAST_DOCUMENT_STATUS, DOCUMENT_ID, DOCUMENT_NUMBER, for example, REQ_CANCELLED, REQ_ID, REQ_NO) 5. DOCUMENT_STATUS (contains status of document, REQ_CREATE) 6. DOCUMENT_TRAIL (contains link between documents, PARENT_DOCUMENT, CURRENT_DOCUMENT, for example, REQ_ID (PARENT_DOCUMENT), RFQ_ID (CURRENT_DOCUMENT) 7. PO_REVISION (contains PO REVISION, when link with DOCUMENT, PO_REV_NO)
Currently when i tried to join all the TABLES, i get multiple lines against REQ_NO.
I realised the multiple lines generated due to the following:-
One to many relationships: A. RFQ - 1 or more PO B. PO - 1 or more PO_REVISON
I was thinking how to MAX the records in PO to show only the last PO_REVISION. It seems that DOCUMENT_TRAIL will contain 1 base document PO and 1 or more PO_REVISION.
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
I've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks. What does this mean exactly and how would you configure this? Is this a common practice in high-performance real-world environments (or should it be)?
Hi, We are building an application for online system for people to place ADs for selling various used items like Car, Electronics, Houses, Books etc. If someone selling a car then he can fill out headline, year, make, model, mileage, transmission, condition, color, price, description, contact etc. Similarly if someone selling a digital camera he will fillout headline, memory, zoom, megapixel, maker, model, color, batter, description etc. Option 1: I can have a main table to hold the common attributes of all different types of ADs (headline, images, contact, price, color, condition, description) + 1 table to store string values of all ADs (car: maker, model, square feet (if house), memory, megapixel (camera) etc) + 1 table to store the droplist select values(car: transmission, door, seat etc; house: year_built) pros: single table for all ADs. unique IDs for all ADs, easy to extend as new attributes can be dropped easily. cons: lot of physical reads of 2nd and 3rd table from join. 10 times physical reads compared to option 2 when reading 5000 records. Option 2: have different set of table for each AD type. Car will have its own main table + 1 table to store multiselect list box values. Similarly housing will have its own set of tables pros: 10% less physical read than option 1. cons: hard to add new attributes. We have to modify the main table by adding one column. Query will go to different table based on the category. Do you have any suggestions on which way to go?Thanks
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.
My end result should look like the below example.
Item Location QtyOnHandByLocation SumQtyCommitTotal Prod1 NJ 10 10 Prod1 NY 10 0 Prod1 FL 0 0 Prod1 PA 0 0
So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.
Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.
--create the temp table Create table #SalesLine ( Novarchar (50) not null , LocationCodevarchar (50) not null , QtyCommitint not null ) create table #ItemLedgerEntry
[code]....
I am close to the desired results but can't find a way.
I have a report that's created each day as a flat textfile.Because I came from the Access world, I created a macro that importsit with a schema that gives meaningful names to the various columns,and then uses a query to massage some of the data for me (deletes thefirst blank row and does a couple of calculations)Then I use DTS to import the Access query as a table.the textfile has a column called "File_num", and among several others,a column called "Serial_num". (the file numbers represent shipments,and sometimes there are more than one serial number in the shipment,etc., so there is a separate line for every serial number)Naturally, I would like to split this info into two tables..one thatdoes not contain the serial numbers and has a primary key on the"File_num" column, and another table that would contain just the"File_num" and "Serial_num" columns. That way I could relate themlater...but most importantly, it will give me a table where I can usethe "File_num" as my primary key.What would be the best way to import these two tables from one sourcetextfile? The other thing that gives me problems is that the textfile has no column names, and the first row is always blank.I'm very new to SQL and DTS and would appreciate any direction.Thanks,Larry- - - - - - - - - - - - - - - - - -"Forget it, Jake. It's Chinatown."
Hello Hello, This Noob has a question. I have been atempting for days to figure this out. I have this set up: (See Below)
The Problem I am having is occuring at the Table named OU in Maroon below. It combines 2 chains of keys into one table to try and make a unique record. The problem is that it is not.
Pasted below is a Query run on the data within the tables: As you can see certain data is getting duplicated in this tabel. I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.
Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.
Table:zCombined PK: zCombinedID FK: TempID ~ To Temp Table FK: ShipTypeID ~ To Ship Type Table FK: CoordinatorID ~ To Coordinator Table FK: BoxTypeID ~ To Box Type Table
can someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1. In the SERVER1, there is a table SEQUENCE in a database DATABASE1. And the table's structure of SEQUENCE are SeqType & SeqNo. Here is the sample data:
SeqType SeqNo Invoice 100 DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time? Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
I am running a proc (PROC1) that gets one record at a time and does some work against it.
select min(myid) as NextID from tbl_ListOfThingsToDo where AlreadyDone is null
Then once I have the MyID, I set the 'AlreadyDone' bit to True so that no one else picks that record up.
update tbl_listofThingsToDo set AlreadyDone=1 where MyID=(from above)
If there is only one PROC1 running, this is no problem. He just keeps grabbing the next record to operate on.
--but--
If I kick off multiple instances of PROC1 for scaleability reasons, how can I make sure that only one instance gets the next record? For example, couldn't the second instance get the same myid BEFORE the first instance has written that he already got it?
Design is to run 3-5 instances of this to get extra cycles.
