If so can someone help me out?
Ihave 2 tables. Both tables have pk's of (patId, visitDate). fk is patId. Sometime the 2 dates will match other timesthey don't so they aren't.
Table1example
patId visitDate1 labValue
1 1/1/06 5
1 1/5/06 <NULL>
1 2/1/06 <NULL>
2 2/2/06 6
2 3/12/06 3
3 1/24/06 2
3 3/1/06 <NULL>
Table2example
patId visitDate2 Col1 Col2
1 1/1/06 3 7
1 1/23/06 <NULL> 12
2 2/2/06 2 <NULL>
3 1/16/06 5 3
NoticeTable 1 has more/different patId's/visitDates
Nowwhat I want the query to output is everything from Table2 and "append" labValuefrom Table1. I want all records fromboth tables where the patId's match
Desiredoutput:
patId Date(from both tables) labValue Col1 Col2
1 1/1/06 5 3 7
1 1/5/06 <NULL> <NULL> <NULL>
1 1/23/06 <NULL> <NULL> 12
1 2/1/06 <NULL> <NULL> <NULL>
2 2/2/06 6 2 <NULL>
2 3/12/06 3 <NULL> <NULL>
3 1/16/06 <NULL> 5 3
3 1/24/06 2 <NULL> <NULL>
3 3/1/06 <NULL> <NULL> <NULL>
SELECT d1.PatID,d1.Date,t1.labValue,t2.Col1,t2.Col2
FROM (SELECT PatID,Date FROM Table1 UNION SELECT PatID,Date FROM Table2) d1
LEFT JOIN table1 t1 ON (d1.Patid=t1.patid AND d1.Date=t1.Date)
LEFT JOIN table2 t2 ON (d1.Patid=t2.patid AND d1.Date=t2.Date)|||
Thanks for your assistance with this.
It's almost working correctly, but not quite.
It is pulling all records from Table1 (which I want), but itis not pulling any records from Table2. It IS pulling data from Table2 when it matches a date in Table1, butthis is not always the case.
Table2 can have records with dates that do not match a datein Table1.
See my example in my first post of how records from bothtables are appended.
|||
The query I gave will return the exact results you have in your first post.
|||
USE [test]
GO
/****** Object: Table [dbo].[Table1] Script Date: 03/13/2006 12:13:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[patId] [int] NULL,
[visitDate] [datetime] NULL,
[labValue] [int] NULL
) ON [PRIMARY]
USE [test]
GO
/****** Object: Table [dbo].[Table2] Script Date: 03/13/2006 12:13:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table2](
[patID] [int] NULL,
[visitDate] [datetime] NULL,
[Col1] [int] NULL,
[Col2] [int] NULL
) ON [PRIMARY]
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'1/1/2006',5)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'1/5/2006',NULL)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (1,'2/1/2006',NULL)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (2,'2/2/2006',6)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (2,'3/12/2006',3)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (3,'1/24/2006',2)
INSERT INTO [test].[dbo].[Table1]([patId],[visitDate],[labValue]) VALUES (3,'3/1/2006',NULL)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (1,'1/1/2006',3,7)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (1,'1/23/2006',NULL,12)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (2,'2/2/2006',2,NULL)
INSERT INTO [test].[dbo].[Table2]([patID],[visitDate],[Col1],[Col2]) VALUES (3,'1/16/2006',5,3)
SELECT d1.PatID,d1.visitDate,t1.labValue,t2.Col1,t2.Col2
FROM(SELECT PatID,visitDateFROM Table1UNIONSELECT PatID,visitDateFROM Table2) d1
LEFTJOIN table1 t1ON(d1.Patid=t1.patidAND d1.visitDate=t1.visitDate)
LEFTJOIN table2 t2ON(d1.Patid=t2.patidAND d1.visitDate=t2.visitDate)
Gives the results:
1 2006-01-01 00:00:00.000 5 3 7
1 2006-01-05 00:00:00.000 NULL NULL NULL
1 2006-01-23 00:00:00.000 NULL NULL 12
1 2006-02-01 00:00:00.000 NULL NULL NULL
2 2006-02-02 00:00:00.000 6 2 NULL
2 2006-03-12 00:00:00.000 3 NULL NULL
3 2006-01-16 00:00:00.000 NULL 5 3
3 2006-01-24 00:00:00.000 2 NULL NULL
3 2006-03-01 00:00:00.000 NULL NULL NULL
|||I think the last one will work
Thanks for your help|||
Ok, we're almost there. Going off of your last query I have this working. Here's my actual query:
SELECT d1.Date, t2.BxMarsh, t2.Diet, t2.Symptoms, t2.Bx,t2.BxLocation, t2.GI_MD,
t2.DateDietStarted,t2.DietNotes,t2.Comments
FROM (SELECT patnum,Date FROM labs UNION SELECTpatnum,labDate FROM labceliac) d1
LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum ANDd1.Date=t2.labDate)
where (d1.patnum = 3625)
Now, the last thing I need to do is add a column from Labsthat IS NOT in LabCeliac. Here's what Iadded:
SELECT d1.Date,d1.Transglut, t2.BxMarsh, t2.Diet,t2.Symptoms, t2.Bx, t2.BxLocation, t2.GI_MD,
t2.DateDietStarted,t2.DietNotes,t2.Comments
FROM (SELECT patnum,Date, Transglut FROM labs UNION SELECTpatnum,labDate FROM labceliac) d1
LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum ANDd1.Date=t2.labDate)
where (d1.patnum = 3625)
I added the Transglut field to both the first line and
Now I'm getting this error:
Server: Msg 8157, Level 16, State 1, Line 1
All the queries in a query expression containing a UNIONoperator must have the same number of expressions in their select lists.
I know that this is asking for the same field after theunion statement, but Transglut only exists in Labs, and not LabCeliac.
|||
SELECT d1.Date,labs.Transglut, t2.BxMarsh, t2.Diet, t2.Symptoms, t2.Bx, t2.BxLocation, t2.GI_MD,
t2.DateDietStarted, t2.DietNotes,t2.Comments
FROM (SELECT patnum,Date FROM labs UNION SELECT patnum,labDate FROM labceliac) d1
LEFT JOIN labceliac t2 ON (d1.patnum=t2.patnum AND d1.Date=t2.labDate)
LEFT JOIN labs ON (d1.patnum=labs.patnum AND d1.Date=labs.Date)
where (d1.patnum = 3625)
The trick is that our derived table d1 is a list of rows which represent every patient and every date for each of those patients that exist in either labs or labceliac. With that in hand, then we can and join that back to the labs and labceliac tables to get columns from those tables that are in one, but not the other by doing left (outer) joins.
