score:0
Find all client/policies that have both types by use of GROUP BY and HAVING. Then use this in your from clause so you get only those client/policies.
select ...
from
(
select client, policyno
from mytable
where trantype in ('NBS','XLN')
group by client, policyno
having count(distinct trantype) = 2
) wanted
inner join sometable on sometable.client = wanted.client and sometable.policyno = wanted.policyno
...
score:1
You could use EXISTS
:
SELECT t1.*
FROM dbo.TableName t1
WHERE t1.TranType IN('NBS','XLN')
AND EXISTS
(
SELECT 1 FROM dbo.TableName t2
WHERE t1.Client = t2.Client
AND t1.PolicyNo = t2.PolicyNo
AND t1.TranType <> t2.TranType
AND t2.TranType IN('NBS','XLN')
)
More questions
- SQL Query: I need both rows if values exist
- SQL query which checks the table and returns yes or no if both values exist in the table
- insert into select from sql query with both query and static values
- SQL Query rows need to be converted to columns dynamically
- How to adjust SQL aggregation by date query result to merge values in same level where nulls exist for some columns
- SQL Server : SELECT when 2 values exist in rows
- Need to get rows where combination of two columns both exist and don't exit
- need to assign values from sql query in single instance
- Need a SQL Query to fill out values so that every missing option is present in the result set
- SQL Server: Need query to get records by comparing one of it's field with different values
- SQL query to remove duplicate column values within distinct rows
- SQL Select Query - Select rows in the table based on the latest values of column
- SQL Query that returns static values into rows instead of columns
- SQL Server : selecting rows based on values gathered in query
- SQL PIVOT query is repeating the same values for all pivoted rows
- sql server query to assign 2 different column values for each half of rows
- SQL Server 2008 Query to Delete All Rows Where Values Are Not Found in 3 Other tables
- sql query to list rows with same column values and then delete a specific row
- Need SQL query which accepts EMPTY values in IN 'clause'
- How to sort rows in SQL that contain both numeric & alphanumeric values in ascending order?
- Query to concatenate column values from multiple rows SQL Server 2008 R2
- SQL query - Difference between the values from two rows and two columns
- I need sql IN query for multiple values in single column?
- SQL - query returning rows that presumably do not exist
- Including rows with no values in SQL query output
- Get 'balanced' column value as 'Y' if both rows has same amt and 'N' if not in SQL query
- SQL query to pull certain rows based on values in other rows in the same table
- How to query for Xml values and attributes from table in SQL Server?
- Delete rows with date older than 30 days with SQL Server query
- SQL query to determine that values in a column are unique
More questions with similar tag
- Casting with bool for checkbox gives error "Specified cast is not valid"
- How to SET IDENTITY_INSERT ON in SQL Server 2008 for multiple tables at once
- SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns
- Search for in a delimited string column
- When executing stored procedure that return the count of "resident" in a table, expecting 4, but got zero instead
- SSIS Project - Catalog Deployment - Environment Variable (for location) accessing file server mapped to a local Z drive - SQL Server Agent issue
- TABLE1 T1, TABLE2 T2 WHERE T1.Blah = T2.Blah - VS - INNER JOIN
- Get the return value of date diff?
- How to replace the column value in select statement in Sql?
- SQLAlchemy order by function result
- Multiple Rows for same identifier
- Update trigger from select statement loop
- Update multiple rows and columns using respective ids in mssql
- Consuming JSON in SQL Server 2012
- Remove Duplicate Nodes from SQL Server Hierarchy
- T-SQL STOP or ABORT command in SQL Server
- Sql inserting arrays using split function
- Get top three records from duplicate records having unique identity
- How to write query to change SQL Server database mode from Recovery to NoRecovery
- Left join using LINQ to SQL
- How to Upload 100MB file?
- Math operation in SQL without select?
- ExecuteNonQuery Returns -1 when the code succeeds
- Can you/should you use SQL Server Service Broker with .NET applications?
- Multipart queries in SQL Server with RODBC
- How to Sum value of Pivoted Columns and add it into another Pivoted Column
- Multiple Database with single Website using Database Overwrite the Connection String
- How to write the quantile aggregate function?
- How to perform arithmetic operation on the outputs of 2 queries
- How to get previous record if current record does not exist in table
Source:
stackoverflow.com