Selecting unique values from two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting unique values from two tables?

Author

Topic

ngreen88
Starting Member

1 Post

Posted - 2010-07-02 : 10:29:53

Hi,

I'm very new to SQL, and I haven't been able to figure this out. I'm trying to only read distinct sets of data, but also find out what's the earliest date they occur at. The system we have now logs several types of data every time a test is run, among which is the measured data and current calibration. I want to be able to find the dates which the calibrations change, since it's only changed every few months.

Basically, the database looks like:


Table1

DateTime     | TestName | Metal | Length | ...etc
6/1/10 10:40 | 1234     | Fe    | ...
6/1/10 10:40 | 1234     | Fe    |
6/1/10 10:50 | 4567     | Fe    |
6/1/10 10:50 | 4567     | Fe    |
6/1/10 11:10 | 8945     | Fe    |
6/1/10 11:10 | 8945     | Fe    |


Table2

 DateTime       |DataType | Ch0 | Ch1 | Ch2 | Ch3 |...etc
6/1/10 10:40:17 |  1      | 1.2 | 1.3 | 1.1 |...
6/1/10 10:40:17 |  2      | 1   | 2   | 1   |
6/1/10 10:50:45 |  1      | 1.1 | 1.4 | 1.2 |
6/1/10 10:50:45 |  2      | 1   | 2   | 1   |
6/1/10 11:10:01 |  1      | 2.2 | 2.1 | 2.6 |
6/1/10 11:10:01 |  2      | 1.1 | 1.8 | 1.1 |

Where DataType = 1 is measurement data, = 2 is the current calibration.

Desired Results


 DateTime       | DataType | TestName | Metal | Ch0 | Ch1 | Ch2 |... etc
6/1/10 10:40:17 | 2        | 1234     | Fe    | 1   | 2   | 1   |...
6/1/10 11:10:01 | 2        | 8945     | Fe    | 1.1 | 1.8 | 1.1 |...

I only want to query records where Ch0, Ch1, Ch2, and so on, are unique and when DataType = 2. Just using SELECT DISTINCT Table2.Ch1, Table2.Ch2, etc works but then I can't find out what the earliest date they occur at or the metal used or any other data associated with the test.

If I include the Date or any other fields in the distinct select, it reads data every time a measurement is taken, not just when the system is recalibrated. The DataType and Ch# columns are from a different table than the TestName and Metal Columns. Both tables have a DateTime column which relates them.

sample query of what I've been trying


SELECT    Table1.DateTime, Table2.DataType, Table1.TestName, Table1.Metal, Table2.Ch0, Table2.Ch1, Table2.Ch2,...
FROM      Table2 INNER JOIN Table1 
            ON Table1.DateTime = Table2.DateTime
WHERE     (Table2.DateTime > CONVERT(DATETIME, '2010-06-01 00:00:00', 102)) 
            AND (Table2.DataType = 2)
ORDER BY  Table2.DateTime DESC

I've tried using SELECT DISTINCT with a LEFT/RIGHT join... but can't get the results I'm looking for. I've only been using SQL for a couple weeks, and don't really know how to go about this. Any help would be greatly appreciated!

Thanks!
-Nick

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-02 : 10:47:06

Try something like this:

SELECT
	Table1.DateTime, 
	Table2.DataType, 
	Table1.TestName, 
	Table1.Metal, 
	Table2.Ch0, 
	Table2.Ch1, 
	Table2.Ch2,
	...
FROM      
	Table2 
INNER JOIN  
	(
		SELECT 
			MAX(DateTime) AS DateTime, 
			DataType, 
			Ch0, 
			...
		FROM 
			Table2
		WHERE 
			DataType = 2
			AND DateTime > CONVERT(DATETIME, '2010-06-01 00:00:00', 102)
		GROUP BY 
			DateTime
	) AS Table2
    ON Table1.DateTime = Table2.DateTime
ORDER BY  
	Table2.DateTime DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources