Duplicate records on a SQL query using left joins
Posted on 2011-03-24
Ok, I know this is pretty much by design; I'm just trying to find a way around it for this specific instance...
In short, (and I guess expected) I get multiple records returned when I call a query with an inner join. But in this case I do not want to know what's on the 2nd table; I just want to know it exists.
In my case I want to be able to search the dates for open/close records without pulling up the dates. in my C# app, I handle this when the individual record is opened. It would be way too much information to pull in during the initial query.
I need to be able to search the dates for open records and closed records. Eg. if dtOpen > 2010-05-11' and dtClose = '1900-01-01' would mean that the record was opened but not closed yet. I also need to search the dtOpen and dtClose for specific dates.
What I have for my query looks something like this:
SELECT DISTINCT szName, pkID, DTS.dtOpen, DTS.dtClose
tblMain (LEFT JOIN tblDates as DTS on tblMain.pkMainID = DTS.fkMainID)
DTS.dtOpen = '2011-05-11' AND DTS.dtOpen = '1900-01-01' .... AND other qualifications
If the tblDate has more than one record, the SQL will give me multiple records for each which I don't want. Does anyone know how I can use that table for checking but not reporting?