AnsweredAssumed Answered

SQL statement to extract attribute data from a single table in Oracle

Question asked by ChrisWhittaker_SSWater on Jun 5, 2018
Latest reply on Jun 6, 2018 by ChrisWhittaker_SSWater

i have a single table in an Oracle database that has different unique feature class codes in the same table relating to main (CODE = 76000)  and pipe samples (CODE = 77030) taken against the mains.

 

I want to know how top grab attributes from the two feature classes without having to run multiple sql statements.

 

I have got to the same result by running multiple sql statements via the 'IN CLAUSE' so just want to further my skills.

 

I wanted to generate a single piece of SQL to grab the items in RED from a different feature class than 77030 i.e. the mains class  The ones in RED relate to the main that the pipe sample is attached to and come from a feature code of 76000.. I think I needed an inner join.

 

the S_IPID relates to the pipe sample and the S_PARENT_IPID relates to the main that the pipe sample is attached to. Hence we have the join, but I do not know how to grab the attributes in RED from the mains attributes

select S_IPID,
S_PARENT_IPID,
U_DATE_PIPE_SAMPLE,
S_XCOORD_A,
S_YCOORD_A,
U_CONDITION_GRADE,
U_GROUND_TYPE,
U_PIPE_SAMPLE_REF,
U_REASON,
U_STREET,
U_SURFACE_USE,
U_TOWN,
U_MATERIAL,
U_DIAMETER,
U_DATE_INSTALLED,
U_DATE_REHABILITATED

from sdf_asset_all
where S_FCODE=77030

Outcomes