Query

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!

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

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

1. Subquery (sub) :

   • The subquery identifies all BplId values with more than one distinct Location, similar to the previous query.

2. 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.

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

1. 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.

2. 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.