Scripting Help

[joe_pool_is]

We have two tables that I need to write a query for. I am a Software Developer, and we do not have any Database Admins here.

TableA:
|_varchar(50)_|_char(15)_|_char(10)_|_varchar(99)_|_DateTime_|
|_Employee____|_ModelNum_|_Station__|_Status______|_TestDay__|

TableB:
|_char(15)_|_varchar(50)_|
|_ModelNum_|_ModelName___|

Though no primary key exists for the two tables, TableAs ModelNum *does* map to TableBs ModelNum.

How would I get:
TableA.Status, TableA.ModelNum, and TableB.ModelName
WHERE (TableA.Station=Testing)
AND (TableA.TestDay between (Date1 and Date2))
AND (TableA.Employee from TableA.Station=Building)

Here is the scenario:

Employee John assembles units at the Building Station.

Sometime later, these uints arrive at the Testing Station where they are tested by others.

For a given date range at the Testing Station, we need the ModelNum, ModelName, and Status at the Testing Station for all coils that John built at any time in the Building Station.

How would I write such a query?

I am interested in seeing ways to write this query - simple or elegant!

Bonus points if I can get this query to return a count of the distinct ModelNames! (though I can do this manually in my software)

 

[PlausiblyDamp]

Off the top of my head the closest literal translation of your code would be
[highlight=sql]
SELECT DISTINCT A.Status, A.ModelNum, B.ModelName
FROM TableA A JOIN TableB B
ON A.ModelNum = B.ModelNum
WHERE A.Station = Testing
AND A.Employee IN (SELECT Employee FROM TableA WHERE Station = Building)
AND A.TestDay BETWEEN Date1 AND Date2
[/highlight]

 

[joe_pool_is]

Wow! All that knowledge, and SQL power too! You must get paid the big bucks!

Ok, Ill have to wait until Monday morning to give that a shot. Darned company closes on Saturday. {sigh}

On a personal note: Were over by Dallas, TX and Ike will be visiting us in an hour or so. How exciting!