SQL join question
-
I don't do SQL on a regular basis and don't really know much about JOINs. Can someone help me form a proper SQL statement to achieve what I need.
I have to do a search on any or all of:
Lastname, firstname, active , contacttype, organization
I know there will be multiple sql statements, but I figured if I could get one, I could figure out the rest.
Table A.Firstname, Lastname, ContactID, ActiveIndicator
Table B.ID, ContactID, ContactTypeID
Table C.ContactTypeID, Description
Table D.ContactID, OrgID
Table E.OrgID, OrgName
So in my first search, what if I have the ContactID from Table A. Now what?
In this case, I know the contact is Active because I have that in Table A. However, I don't have ContactType, or the Organization name. I guess it's possible that there could be more than one.
-
There are probably a variety of ways to address this.
One way would be to create a view joining together the fields you want to be searchable, so you can issue queries against that view. For example -- and I left out Active, as I don't know how you want to handle it, as I don't see a table mapping "ActiveIndicator" to "ActiveName" or some such.
-- Create view uniting all the searchable fields
-- Result has ContactID as first field, because I assume once you find the row
-- you want to know who the underlying contact is
-- Result has [SearchField] as second field -- test against that one
-- Result has a third field [MatchType] to tell you how you hit on them
CREATE VIEW [SearchFields] AS
SELECT A.[ContactID], A.[Firstname] AS [SearchField], 'Firstname' AS [MatchType]
FROM A
UNION ALL
SELECT A.[ContactID], A.[Lastname] AS [SearchField], 'Lastname' AS [MatchType]
FROM A
UNION ALL
SELECT A.[ContactID], C.[Description] AS [SearchField], 'ContactType' AS [MatchType]
FROM (A JOIN B ON A.[ContactID] = B.[ContactID])
JOIN C ON B.[ContactTypeID] = C.[ContactTypeID]
UNION ALL
SELECT A.[ContactID], E.[OrgName] AS [SearchField], 'Organization' AS [MatchType]
FROM (A JOIN D ON A.[ContactID] = D.[ContactID])
JOIN E ON D.[OrgID] = E.[OrgID]
Use it like so:
select * from [SearchFields] WHERE [SearchField] like '%Smith%'
Anyway, hopefully this gives you example joins at least
-
Try:
select A.LastName, A.FirstName, C.Description, E.OrgName
from A
inner join B on B.ContactID = A.ContactID
inner join C on C.ContactTypeID = B.ContactTypeID
inner join D on D.ContactID = A.ContactID
inner join E on E.OrgID = D.OrgID
You must have key data in all tables for the join to work. That is, each Contact must have a entry in B and D, etc. If this is a problem, change joins to left joins instead.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply