Table Relationship Question For Unique Record
Apr 9, 2008
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.
Thanks a Bunch
Table: Sponsor
PK: Sponsor ID
Table: Protocol
PK: ProtocolID
FK: Sponsor ID
Table: Study
PK: StudyID
FK: ProtocolID
Table: OUPK: OUIDFK: StudyIDFK: zCombinedID
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
Sponsor
Protocol
Study
OU~Column1
OU~Column2
Box
Coordinator
Ship Type
Temp
Omni
AAAAA
XPG
S0101
NA
Wheaton
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
Matrix
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
B-1
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
9 x 9 4mL
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
NA
9 x 9 2mL
Raul Vargas
Daily
-70
Omni
AAAAA
XPG
S0101
S0201
Wheaton
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
Matrix
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
B-1
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
9 x 9 4mL
Mike Keane
DNA
-40
Omni
AAAAA
XPG
S0101
S0201
9 x 9 2mL
Mike Keane
DNA
-40
View 3 Replies
ADVERTISEMENT
Feb 10, 2014
I have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
View 4 Replies
View Related
Sep 23, 2014
Table :StudentTeacherRelation
Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
View 1 Replies
View Related
Sep 15, 2014
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.
View 4 Replies
View Related
Aug 29, 2006
Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...
WHAT HAPPENS??? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?
Thank you very much
View 1 Replies
View Related
Oct 24, 2005
HI
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
THANKS
ASM
View 3 Replies
View Related
Jan 9, 2008
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>
View 3 Replies
View Related
Mar 20, 2013
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.
View 6 Replies
View Related
Jul 20, 2005
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
View 6 Replies
View Related
May 1, 2008
Hi
I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.
Something like table A has
FRUITS
-------------
Oranges
Mangoes
Apricots
....
I want to write a sql query that does something like
SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A
so the output is
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....
regards
Hrishy
View 4 Replies
View Related
Apr 23, 2015
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.
View 13 Replies
View Related
Jul 11, 2006
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.
View 1 Replies
View Related
Jan 19, 2007
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.
View 4 Replies
View Related
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 > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 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)ContentGUIDVersionViewsRatingComments................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.
Thanks,P.
View 8 Replies
View Related
Aug 27, 2007
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?
pls advise. thanks.
View 1 Replies
View Related
Oct 19, 2007
Greetings,
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.
Let me know your thoughts, and thanks..
Dan Ribar
View 3 Replies
View Related
May 30, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 4 Replies
View Related
Mar 25, 2006
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
View 3 Replies
View Related
Oct 14, 2015
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
View 9 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 5, 2007
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
View 3 Replies
View Related
Feb 7, 2008
Hi I have two tables:
1.) Operator-OperatorID{PK, int, not null}-OperatorName{varchar(100), not null}-Enabled{bit, not null}-PasswordChange{bit, not null}-BirthDate{datetime, not null}
2.) Password-PasswordID{PK, int, not null}-Password{varchar(50), not null}-ExpirationDate{datetime, not null}
I'm not sure how to design and layout these two tables. The layout of these two tables is completely flexible as the application has not been deployed. I'm open to any good suggestions.
For each Operator I want to stored up to 3 previous passwords plus their current password. The password change field is so that if the operator's password expires or gets reset, they will be forced to enter a new password. This is a simple internal company application, so password encrypting is not necessary. The ExpirationDate indicates the date that the password will expire.
Hope to hear from someone soon! Thank you!
View 3 Replies
View Related
Apr 17, 2014
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
View 2 Replies
View Related
Jul 20, 2005
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies
View Related
Apr 11, 2004
Why is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
View 4 Replies
View Related
Sep 19, 2005
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View 4 Replies
View Related
Jun 10, 2006
We have two tables. Users and Projects and there is a many-to-many relationship.Ex. A user can be assigned into multiple projects.For the relationship table, should the table name be UserProjects or ProjectUsers?Also should it be singular or plural? (ex. UsersProjects or ProjectsUsers)?
View 2 Replies
View Related
Dec 5, 2006
Hi, I come back again.
Can anyone help me to create table with many-to-many relationship. Here is my three tables
tbl_Networks
(
NID int identity(1,1) primary key,
NetworkName nvarchar(256)
)
tbl_Categories
(
CID int identity(1,1) primary key,
CateName nvarchar(256),
NID int
)
tbl_Sim
(
SID int identity(1,1) primary key,
NID int,
CID int,
NameOfSim nvarchar(256)
)
My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them
So I need some help...
View 5 Replies
View Related
Feb 27, 2008
Hello,
I have 3 tables: Articles, ArticlesTags and Tags.
ArticleTags relate Articles and Tags records.
When I try to delete a record in Tags I get the following error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_ArticlesTags_Tags". The conflict occurred in database "MyDB", table "dbo.ArticlesTags", column 'TagID'.
The statement has been terminated.
What am I doing wrong?
Here is how I am creating my tables:
create table dbo.Articles
(
ArticleID uniqueidentifier not null
constraint PK_Article primary key clustered,
Body nvarchar(max) not null
)
create table dbo.ArticlesTags
(
ArticleID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_ArticlesTags
primary key clustered (ArticleID, TagID),
constraint FK_ArticlesTags_Articles
foreign key(ArticleID)
references dbo.Articles(ArticleID)
on delete cascade,
constraint FK_ArticlesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)
Basically, what I need is:
1. If an Article is deleted then:
> Delete all records for that Article in ArticlesTags
> Don't delete any Tag in Tags.
2. If an Tag is deleted then:
> Delete all records associated with it in ArticlesTags.
> Don't delete any Article in Articles.
What am I missing?
Thanks,
Miguel
View 2 Replies
View Related
Dec 19, 2006
I
have 3 tables I am trying to relate for a music player. I was
following the example in the msdn however, my relationships do not seem
right. Here are tables i want to relate:
Table 1:
ArtistID
ArtistName:
Table 2:
RecordingID
RecordingName
ArtistID
Table 3:
TrackID
TrackName
TrackNumber
TrackLocation
RecordingID
So
the main idea here is that the foreign keys are recordingId and
artistID. So what i did is created the 3 tables and then make a
diagram to create the relationships. I then was reading this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219696&SiteID=1
What
i want to do is use 2 different text boxes lets say and as i move from
the records in the Record_table (2) the corresponding artist will
change with it. However in the dataset the relationship looks like
this:
Artist -> Recording -> Track.... inorder for me to get
this relationship to work correctly i have to change all the
relationships in the dataset diagram. This way the dataset would look
like this: Track -> Recording ->Artist. This way i can use the
2 bindings to reference each other as stated in the link above. why
doesnt the relationship of the database know this already? why do i
have to change the relationship in the datasets.
View 3 Replies
View Related
Apr 23, 2008
Could anybody help me with the following scenario:
Table 1 Table2
ID,Date1 ID, Date2
I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.
Example:
Table1 data Table2 Data
ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008
The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1
Any help would be greatly appreciated.
Thanks
View 4 Replies
View Related
Aug 16, 2006
I am trying to update a record in a table based off of criteria of another record in the table.
So suppose I have 2 records
ID owner type
1 5678 past due
2 5678 late
So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?
View 5 Replies
View Related
Apr 6, 2007
I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:
- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
View 6 Replies
View Related
