How to write store procedure using joins

Question

SELECT ud.id as userId, ud.organization_id,ud.first_name,ud.last_name,ud.image_url,agentCount,
 dealerCount, IFNULL(inventoryCount, 0) inventoryCount
 
 FROM
(
    SELECT * FROM jhi_user ju 
    INNER JOIN 
    (
        SELECT user_id , organization_id FROM employee e 
        WHERE role_id in 
                    (SELECT id FROM role r WHERE organization_id in 
                       (SELECT id FROM organization o WHERE sales_person_id = 1) 
                     and name = 'NEXCAR_SALES_PERSON' AND (deleted = 0 or deleted is null))
    ) userOrg ON ju.id = userOrg.user_Id
) ud

LEFT JOIN 

(
       SELECT sales_person_id,id, count(id) as dealerCount FROM organization o  
       GROUP BY sales_person_id
                            
) odetails on odetails.sales_person_id  = ud.id

 LEFT JOIN 

(
    SELECT  o.id , count(o.id) as agentCount FROM employee e
                           LEFT JOIN organization o on e.organization_id = o.id 
                           WHERE e.user_id = 1 GROUP BY o.id 
) edetails ON ud.organization_id = edetails.id
 
LEFT JOIN 
(
   SELECT i.organization_id, count(id) as inventoryCount FROM inventory i  
)idetails on idetails.organization_id = odetails.id

the above query i have a table called role in that table i have a column 'name' how to select that column in above

This doesn't appear to be related to java - please remove the tag. — Andrew S, Sep 07 '22 at 12:31
Title is misleading question is nothing to do with procedure try converting IN to join to acquire role name — P.Salmon, Sep 07 '22 at 15:05
You need to use a nested select statement, I don't see 'name' inside your subquery, you should select 'name' in your subquery. See, https://stackoverflow.com/a/4630000/16764813 — Wei Chen Chen, Sep 09 '22 at 08:39

score -1 · Answer 1 · answered Sep 07 '22 at 12:25

-1

Assuming you are using SqlServer you could do the following:

USE YourDatabaseName
GO

CREATE STORED PROCEDURE [your_schema].[your_procedure_name]
as
BEGIN
-- YOUR LOGIC HERE
END

GO

You can find more info about creating stored procedures Create a Stored Procedure

You should really clarify your question a bit more. Give this a go: How do I ask a good question?

answered Sep 07 '22 at 12:25

CodeJunkie

263
2
11

please find the above query and see my question sir @darko6977 – Muvvala Krupanandam Sep 08 '22 at 06:09
2

The question is not clear enough. The title of the question says **How to write store procedure using joins**. If you don't provide any details or code you have tried to solve your issue, there is no way of precisely answering your question, I'm sorry. – CodeJunkie Sep 08 '22 at 06:33

How to write store procedure using joins

1 Answers1