Problem trying to perform 3 queries based on each other

[beetlebailey]

The first select statement works fine. When I try to incorporate the next two select statements it does not work. I need the results to display in 3 seperate query results windows. The query from the vpd table should result in: Tester1 = 6, Tester2 = 7, Tester3 = 10
Can anyone see what I might be doing wrong ?

(select top 1 tester as 'Tester1' from vpd where line = 451)
(select top 1 tester as 'Tester2' from vpd where line = 451 and tester <> 'Tester1')
(select top 1 tester as 'Tester3' from vpd where line = 451 and tester <> 'Tester1' and tester <> 'Tester2'

Thanks for the help.

 

[jby1]

This is a simple (although prob not best) solution:

declare @tester1 int
declare @tester2 int

select @tester1 = top1 tester from vpd where line = 451
select @tester2 = top1 tester from vpd where line = 451 and tester <> @tester1

select @tester1 as Tester1
select @tester2 as Tester2
select top1 tester as Tester3 from vpd where line = 451 and tester <> @tester1 and tester <> @tester2

 

[jby1]

Although I will say that you should probably say what it is you are actually trying to do, because I can see loads of possible issues with the approach you have taken.

 

[Catadmin]

If you want QA to show three different result windows at the bottom, the simple solution is to put a GO command between each SELECT statement.

Like so:

select top 1 tester as 'Tester1' from vpd where line = 451

GO

select top 1 tester as 'Tester2' from vpd where line = 451 and tester <> 'Tester1'

GO

select top 1 tester as 'Tester3' from vpd where line = 451 and tester <> 'Tester1' and tester <> 'Tester2'

GO

Then click the little green arrow.

If you are trying to pull up 3 different results windows in another application (not Query Analyzer), let us know because the answer will be something completely different.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[beetlebailey]

OK, here's the deal, hopefully I can explain it good enough for all to understand: I have a production line with 3 testers on it. Each tester is independant of the other and the line. All 3 testers update production data in a MSSql 2000 table called vpd based on it's tester number and what line it assigned to, in this case the line is 451 and the testers are 6, 7 and 10. Each tester knows what line it is assigned to and what it's tester number is. I am attempting to query the vpd table to obtain the summed production data of all three testers on line 451. I also need the tester number, line number and part number that each tester is currently set up to test, (which could be up to 3 different units) I am trying to combine all this into one query which will be used in FrontPage 2003 in the Database Results query wizard. The above two replies do work in Query Analyzer but not in FrontPage. Must I change my query approach ?

 

[Catadmin]

I think so. You probably need to create a series of views that first seperates your data, then pulls it back together in the format you want. Then you can use the view to display your information in FrontPage.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???