Newbie SQL Question - finding multiple records

I have a table called DRAWINGS there are two fields in DRAWINGS,  DRAWNO and REV
The DRAWNO is 25 char string and REV is a one character string.   The REV (revision) can be A, B or C

How I can I find out all the DRAWNO that have both an A and B revision.  (in different records obviously)

I suspect it needs to group by DRAWNO but I am lost.

This is probably simple but I just need some SQL statements to get me started

TIA - NaomiG


NaomiGAsked:
Who is Participating?
 
rafranciscoCommented:
Try this:

SELECT A.*
FROM Drawings A INNER JOIN
(SELECT DRAWNO
FROM Drawings
WHERE REV IN ('A', 'B')
GROUP BY DRAWNO
HAVING COUNT(*) = 2) B
ON A.DRAWNO = B.DRAWNO
0
 
r_a_j_e_s_hCommented:

try this
select * from drawings where rev = 'A' or rev = 'B'
0
 
roshkmCommented:
Hope this select workz..  

Select DRAWNO  from Drawings
where ReV = 'A' OR ReV = 'B'
group by DRAWNO  
having count (rev) > 1

Regards,
RKM
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NaomiGAuthor Commented:
Nice try but  - I need to know only which drawings have a record for both A and B not either
0
 
OtanaCommented:
select drawno from drawings where drawno in (select drawno from drawings where rev='A') AND drawno in (select drawno from drawings where rev = 'B')
0
 
r_a_j_e_s_hCommented:
Try this

select drawno from drawings where rev in (select rev from drawings where rev = 'A' or rev = 'B')
0
 
NaomiGAuthor Commented:
I think that will work,  but it only displays one field
is there a way to display both fields DRAWNO and REV and some extra informative field like DATE on the affected rows so I can better verify the data.

Example of resulting tables
DRAWNO  -  REV - DATE
----------------------
DWG0001  - A  -  04/02/05
DWG0001  - B  -  04 /06/05
DWG0004 -  A  -  04/05/05
DWG0004 -  B  -  04/06/05

POINTS INCREASED
0
 
OtanaCommented:
sure, just add thise fields in your query:

select drawno, rev, date from drawings ...
0
 
r_a_j_e_s_hCommented:
but u have given there are only 2 rows in ur table. if u want to display, just provide more info for the same
0
 
r_a_j_e_s_hCommented:
have u viewed ur previous question, whether  the comment is perfect for ur query r u want more info for that?????????
0
 
JulianvaCommented:
You can also do this

select  DRAWNO ,  REV , DATE
from drawings where  rev = A
union
select  DRAWNO ,  REV , DATE
from drawings where  rev = B

0
 
NaomiGAuthor Commented:
Not quite - Julianva solution displays the rows and fields (good)
but includes all records with Rev = A or Rev = B not just those with both

I am pretty sure I somehow need to add the Group by and Having count (or something) to identify which have both Rev A and B like roshkm suggested

NaomiG



 
0
 
OtanaCommented:
SELECT drawno, rev, date
FROM drawings
WHERE drawno IN
  (select drawno from drawings where rev='A')
AND drawno IN
  (select drawno from drawings where rev = 'B')
0
 
rafranciscoCommented:
or this:

SELECT A.*
FROM Drawings A INNER JOIN
(SELECT DrawNo FROM Drawings WHERE REV = 'A') B
ON A.DrawNo = B.DrawNo
INNER JOIN
(SELECT DrawNo FROM Drawings WHERE REV = 'B') C
ON A.DrawNo = C.DrawNo
0
 
JulianvaCommented:
this will give you records with only A and B

after you run this query do a select of one record from the result and see if its correct

select T.drawing,T.rev,T.date from table1 T,Table1 Q
where   T.drawing = Q.drawing
and T.rev = 'A'
and Q.Rev = 'B'
group by T.drawing,T.rev,T.date
0
 
Scott PletcherSenior DBACommented:
Please try this, which will provide the results with only one pass thru the table (may need a correction(s) :-) :


SELECT DRAWNO,
      'A' AS [REV1], MAX(CASE WHEN REV = 'A' THEN DATE ELSE NULL END) AS [DATE1],
      'B' AS [REV2], MAX(CASE WHEN REV = 'B' THEN DATE ELSE NULL END) AS [DATE2]
FROM DRAWINGS
GROUP BY DRAWNO
HAVING MAX(CASE WHEN REV = 'A' THEN DATE ELSE NULL END) IS NOT NULL AND
      MAX(CASE WHEN REV = 'B' THEN DATE ELSE NULL END) IS NOT NULL
0
 
Scott PletcherSenior DBACommented:
P.S.:

If you need to get rid of the warning msg about "Null value", issue this command before the SELECT:

SET ANSI_WARNINGS OFF
0
 
NaomiGAuthor Commented:
Thanks - I have enough to work with for now
I will split the points and come back after I have time to try some of these suggestions out.   Naomi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.