Display the customer who has issued for more than one movie from that category

Question

0.00/5 (No votes)

See more:

Actually this is in mysql and i am not able to select mysql tag.

waq to display customerid,cusname,contactno,num of movies issued to each customer
and category. display the customer who has issued for more than
one movie from that caregory.display phone num as "+91-987-654-3210".

find the ER diagram in below link

https://drive.google.com/open?id=0Bxich9-8Q8B5dVhXS25nWEZtOUE&authuser=0

Customer_master

CUS001	AMIT	9876543210	ADD1	2012-02-12	21
CUS002	ABDHUL	8765432109	ADD2	2012-02-12	21
CUS003	GAYAN	7654321098	ADD3	2012-02-12	21
CUS004	RADHA	6543210987	ADD4	2012-02-12	21
CUS005	GURU		        ADD5	2012-02-12	21
CUS006	MOHAN	4321098765	ADD6	2012-02-12	21
CUS007	NAME7	3210987654	ADD7	2012-02-12	21
CUS008	NAME8	2109876543	ADD8	2013-02-12	21
CUS009	NAME9		        ADD9    2013-02-12	21
CUS010	NAM10	9934567890	ADD10	2013-02-12	21
CUS011	NAM11	9875678910	ADD11	2013-02-12	21

Customer_issue_details

IS001	CUS001	MV001	2012-05-13	2012-05-13	2012-05-13
IS002	CUS001	MV001	2012-05-01	2012-05-16	2012-05-16
IS003	CUS002	MV004	2012-05-02	2012-05-06	2012-05-16
IS004	CUS002	MV004	2012-04-03	2012-04-16	2012-04-20
IS005	CUS002	MV009	2012-04-04	2012-04-16	2012-04-20
IS006	CUS003	MV002	2012-03-30	2012-04-15	2012-04-20
IS007	CUS003	MV003	2012-04-20	2012-05-05	2012-05-05
IS008	CUS003	MV005	2012-04-21	2012-05-07	2012-05-25
IS009	CUS003	MV001	2012-04-22	2012-05-07	2012-05-25
IS010	CUS003	MV009	2012-04-22	2012-05-07	2012-05-25
IS011	CUS003	MV010	2012-04-23	2012-05-07	2012-05-25
IS012	CUS003	MV010	2012-04-24	2012-05-07	2012-05-25
IS013	CUS003	MV008	2012-04-25	2012-05-07	2012-05-25
IS014	CUS004	MV007	2012-04-26	2012-05-07	2012-05-25
IS015	CUS004	MV006	2012-04-27	2012-05-07	2012-05-25
IS016	CUS004	MV006	2012-04-28	2012-05-07	2012-05-25
IS017	CUS004	MV001	2012-04-29	2012-05-07	2012-05-25
IS018	CUS010	MV008	2012-04-24	2012-05-07	2012-05-25
IS019	CUS011	MV009	2012-04-27	2012-05-07	2012-05-25

Movie_Master

MV001	DIEHARD  	2012-05-13	ENGLISH	4	2HRS	U/A	ACTION	DIR1	L1	L2	100
MV002	THE MATRIX	2012-05-13	ENGLISH	4	2HRS	A	ACTION	DIR2	L1	L2	100
MV003	INCEPTION	2012-05-13	ENGLISH	4	2HRS	U/A	ACTION	DIR3	L1	L2	100
MV004	DARK KNIGHT	2012-05-13	ENGLISH	4	2HRS	A	ACTION	DIR4	L1	L2	100
MV005	OFFICE S	2012-05-13	ENGLISH	4	2HRS	U/A	COMEDY	DIR5	L1	L2	100
MV006	SHAWN OF DEAD   2012-05-13	ENGLISH	4	2HRS	U/A	COMEDY	DIR6	L1	L2	100
MV007	YOUNG FRANKEN	2012-05-13	ENGLISH	4	2HRS	U/A	COMEDY	DIR7	L1	L2	100
MV008	CAS	        2012-05-13	ENGLISH	4	2HRS	A	ROMANCE	DIR8	L1	L2	100
MV009	GWW     	2012-05-13	ENGLISH	4	2HRS	A	ROMANCE	DIR9	L1	L2	100
MV010	TITANIC  	2012-05-13	ENGLISH	4	2HRS	A	ROMANCE	DIR10	L1	L2	100
MV011	THE NOTE BOOK	2012-05-13	ENGLISH	4	2HRS	A	ROMANCE	DIR11	L1	L2	100

additional information copied from comment below

SQL

select m.customer_id,customer_name,
cast(concat('+91-',substring(contact_no,1,3),'-',substring(contact_no,4,3),'-',
substring(contact_no,7) ) as char)as contact_no,
count(i.movie_id) as no_of_movies,movie_category
from customer_master m 
join customer_issue_details i 
on m.customer_id = i.customer_id
join  movies_master mv
on i.movie_id = mv.movie_id
group by m.customer_id
having count(m.customer_id) >1;

Posted 18-Apr-15 9:41am

Harshit Wadhera

Updated 18-Apr-15 11:41am

Nelek

v5

Add a Solution

Comments

PIEBALDconsult 18-Apr-15 16:30pm

You likely need a GROUP BY and a COUNT()

Harshit Wadhera 18-Apr-15 16:55pm

i had done this.. but its giving me wrong ans

PIEBALDconsult 18-Apr-15 18:17pm

Then update the question (Improve question) with what you have and what's wrong with it.

Harshit Wadhera 18-Apr-15 16:54pm

select m.customer_id,customer_name,
cast(concat('+91-',substring(contact_no,1,3),'-',substring(contact_no,4,3),'-',
substring(contact_no,7) ) as char)as contact_no,
count(i.movie_id) as no_of_movies,movie_category
from customer_master m
join customer_issue_details i
on m.customer_id = i.customer_id
join movies_master mv
on i.movie_id = mv.movie_id
group by m.customer_id
having count(m.customer_id) >1;

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-04-19T01:37:00

Try this:

SQL

SELECT cud.CustomerID, cud.CountOfIssues, cum.<OtherFields>, '+91-987-654-3210' As EmergencyPhoneNo
FROM (
    SELECT CustomerID, COUNT(MovieId) As CountOfIssues
    FROM Customer_issue_details
    GROUP BY CustomerID
    HAVING CountOfIssues>1
) AS cud INNER JOIN Customer_master AS cum ON cud.CustomerID = cum.CustomerID

Replace cum.<OtherFields> with corresponding field list, such as: cum.Name, cum.SurName, etc.

For further information, please see:
HANDLING GROUP BY[^] and HAVING[^]
SELECT[^]
ALIAS[^]

Member 11618051 · Answer 2 · 2015-04-18T11:00:00

Solution 1

COUNT (X)
GROUP BY X
HAVING COUNT(X) "whatever"
....dive into SQL and don't be only lazy and ask for the solution

Posted 18-Apr-15 11:00am

Member 11618051

Updated 18-Apr-15 11:01am

v2

Comments

Harshit Wadhera 18-Apr-15 17:06pm

dude 1 thing i will tell you You will not understand how complicated it is...
i had already applied having group by and count... just understand the question then answer......

PIEBALDconsult 18-Apr-15 18:18pm

That's a bit harsh.

[no name] 18-Apr-15 18:20pm

It is, sorry...