Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Newbie SQL Question - finding multiple records

Posted on 2005-05-17
18
Medium Priority
?
180 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:NaomiG
  • 4
  • 4
  • 3
  • +4
18 Comments
 
LVL 3

Assisted Solution

by:r_a_j_e_s_h
r_a_j_e_s_h earned 80 total points
ID: 14017727

try this
select * from drawings where rev = 'A' or rev = 'B'
0
 
LVL 4

Expert Comment

by:roshkm
ID: 14017735
Hope this select workz..  

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

Regards,
RKM
0
 

Author Comment

by:NaomiG
ID: 14017739
Nice try but  - I need to know only which drawings have a record for both A and B not either
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Assisted Solution

by:Otana
Otana earned 80 total points
ID: 14017740
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
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 14017779
Try this

select drawno from drawings where rev in (select rev from drawings where rev = 'A' or rev = 'B')
0
 

Author Comment

by:NaomiG
ID: 14017840
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
 
LVL 11

Expert Comment

by:Otana
ID: 14017866
sure, just add thise fields in your query:

select drawno, rev, date from drawings ...
0
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 14017906
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
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 14017925
have u viewed ur previous question, whether  the comment is perfect for ur query r u want more info for that?????????
0
 
LVL 8

Expert Comment

by:Julianva
ID: 14017984
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
 

Author Comment

by:NaomiG
ID: 14018244
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
 
LVL 11

Expert Comment

by:Otana
ID: 14018417
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 80 total points
ID: 14018459
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14018473
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
 
LVL 8

Assisted Solution

by:Julianva
Julianva earned 80 total points
ID: 14018479
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 80 total points
ID: 14018564
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14018596
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
 

Author Comment

by:NaomiG
ID: 14089910
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question