Duplicate record - almost!
-
Hello all,
hope someone may be able to help me solve a problem which is beyond my minimal SQL knowledge!
I have a table which contains details of relationships in a CRM database. Each side of the relationship is stored in this table along with relevant details. For instance....
I'm hoping to be able pull both sides of the relationship data together in a SQL view to show the detail for both sides in a single row.
Is this possible, and if so how would I go about it?Thank you in advance.
Paul -
Paul
Yes, it's possible. It would help if you could post some table DDL (CREATE TABLE statement(s)) and sample data (INSERT statement(s)). What other relationships are there - mother, daughter, aunt/uncle, niece/nephew, brother, sister, cousin, teacher, pupil?
John
-
Hi John,
Thank you for your quick response and offer to help.
I've attached a copy of some sample data and the create table script - I've dropped most of the fields from the original table and re-named the field names as they wouldn't have made any sense to you.Hope that's OK.
Paul -
Most people here aren't going to open a zip file posted on the internet. I suggest that you resubmit your data as outlined in the first link in my signature.
That being said, the relationship table is just a bridge table where both sides of the bridge point to the same table. You'll need to join to the appropriate table twice, and each instance will need it's own alias. For instance
SELECT *
FROM Relationship r
INNER JOIN Person rel
ON r.CRMUserID = rel.ID
INNER JOIN Person revrel
ON r.RevCRMUserID = revrel.ID
DrewJ. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, January 18, 2018 9:51 AM
Hi Drew,
I have read through the post as suggested and it is very useful, however I actually found that and your response quite condescending.Whilst I understand the points it is trying to make, it is assuming everyone uses SQL as part of the role on a regular basis.
As I said in the initial line of initial post I have minimal SQL knowledge. I work for a small healthcare charity (Not For Profit) who do not have the luxury of having someone with necessary skills. I pick up the very occaisonal SQL issue on a learn as you go basis and rely occaisonally (once or twice a year) on the goodwill of this and other forums to get me through.
Thanks again for advice I will bear it in mind for the next time.
Oh, by the way the post doesn't appear to mention anything to do with file types, specifically zip files. 🙂
- paul.ette - Friday, January 19, 2018 9:20 AM
Yes, that's the point. The post goes into a lot of detail about how to post in the forum. You don't need zip, or any other, files. I don't agree that Drew was being condescending. He was merely pointing out that if you post stuff in a way that either (a) makes it more difficult for the reader to consume or (b) incurs a security risk (whether real or perceived) for the reader then the quality and quantity of the responses you get might be less than you hoped for.
How did you get on, anyway? Did Drew's code work, or do you need any more help?
John
- paul.ette - Friday, January 19, 2018 9:20 AM
It doesn't mention file types, because it specifically says that saving data to a file is something to avoid.
Security threats are not specific to SQL. Anybody who connects to the internet should be aware of common avenues of attack, and one of the primary avenues is opening a file from an untrusted source.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply