Simple SELECT question 1

[littleIdiot]

Hi,

I'm sure this is easy to do - but I'm still building my knowledge as I work so turning to trusty tek-tips forums for help ...

Table 1: SEIS_DISCRIPTION

SEIS_DISCRIPTION	Null?	  Type
 ------------------------------- ----
 IDX				  NUMBER
 FORMAT 			  VARCHAR2(6)
 START_CDP			  NUMBER
 END_CDP			  NUMBER
 FIRST_RECORD			  NUMBER
 LAST_RECORD			  NUMBER
 SAMPLES			  NUMBER
 SAMP_RT			  NUMBER
 LOGICAL_REEL_NUM		  NUMBER
 CHANNELS			  NUMBER
 RECORD_LENGTH			  NUMBER
 JOB_ID 			  NUMBER
 FIRST_X_COORD			  NUMBER
 LAST_X_COORD			  NUMBER
 FIRST_Y_COORD			  NUMBER
 LAST_Y_COORD			  NUMBER
 LINE				  VARCHAR2(16)

Table 2: DATACARRIER

 DATACARRIER		Null?	  Type
------------------------------- ----
 IDX				  NUMBER
 BARCODE			  VARCHAR2(12)
 PROJECT			  VARCHAR2(20)
 REEL_NUM			  VARCHAR2(12)
 LOCATION			  NUMBER
 CREATION_DATE			  DATE
 FIRST_LINE			  VARCHAR2(16)
 DENSITY			  VARCHAR2(1)
 KINDCODE			  VARCHAR2(6)
 KINDDIS			  NUMBER
 ARCHIVE_NAME			  VARCHAR2(16)
 QC_STATUS			  VARCHAR2(5)

I am trying to find all unique BARCODE and DATACARRIER values from DATACARRIER where LINE = U3* in SEIS_DISCRIPTION.

I know that te IDX value is the key - jsut not how to link the results!

Simple eh?

All help appreciated,
littleIdiot.

PS Spelling of "DISCRIPTION" is intentionally incorrect!

 

[littleIdiot]

Will this work as I think it should?

select unique IDX, BARCODE, PROJECT from DATACARRIER where IDX in (select idx from SEIS_DISCRIPTION where LINE like 'U3%');

thanks

 

[JarlH]

Replace [tt]unique[/tt] with [tt]distinct[/tt] and it will work.

(Note that you are selecting the distinct combinations of IDX, BARCODE and PROJECT - not only IDX and BARCODE as you mentioned in your first posting.)

You can also write the query as
[tt]
select distinct IDX, BARCODE, PROJECT
from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
where LINE like 'U3%';[/tt]

May be slower or faster depending on product, indexes, amount of data etc.

 

[littleIdiot]

Thanks ... but it failed and I'm not sure why ...

SQL> select distinct IDX, BARCODE, PROJECT from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX where LINE like 'U3%';
select distinct IDX, BARCODE, PROJECT from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX where LINE like 'U3%'
                                                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended

 

[littleIdiot]

this might be easier to read:

SQL> select distinct IDX, BARCODE, PROJECT
  2  from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
  3  where LINE like 'U3%';
from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
                      *
ERROR at line 2:
ORA-00933: SQL command not properly ended

 

[JarlH]

Well, I've never used Oracle, so I’m not really the right person to help you with this...

How do you use to end SQL statements?

Or does Oracle require old style joins?
[tt]
select distinct IDX, BARCODE, PROJECT
from DATACARRIER,
SEIS_DISCRIPTION
where DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
and LINE like 'U3%'[/tt]

Or maybe you have to write INNER JOIN, instead of just JOIN...?

 

[littleIdiot]

I use semi-colon ( to close ...

and yes, it seems it needs old style joins ... this worked:

select distinct BARCODE, PROJECT 
from DATACARRIER, SEIS_DISCRIPTION 
where DATACARRIER.IDX = SEIS_DISCRIPTION.IDX 
and LINE like 'U3%';

Thanks for your help!