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, TableA's ModelNum *does* map to TableB's 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, I'll have to wait until Monday morning to give that a shot. Darned company closes on Saturday. {sigh}

 

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