How to write a SQL query to get the below Scenario output

scenario based sql query
complex select statement in sql
sql advanced queries
unsolved sql queries
retail sql queries
tricky query in sql
write an sql query to fetch first_name from worker table in upper case
longest sql query

I have 2 table like below and I need the client_ID from client table and his Roles from roles table, but the condition to get the data the tables is, client whose is having more than one contribution_type.

Please find the below sample table and output.

Client table

Client_ID   cname   Contribution_type
-------------------------------------   
   1        A       Regular
   2        B       public 
   3        C       regular
   4        D       private
   1        A       public 
   4        D       similar

Role table

Client_ID   Rname
------------------    
   1        owner
   2        owner
   3        trustee
   4        benificier
   1        trustee
   2        benificier
   3        owner
   4        owner

Output

Client_ID   Rname
-------------------    
    1       owner
    1       trustee
    4       beneficiary
    4       owner

I've written the below query but getting the below error

select 
    c.cid, r.rname 
from 
    Client_table c 
join 
    role_table r on c.cid = r.CID
where 
    c.cid in (select cl.CID, count(Contribution_type) 
              from Client_table c 
              group by cl.CID 
              having count(Contribution_type) > 1);

Error message:

ORA-00913: too many values 00913. 00000 - "too many values" *Cause: *Action: Error at Line: 21 Column: 9

Thanks in advance.


Try to remove count(Contribution_type) in your where subquery. because In only can compare one column.

select c.cid,r.rname
from Client_table c 
join role_table r on c.cid = r.CID
where c.cid in (select cl.CID from Client_table c group by cl.CID having count(Contribution_type)>1);

or you can just write a subquery in from instead of in where.

select c.cid,r.rname 
from (
  select cl.CID 
  from Client_table c
  group by cl.CID 
  having count(c.Contribution_type)>1
) c 
join role_table r on c.cid = r.CID

Need SQL query for below scenario, This may be slightly different in Oracle SQL SELECT seq, req FROM table1 WHERE seq in (SELECT max(seq) FROM table1) AND seq NOT IN  Why do you need to write select statments with DUAL table. Since you have not posted the CREATE TABLE and INSERT statements, people have used WITH clause and SELECT col,col FROM dual to generate test data. In your case apply the query on your table directly. You will get the output.


This will do it for you. Assuming that in case of same contribution type appearing more than once, you want only one instance, use count (distinct contribution_type) else just use count(contribution_type)

select * from role r
where r.client_id in
    (select client_id from client c
    group by client_id 
    having count(distinct contribution_type)>1
    )

The reason for the error is that in your IN clause, you have mentioned only 1 value, but you are fetching more than 1 values from your subquery, hence the error.

How to write a SQL query to get the below Scenario output, How to write a SQL query to get the below Scenario output. I have 2 table like below and I need the client_ID from client table and his Roles  You have to just change your order by query as below, you include both month name and year in order by instead of just select year in order by clause. ORDER BY RIGHT(DATENAME(YEAR, date), 2) AS date If you wants to apply order on first year and then month then write query as below:


select c.cid,r.rname from Client_table c join role_table r on c.cid = r.CID
where c.cid in (select cl.CID from Client_table c group by cl.CID having count(Contribution_type)>1);

Remove count(Contribution_type) in your subquery.

Write a sql Query as per given scenarios, select * from tblEmployee a. ouput like this. Table2. select * from tblRelatives a. output. final the query output given below. select; subquery. SQL Processing & Query Execution. To improve the performance of your SQL query, you first have to know what happens internally when you press the shortcut to run the query. First, the query is parsed into a “parse tree”; The query is analyzed to see if it satisfies the syntactical and semantical requirements.


it will do :

select  distinct client_id,rname from role_table where client_id in(1,4) order by client_id;

Complex SQL Queries, Complex SQL queries examples with answers,Complex SQL Queries,Advanced SQL Queries,Complex SQL Queries for Interview,distinct Query to find Second Highest Salary of Employee?(click for explaination) 21.how to write sql query for the below scenario We cannot use dual table to display output given above. Following are some Business Steps of Writing SQL Queries : 1.Analyse the Business Logic : The first step is to analyse the Business Logic. 2.Fragment the Business Logic : The second step is user needs to Fragment the Business logic and accordingly need to fragment logic.


Your problem is that the subquery is returning multiple columns. But, you can also simplify the outer query:

select r.cid, r.rname 
from role_table r
where r.cid in (select c.CID  -- only one column here
                from Client_table c 
                group by c.CID 
                having count(c.Contribution_type) > 1
               );

In other words, you don't need the join to Client_table because you have all the fields you need in role_table.

Chapter 16 SQL Data Manipulation Language – Database Design , The SELECT statement, or command, allows the user to extract data from tables, In this next example, we will use a Publishers table (Table 16.2). The GROUP BY clause is used to create one output row per each group and produces The below SELECT statement illustrates how to count groups of data using the  SQL putting text in query with group by and order by. To get a formatted output with user defined columns ('For','No.of Agent','Agent(s)','in' and '%' ) along with the 'agents' table with following condition - 1. number of agents for each 'working_area' must be less than 3, the SQL statement can be used: SQL Code:


The Best SQL Examples, This is used to select the database containing the tables for your SQL opposite order (descending) you use DESC, as in the example below: As a database developer, writing SQL queries, PLSQL code is part of daily life. Having a good knowledge on SQL is really important. Here i am posting some practical examples on SQL queries. To solve these interview questions on SQL queries you have to create the products, sales tables in your oracle database.


SQL UNION overview, usage and examples, Let's get into the details of Set Operators in SQL Server, and how to use them aforementioned example, the intersect operator output is given below In such a scenario, you would use the Union clause and it is very easy to 


SQL Examples, creating a table; creating a view; inserting rows; updating rows; deleting rows; commit query to select Northern stations (Northern latitude > 39.7): another metric query restricted to January below-freezing (0 Celsius) data, sorted on rainfall:.