Query

Question

Hi I have table OWHS with fields BplId,Location. I want list of those reocrd which BPlId has more than 1 Location Thanks

Tuhin Paul · Answer

If you prefer not to use a subquery, you can achieve the same result using a JOIN : SELECT o.BplId, o.Location FROM OWHS o JOIN ( SELECT BplId FROM OWHS GROUP BY BplId HAVING COUNT(DISTINCT Location) > 1 ) sub ON o.BplId = sub.BplId; Explanation of the Alternative Query Subquery ( sub ) : The subquery identifies all BplId values with more than one distinct Location , similar to the previous query. JOIN : The main table ( OWHS ) is joined with the subquery on the BplId column. This ensures that only records with BplId values matching those in the subquery are retrieved.

Tuhin Paul · Answer

To retrieve records from the OWHS table where the BplId has more than one unique Location , you can use SQL. The query will involve grouping the data by BplId and filtering those groups that have more than one distinct Location . SELECT BplId, Location FROM OWHS WHERE BplId IN ( SELECT BplId FROM OWHS GROUP BY BplId HAVING COUNT(DISTINCT Location) > 1 ); Explanation of the Query Subquery ( IN Clause) : The subquery identifies all BplId values that have more than one unique Location . It uses GROUP BY BplId to group the records by BplId . The HAVING COUNT(DISTINCT Location) > 1 condition ensures that only those BplId values with more than one distinct Location are selected. Outer Query : The outer query retrieves all records ( BplId and Location ) from the OWHS table where the BplId matches the list of BplId values returned by the subquery.

Muhammad Imran Ansari · Answer

Hello Ramco, You can use the HAVING clause with GROUP BY to find BplId values that have more than one associated Location . SELECT BplId FROM OWHS GROUP BY BplId HAVING COUNT(DISTINCT Location) > 1; Good Luck!

Pavan Nalluri · Answer

SELECT BplId, COUNT(Location) AS NoOfLoc FROM OWHS GROUP BY BplId HAVING COUNT(Location) > 1; Explanation: The above query displays the BplId for which Id there is count of Location greater than 1, we grouped teh resukt by id and then filtered it based on the count of location

Query

Answers (4)