SQL question!

1. Senior Member
Join Date
Dec 1969
Posts
114

## SQL question!

Hi all,<BR><BR>I am using access 2000. I have a table with this type of structure:<BR><BR>id questionid rating<BR>100 1 5<BR>100 2 5<BR>100 3 5<BR>100 4 5<BR><BR>Rating may have values from 1 - 5<BR><BR>QuestionId&#039;s will range from 1 -4 for a specific ID.<BR><BR>I need to write a query in which for a specific ID if questionid is either 1,2 and rating is 5 and for questionid&#039;s 3 and 4 either of the rating is 5 and other is 4 return "true" in any other case return "false".<BR><BR>So the possible permutations to return "true" would be<BR><BR>questionid: 1 2 3 4<BR><BR>rating : 5 5 5 5<BR> 5 5 5 4<BR> 5 5 4 5<BR><BR>I have been trying to find a pragmatic solution for a while but to no avail, any hints are highly appreciated.Thanks in advance.

2. Senior Member
Join Date
Dec 1969
Location
Indianapolis, IN
Posts
1,755

## RE: SQL question!

SELECT CASE WHEN (QuestionID IN (1, 2, 3, 4) AND Rating = 5) OR (QuestionID IN (3, 4) AND Rating = 4) THEN TRUE ELSE FALSE AS MyCrazyField<BR>FROM mytable<BR><BR>Something like that maybe? If I have confused your logic, at least understand how you could use CASE to make it work.

3. Senior Member
Join Date
Dec 1969
Posts
114

## RE: SQL question!

Case Statement is supported in Access?

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## HUH?

(1) No CASE in Access, but<BR><BR>(2) This doesn&#039;t come even close.<BR>That will return a true/false based on *ONE RECORD*! <BR><BR>To make a decision, as the question is given, you have to look at *FOUR* records, all at once!<BR><BR>

5. Senior Member
Join Date
Dec 1969
Location
Indianapolis, IN
Posts
1,755

## RE: SQL question!

Ah, crud. I got so excited I didn&#039;t even look at what you were using. I&#039;m honestly not sure, but my guess is probably not. You could try it out pretty quick, though. Sorry for the almost certain waste of time...

6. Senior Member
Join Date
Dec 1969
Location
Indianapolis, IN
Posts
1,755

## RE: HUH?

I completely overlooked the big sentence about Access 2000. I withdraw my post.

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Have to join to self...

Any time you want a value (including a "truth") that is based on more than one record, what do you have to do?<BR><BR>JOIN, right?<BR><BR>And for four separate records, you have to join 4 tables.<BR><BR>The fact that all the records are in the same table doesn&#039;t really change anything.<BR><BR>SELECT T1.id, T1.rating, T2.rating, T3.rating, T4.rating<BR>FROM table AS T1, table AS T2, table AS T3, table AS T4 <BR>WHERE T1.id = T2.id AND T2.id = T3.id AND T4.id = T3.id<BR>AND T1.rating = 5<BR>AND T2.rating = 5<BR>AND ( ( T3.rating = 5 AND T4.rating = 4 )<BR> OR ( T3.rating = 4 AND T4.rating = 5 )<BR> )<BR>ORDER BY T1.id<BR><BR>But even that&#039;s not quite enough for this situation.<BR><BR>If all you wanted to do was find all the id&#039;s that satisfied your condition, you could get away with that. <BR><BR>But you *also* want an answer for the records that *do not* fulfill that condition, right?<BR><BR>And that means an OUTER JOIN. <BR><BR>And quite frankly, Access is fussy enough about its outer join syntax that I&#039;d want to muck with it a while to try to find an answer. So why don&#039;t I let you try that, first?<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## So make it work in SQL Server...

It&#039;s still not the right answer. That will get a true/false for *ONE* record:<BR> id -- questionid -- rating<BR> 100 -- 1 -- whatever<BR><BR>But how does it relate *that* record to the other 3 questionid&#039;s???<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## One way...

...would be to save *THAT* query (prior msg) *AS* a named query in Access. Say you save that as "FourAndFive".<BR><BR>Then you could simply do:<BR><BR>SELECT T.id, ( FF.id IS NOT NULL) AS TheTruth<BR>FROM table AS T LEFT JOIN FourAndFive AS FF<BR>ON T.id = FF.id<BR>ORDER BY T.id<BR><BR>That might be easier than trying to get all the parentheses in the right place doing it all in one query in Access.<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay...I really coded it...

...in Access. I missed one big thing in the first query (forgot to qualify the table instances by the qid value!).<BR><BR>I used a table named "questions" for all of this.<BR><BR>So here&#039;s the "FourAndFive" query:<BR><BR>SELECT T1.id, T1.rating, T2.rating, T3.rating, T4.rating<BR>FROM questions AS T1, questions AS T2, questions AS T3, questions AS T4<BR>WHERE T1.id=T2.id And T2.id=T3.id And T3.id=T4.id <BR> And t1.qid=1 And t2.qid=2 And t3.qid=3 And T4.qid=4 <BR> And T1.rating=5 <BR> And T2.rating=5 <BR> And ( ( T3.rating=5 And T4.rating=4) <BR> Or (T3.rating=4 And T4.rating=5)<BR> )<BR><BR>And then the main "TruthTable" query is simply:<BR><BR>SELECT DISTINCT T.id, IIf(FF.id Is Not Null,&#039;Oh, yes!&#039;,&#039;Too bad&#039;) AS TheTruth<BR>FROM questions AS T LEFT JOIN FourAndFive AS FF ON T.id = FF.id<BR>ORDER BY T.id;<BR><BR>I had to add the DISTINCT else you would get 4 results for each id. If you joined with some other table where the ID values only appear once each, then you wouldn&#039;t need the DISTINCT.<BR><BR>And of course you can use the IIF to return whatever values you want for that second column. Of just use<BR> SELECT DISTINCT T.id, (FF.id Is Not Null) AS TheTruth<BR>if a true/false is adequate.<BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•