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.