|
Make sure you build in a process for archiving this History table on a periodic basis. It will grow by leaps and bounds in no time. Also, consider a method for turning this auditing off ... there may be a case where you have a large update job that you don't really want to audit.
Just a thought from someone who has been there ... 
Good luck.
|
|
|
|
|
If your using SQL Server 2005 or greater have a look at the TSQL Output clause[^]
this will give you access to the deleted and inserted tables that you normally get with a trigger.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Is it possible to get information about whether a child table has reference to a parent table through a certain record?
For example, tblParent has a record whose primary key value is 5. I want to know whether tblChild_A, tblChild_B and tblChild_C have one or more records in them where the foreign key value is 5. Well it's definitely possible by querying each of the child tables individually but I want to know whether there's any system query or stored procedure or whatever that produces a tabular output from all those tables together.
If it's possible in MS SQL Server then please also inform what's its counterpart in MS Access. Regards.
modified on Tuesday, June 14, 2011 3:16 AM
|
|
|
|
|
It's a very simple query to return how many records in a child table match a parent's ID, however, you haven't really made it clear what you want to achieve.
How many child tables do you have? Why don't you want to query them individually?
|
|
|
|
|
Well as I said, if possible, I want to get a tabular output showing which tables are related to a certain primary table and the related data in them all-together. Please refer to the second paragraph in my OP. Feel free to ask if I'm still not clear.
|
|
|
|
|
Have a read through this article on sp_help[^]
There is a section in the article that shows the tables a given table references.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
While there are system views which will identify the FKs between tables this is not a system issues but a data problem. You will need to craft a query that get the data you want the way you want it. There is no 'system' query/function etc that will service this requirement.
It is a fairly simple excercise using either sub queries or left joins depending on the required output.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can I select records from a single sql table that have common data in two of their columns.
Thanks,
i.e.
table customer {Name, Address, Phone Number)
How can I select all records from customer where address and phone number are the same even though the records are unique?
|
|
|
|
|
Ugly way that works:
select distinct a.* from Customer a, Customer b where a.Address = b.Address
and
a.[Phone Number] = b.[Phone Number]
and a.Name <> b.Name
order by Address, [Phone Number], Name
There must be a prettier way to do it...but at least it works in MS SQL. 
|
|
|
|
|
You could try:
SELECT *
FROM customer
WHERE Phone = Address
My advice is free, and you may get what you paid for.
|
|
|
|
|
No, that will return customers who have the same information stored in the phone and address fields of a single record, rather than two different customers who have the same phone number and the same address...
|
|
|
|
|
Okay, I see how I may have misinterpreted the requirement.
I thought he wanted to find mistakes where say a phone number was filled into the address field.
My advice is free, and you may get what you paid for.
|
|
|
|
|
When done, it'd be wise to put a unique-constraint on that phone-number to prevent this from happening again.
Bastard Programmer from Hell 
|
|
|
|
|
What happens if he wants to track 2 people at the same residence (and don't give me any crap about mobiles, not everyone has one).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That would lead to another design; either it's correct to have multiple records or it isn't. Since he's asking how to identify them, I'm assuming that he doesn't want them. The end-user has to be able to identify wheter his/her item is in that list, and they need something to uniquely identify the tupel, otherwise they'll end up putting the data in there twice, creating redundancy.
To answer your question; the telephone number shouldn't be an attribute of the residency. Without that, we wouldn't have to model database, would we? We'd simply hook up everything to a autonumber and we'd be done - but Access proves that it ain't that simple 
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: he's asking how to identify them, I'm assuming that he doesn't want them.
See....I read it as probably a case of he wanted to find all the people who share a residence & phone. Traditional married is easy, based on just the last name and address, but people don't necessarily share names anymore. Thus address + phone number.
|
|
|
|
|
No, you can't read it as you like; it depends on how it's used. If the table can contain two people on the same address, it would be holding people, not addresses. How can one uniquely identify a "human"? Well, the only way that the end-user can be sure, is by using a non-changing and unique attribute of the entity.
The phone-number is not an attribute that uniquely identifies a person, nor a household. We've got normalization-rules for a reason
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: No, you can't read it as you like; it depends on how it's used.
After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Now, totally redesigning what he's working with is another thing, which should be addressed. 
|
|
|
|
|
GenJerDan wrote: After years and years and years of seeing things like this, I can almost guarantee that what I described is what was being looked for at this time.
Most people assume they can, which explains the high number of databases with redundant and even incorrect data. There wouldn't be a reason to search the dupes, if it had been analyzed correctly - it would have had a constraint to prevent them from being entered in the first place.
Bastard Programmer from Hell
|
|
|
|
|
Hmm. Search for dupes. But they're not dupes as such. At least not in the sense of "I want to find the dupes to eliminate them". Maybe more like "I want to find the people who share an address so the company only has to send out one phone book to each household."
Yes, you can do that with a normalized database, too. 
|
|
|
|
|
GenJerDan wrote: Search for dupes. But they're not dupes as such.
Your assumption isn't logical, whether or not they are dupes (of the same record) is determined by the key. It seems as if he's trying to find duplicate records, not merely records that have the same value on a given attribute, isn't it?
Your description doesn't sound very logical either; you're referring to clients, their addresses (some clients could have multiple, in theory), and their phones (could be multiple per location, or even independant of the location)
GenJerDan wrote: Yes, you can do that with a normalized database, too.
I never stated that it wouldn't be
Bastard Programmer from Hell
|
|
|
|
|
Eddy Vluggen wrote: Your assumption isn't logical, whether or not they are dupes (of the same
record) is determined by the key. It seems as if he's trying to find duplicate
records, not merely records that have the same value on a given attribute, isn't
it?
Not to me.
The way I read it is "Find records that have the same address and phone number, but different names", which does fit the share-a-house scenario.
|
|
|
|
|
You shouldn't look at what fits, but at what's required. We model reality, you can't expect reality to follow your model.
It might be true that there are multiple entities at an address, and it might be false - but any assumption is false by definition.
Bastard Programmer from Hell
|
|
|
|
|
Do a group by on the fields you need to test and a count.
Select count(*), Name,Phone,Address from SomeTable
Group By Name,Phone,Address
having count(*) > 1
This will give you the dupes, then join the results back to the original table on these 3 fields to get the actual records. Standard deduping excercise.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, this will only give you results where the name is also the same, not just the phone and address...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
