score:3
Accepted answer
You can do this with conditional aggregation:
select parentid
from tablename
group by parentid
having sum(case when datavalue = 1 then 1 else 0 end) > 0 and
sum(case when datavalue = 6 then 1 else 0 end) > 0
Another way is use exists
:
select distinct parentid
from tablename t1
where exists(select * from tablename where parentid = t1.parentid and datavalue = 1) and
exists(select * from tablename where parentid = t1.parentid and datavalue = 6)
Another way is counting distinct occurrences:
select parentid
from tablename
where datavalue in(1, 6)
group by parentid
having count(distinct datavalue) = 2
More questions
- Find group of records that match multiple values
- MYSQL Query - find multiple records that match a list of variables and return the max date for each one
- Find unique values that do not exist in multiple columns and tables
- MySQL find records with same customerid that have two different values
- How to read records that matches multiple values in the same field?
- PHP MySQL how to retrieve records that match several different values in a single query
- MySQl return records that match AND/OR against second table values
- Trying to find a single record based on a group of records where there is a 100% match in mysql
- Select data based on condition that match multiple column values
- PDO to find where array values match multiple column values
- Return all records that match array of conditions using CakePHPs find
- MySQL: Filter on multiple values in different rows but return only rows that match both values
- Multiple records returned that don't match a field value
- How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
- Sequelize Query to find all records that falls in between date range
- Can I query a record with multiple associated records that fit certain criteria?
- mysql selecting multiple rows that match array of ids
- Find values that fail sql join on
- MySQL Update multiple rows on a single column based on values from that same column
- how to make HQL that will generate SQL to insert multiple values in one statement?
- MySQL Select rows that match multiple rows in related table
- mysql multiple count and group values in a same field
- Mysql query to find all rows that have the same values as another row
- Find all records in database that are within a certain distance of a set of lat and long points
- Find values across SQL SELECT statements so that the concatenation of any 2 values from each SELECT statement is below a certain max length?
- Check if a pair of records belong to multiple group IDs
- How can I join tables so as to include results that exactly match values from another table, or do not include results at all?
- SQL Query that will omit records with the same ID based on values in another column
- Find values in one column that share same value in different column without knowing the 2nd cols value
- Is it possible to consolidate records from a SELECT statement that have duplicate column values with another record in SQL?
More questions with similar tag
- MySQL, two columns sharing the same join
- Inserting Linux command output into MySQL within Bash script
- Simulating MySql OLD_PASSWORD in .NET or MS SQL?
- embed mysql database in java application
- MySQL Connector for C++ | MySQL_Connection::setReadOnly() exception on setSchema
- Rails 3 - Beta environment trying to create/migrate a blank database name
- mysqldump | mysql yields 'too many open files' error. Why?
- Dynamically loaded content through $.post not retaining click events
- Laravel - define multi table relationships
- SQL query to match multiple values in a single column
- SQL Insert Query, Column count doesnt match value count
- mysql query based on length of string in a column
- Xampp MySQL not starting - “MYSQL not starting on XAMPP 3.2.1 version…”
- transfer MySQL database from old drive
- Retrieve data from two tables with indirect relation(from another table)
- Copying an existing table data from read only database into read-write database MySQL
- Protect row from deletion in MySQL
- Calculate Amount (Qty * Price) in SQL query or UI Code
- mySQL: How to select FROM table WHERE IN LIST and NOT IN another list
- What kind of JOIN should I be using here?
- what is correct sql statement syntax in PHP?
- PHP / MySQL - Safe characters for display names / usernames / passwords, with PDO
- Finding the next available id in MySQL
- How should I handle units of measure in an ingredient database?
- Mysql database trigger
- PHP database delete function only works on first row of database?
- Percentage sign in MySql
- How to connect php to mysql database?
- How do I create an Accessor for a POINT data column in a Laravel 4 model?
- Full text search returning to many results
Source:
stackoverflow.com