Oracle SQL. How to select specific IDs where value of columns specify criteria? -

-

i have following statement/query:

select table1.file_id,    table1.status,    table2.status table1 inner join table2 on table1.file_id = table2.file_id;

the result follows:

   file id   |   status table 1    |   status table 2 -----------------------------------------------------    5500002   |        ax           |        ics    5500002   |        ax           |        icso    5500002   |        axo          |        ics    5500003   |        ax           |        ics    5500003   |        ax           |        ics    5500003   |        ax           |        ics    5500004   |        null         |        icso test    5500004   |        axo          |        ics    5500004   |        ax           |        null

i need each file id check 4 statuses:

 - ax  - axo  - ics  - icso

i want run select on file ids cover 4 statuses. can see file id occurs multiple times , i'd want query return file id fulfills requirement, in instance return 5500002 , 5500004.

for 5500004, there status mentions icso test regardless of remainder of text if contain icso within cell, counts icso. same applies other statuses.

how go adding query select specific ids?

try below 

select file_id ( select table1.file_id,    table1.status status1,    table2.status status2 table1 inner join table2 on table1.file_id = table2.file_id ) tab group file_id having count(distinct tab.status1) + count(distinct tab.status2) >= 4

Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more