NaomiG
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice try but - I need to know only which drawings have a record for both A and B not either
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this
select drawno from drawings where rev in (select rev from drawings where rev = 'A' or rev = 'B')
select drawno from drawings where rev in (select rev from drawings where rev = 'A' or rev = 'B')
ASKER
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
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
sure, just add thise fields in your query:
select drawno, rev, date from drawings ...
select drawno, rev, date from drawings ...
but u have given there are only 2 rows in ur table. if u want to display, just provide more info for the same
have u viewed ur previous question, whether the comment is perfect for ur query r u want more info for that?????????
You can also do this
select DRAWNO , REV , DATE
from drawings where rev = A
union
select DRAWNO , REV , DATE
from drawings where rev = B
select DRAWNO , REV , DATE
from drawings where rev = A
union
select DRAWNO , REV , DATE
from drawings where rev = B
ASKER
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
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
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')
FROM drawings
WHERE drawno IN
(select drawno from drawings where rev='A')
AND drawno IN
(select drawno from drawings where rev = 'B')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
If you need to get rid of the warning msg about "Null value", issue this command before the SELECT:
SET ANSI_WARNINGS OFF
ASKER
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
I will split the points and come back after I have time to try some of these suggestions out. Naomi
Select DRAWNO from Drawings
where ReV = 'A' OR ReV = 'B'
group by DRAWNO
having count (rev) > 1
Regards,
RKM