Similar to a true and false query within a query

1

Hey all, thanks for all your support, first time using a forum. i think i lost track trying to simplify the query, here more information about the tables and the query i'm trying to write. If you need for information please feel free to ask.

Table A
ID Name Location
A1 John London
A2 Mike London
A3 Adam London
A4 Steve London
A5 James London

Table B
ID TEST Results
A1 TEST1 SATISFACTORY
A1 TEST2 UNSATISFACTORY
A1 TEST3 SATISFACTORY
A2 TEST1 SATISFACTORY
A2 TEST2 UNSATISFACTORY
A2 TEST3 SATISFACTORY
A3 TEST1 SATISFACTORY
A3 TEST2 SATISFACTORY
A4 TEST1 UNSATISFACTORY
A4 TEST2 UNSATISFACTORY
A5 TEST0 NOT TESTED

at the moment my query is;

Select Distinct TableAID, TableAName, TableALoacation, TableBID, TableBResults

Left outer join TableA on TableID = TableID

So when i run this Query i get the following results

ID NAME ID Results
A1 JOHN A1 SATISFACTORY
A1 JOHN A1 UNSATISFACTORY
A2 MIKE A2 SATISFACTORY
A2 MIKE A2 UNSATISFACTORY
A3 ADAM A3 SATISFACTORY
A4 STEVE A4 UNSATISFACTORY
A5 JAMES A5 NOT TESTED

So if all the tests for a paticular ID is SATISFACTORY(A3) then i get 1 output
'A3 ADAM A3 SATISFACTORY'

So if all the tests for a paticular ID is UNSATISFACTORY(A4) then i get 1 output
A4 STEVE A4 UNSATISFACTORY

Also if all the tests for a paticular ID is NOT TESTED(A5) then i get 1 output
A5 STEVE A5 NOT TESTED

Though if i have an ID with some SATISFACTORY & UNSATISFACTORY then it returns 2 rows
A1 JOHN A1 SATISFACTORY
A1 JOHN A1 UNSATISFACTORY
A2 MIKE A2 SATISFACTORY
A2 MIKE A2 UNSATISFACTORY

What i'm trying to achieve is that if there is a mix of SATISFACTORY & UNSATISFACTORY for 1 ID then the output only shows the UNSATISFACTORY. I've tried case statement, group by .... just having no luck :confused:

thanks once again.

2

No sample table, no sample data in SQL statements, so I have no way of providing you with a worked example that represents your table and column names, I also cannot test my example, and it will only be any use to you if you understand it and can figure it out ...

SELECT	MyName
	, MyTown
	, MyYesNo
FROM	(
		SELECT	[MyRowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY MyName
					, MyTown
				ORDER BY 
					CASE WHEN MyYesNo = 'No' THEN 1 ELSE 2 END
			)
			, MyName
			, MyTown
			, MyYesNo
		FROM	dbo.MyTable
	) AS X
WHERE	MyRowNumber = 1
ORDER BY MyName
	, MyTown
3

Here's a simple way to do it...

SELECT 
	mt.Name,
	mt.Location,
	YorN = MIN(mt.YorN)
FROM 
	dbo.MyTable my
GROUP BY 
	mt.Name,
	mt.Location;
4

See ... with sample data you would have known that there was also a value of "Maybe" in that column :stuck_out_tongue_winking_eye:

5

LOL... Perhaps... :roll_eyes:

6

"Blank"? :confounded:

7

Until the OP says different, I'm going to take them at their word and assume simple two value logic.
Even if NULLs, blanks or other values are possible, a simple CASE expression would make for an easy fix.

8

yeah, fair enough, but I'm afraid I start from a different point : for people asking for help it seems to me that it is more likely that data is of variable quality, and things that we might expect to have been caught by constraints etc. aren't part of their systems.

9

Hi Kriten, i hope the updated post makes more sense

10

Hey Jason,

I've updated the post as it lacked informtion, i hope the updated post helps.

Thanks

11

Try this:

select a.id
      ,a.[name]
      ,case
          when min(b.results)=max(b.results)
          then min(b.results)
          else max(b.results)
       end as results
  from table_a as a
       inner join table_b as b
               on b.id=a.id
 group by a.id
         ,a.[name]
;
12

Hi,

I missed out a value in the example :persevere: could you please have a look again.

the missing value was 'NOT TESTED'.

There's only 4 possible outcomes;
NOT TESTED
SATISFACTORY
UNSATISFACTORY
SATISFACTORY AND UNSATISFACTORY (ISSUE)