October 5, 2006 at 11:11 am
Need some help here - dont even know if can do this:
So I have a view which is joining a few tables, one of them called X. It has a key X.genID which is a foreign key on tables A,B,C which are included in the join. X also has a field called pID which is a foreign key to the ID field on table P.
The view is retrieving all results from A,B,C and P for every genID found in X
NOW - I have another Table Y which also has a field called genID which also is a foreign key on tables A,B,C. And it has a field qID which is a foreign key for ID on Q.
Now I want the view to show all results from A,B,C for every genID in X and Y as well as show P data based on the relevant pID in X as well as all Q data based on the relevant qID in Y.
If anyone is brave enough to help me with this it would be greatly appreciated!
October 5, 2006 at 12:14 pm
Unless I'm missing something, I don't see a reason to use an Outer join from your description. Here is the from clause that I think you need:
X inner join A on X.GenID = A.GenID
inner join B on X.GenID = B.GenID
inner join C on X.GenID = C.GenID
inner join P on X.pID = P.pID
inner join Y on X.GenID = Y.GenID
inner join Q on Y.qID = Q.qID
If this doesn't help, post a script to create the tables and insert sample data. Then show what you want the results to look like.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 12:29 pm
Thanks for the reply but I didnt make myself clear.
Although X and Y both have genID they are two independant tables and no join can happen between them. I need all the above joins made based on all genID's that exist in X, and all above joins made for all genID's that exist in Y.
X and Y themselves never have anything to do with each other, just they both contain genID which is referenced in other tables and used to retrieve necessary values.
Am I being clear?
Thanks again
October 5, 2006 at 12:34 pm
The question said that the genID in Y is a foreign key to A,B,C. So, please explain the relationship between Y and A,B,C. Or better, yet, if you could provide a script, I would know what I am trying to solve.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 3:11 pm
Ok one last go, hopefully this will be clear. If not please dont worry and thanks for your help.
Below is a view generated for Donors table. It contains a donorid (not a primary key but generated elsewhere). The Donors table is the backbone of the view and it is joined in a way that all donorid's in this table will be retrieved. This table is then refernced by the donorid to a few other tables to retrieve specific data.
Now I have another table called Cordblood which also contains this donorid field. I basically want to do exactly the same with the Cordblood table as is done here with donors but all at the same time.
In other words the view should retrieve a list of ALL donorid's, all those found in Donors and all those found in Cordblood (one having nothing to do with the other) and then reference all relevant tables and retrieve data based on these donorids.
So below is a view for the Donors table. If you can show me how to stick Cordblood into this view that would be very much appreciated.
Thanks again
SELECT dbo.SearchResults.DonorID, dbo.SearchResults.Status AS SearchResultStatus, dbo.SearchResults.ResultType AS SearchResultType,
dbo.SearchResults.NewAlgorithm, dbo.SearchResults.SearchRequestID, dbo.Donors.DOB, dbo.Donors.Sex, dbo.Donors.Active,
dbo.DonorsUnavail.Active AS UnavailActive, dbo.DonorsUnavail.CenterID, dbo.DonorsUnavail.AvailAgainDate, dbo.TestRequests.PatientID,
dbo.TestRequests.TestType, dbo.TestRequests.Status AS TestReqStatus, dbo.TestRequests.CenterID AS TestReqCenterId,
dbo.TestRequests.ServicedTimeStamp, dbo.TestRequests.RequestedTimeStamp, dbo.TissueTypeInfo.SER_A1, dbo.TissueTypeInfo.SER_A2,
dbo.TissueTypeInfo.SER_B1, dbo.TissueTypeInfo.SER_B2, dbo.TissueTypeInfo.SER_C1, dbo.TissueTypeInfo.SER_C2, dbo.TissueTypeInfo.SER_DR1,
dbo.TissueTypeInfo.SER_DR2, dbo.TissueTypeInfo.SER_DQ1, dbo.TissueTypeInfo.SER_DQ2, dbo.TissueTypeInfo.DNA_A1,
dbo.TissueTypeInfo.DNA_A2, dbo.TissueTypeInfo.DNA_B1, dbo.TissueTypeInfo.DNA_B2, dbo.TissueTypeInfo.DNA_DRB1_A,
dbo.TissueTypeInfo.DNA_DRB1_B, dbo.TissueTypeInfo.DNA_DQB1_A, dbo.TissueTypeInfo.DNA_DQB1_B, dbo.TissueTypeInfo.DNA_C1,
dbo.TissueTypeInfo.DNA_C2, dbo.TestRequests.DetailID AS TestReqDetailID, dbo.TestRequests.ID AS TestReqID, dbo.NMDP_IDS.NMDP_ID,
dbo.SearchResults.SearchAuthorID, dbo.SearchResults.CreationTimeStamp AS DateSearchIdentified, dbo.DonorsUnavail.ReferenceID AS UnavailRefID,
TestRequests_1.PatientID AS PatientIDFromUnavail, TestRequests_1.Status AS StatusOfTRFromUnavail,
TestRequests_1.TestType AS TestTypeFromUnavail, 0 AS TNC, 0 AS CD34, 0 AS TMNC, 0 AS FinalVol
FROM dbo.TestRequests TestRequests_1 INNER JOIN
dbo.DonorsUnavail ON TestRequests_1.ID = dbo.DonorsUnavail.ReferenceID RIGHT OUTER JOIN
dbo.Donors INNER JOIN
dbo.SearchResults ON dbo.Donors.DonorID = dbo.SearchResults.DonorID LEFT OUTER JOIN
dbo.TestRequests ON dbo.SearchResults.DonorID = dbo.TestRequests.DonorID INNER JOIN
dbo.TissueTypeInfo ON dbo.Donors.TissueTypeInfoID = dbo.TissueTypeInfo.ID LEFT OUTER JOIN
dbo.NMDP_IDS ON dbo.SearchResults.DonorID = dbo.NMDP_IDS.DonorID ON dbo.DonorsUnavail.DonorID = dbo.SearchResults.DonorID
WHERE (dbo.SearchResults.IsCord = 0)
October 5, 2006 at 3:19 pm
Maybe what you are looking for is UNION. That is a way to write two queries that don't have to have anything to do with each other and combine them. This is a very simplified example:
table table1 (col1 int, col2 int)
table table2 (col3 int, col4 int)
into table1 select 1,2
into table2 select 2,3
Col1, Col2 from Table1
Col3, Col4 from table2
Otherwise, what I need is a script similar to the one here that creates example tables and inserts data. The code you posted doesn't really help.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 5, 2006 at 3:49 pm
Yes I thought of the union option and got it to work, just it means making a lot of dummy fields in order for the Union not to complain, as there are going to be different results depending on which table is being referenced.
I think it will be too complicated to send you all the table structure and view code.
Let me try one last thing and simplify it
I have table Customers(ID int,SSN char(20)) and table Info (NAME char(20), SSN char(20)).
So now I want to view all name of customers, so will be
Select Name from Info INNER JOIN Customer ON Name.SSN = Customer.SSN
So thats a straightforward join.
Now what if I have another table called Emplyees(ID int,SSN char(20)) and in one SQL statement I want to get a list of all Names by using the SSN (as we did above for Customers) from all values in the Customers table AND the Employers table.
Is there anyway to do that via a join as opposed to a union?
Thanks again
October 5, 2006 at 4:04 pm
I didn't need your real tables, this example is fine. I think that even though you have to create dummy fields, a UNION isn't too bad. Here is an example using COALESCE:
table customers (ID int,SSN char(20))
table Info (NAME char(20), SSN char(20))
table Emplyees(ID int,SSN char(20))
into customers select 1,'1234'
into customers select 2,'2345'
into Emplyees select 3,'4567'
into Emplyees select 4,'5678'
into Info select 'Bob','1234'
into info select 'Sue','2345'
into info select 'John','4567'
into info select 'Sam','5678'
coalesce(e.SSN,c.SSN) as SSN,
Info i left join customers c
i.SSN = c.SSN left join emplyees e
i.SSN = e.SSN
If it finds the value in the employee table, that is displayed, otherwise it displays the value from the customer table.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
October 6, 2006 at 6:01 am
Perhaps another approach to using a join would be something like:
Select Info.Name from Info WHERE (Name.SSN IN (SELECT Customer.SSN from Customer)) OR (Name.SSN IN (SELECT OtherTable.SSN from OtherTable))
October 6, 2006 at 7:25 am
Thanks for all the help!
The left join you advised made sense. The problem is that there are so many other tables to take care of which are related in this join that I think I am giving up and going with the Union. I implemented the union and the problem is that its just so slow, but for now it will have to do.
Thanks again
October 6, 2006 at 1:39 pm
Just a thought (and maybe not a good one)....
What if you use either of Kathi's solutions above (I am thinking the latter would really need to be a FULL OUTER JOIN rather than a LEFT JOIN to be sure you get all records from both tables) as a subquery to create a derived table that you then join all your other tables to. I don't know how well it would perform compared to the union you are using, but here's what I'm thinking, using the employee/customer example above:
-- Using UNION
SELECT SSN, Name
FROM Info INNER JOIN (
SELECT SSN
FROM Employees
UNION
SELECT SSN
FROM Customers
) People ON Info.SSN = People.SSN
-- Using FULL OUTER JOIN
SELECT SSN, Name
FROM Info INNER JOIN (
SELECT COALESCE(E.SSN, C.SSN) AS SSN
FROM Employees E FULL OUTER JOIN Customers C ON E.SSN = C.SSN
) People ON Info.SSN = People.SSN
I don't know that any of these solutions will be fast, given how much work the server has to do to merge all that data.
--Andrew
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply