SQL query for matching multiple values in the same column
What you might actually be looking for is relational division, even if your exercise requirements forbid using AND
(for whatever reason?). This is tricky, but possible to express correctly in SQL.
Relational division in prosa means: Find those employees who have a record in the employees table for all existing designations. Or in SQL:
SELECT DISTINCT E1.Employee FROM Employees E1
WHERE NOT EXISTS (
SELECT 1 FROM Employees E2
WHERE NOT EXISTS (
SELECT 1 FROM Employees E3
WHERE E3.Employee = E1.Employee
AND E3.Designation = E2.Designation
)
)
To see the above query in action, consider this SQLFiddle
A good resource explaining relational division can be found here:
http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division
Query with Multiple Values
change the BTACH REF NO
to include SuppSerial
in the WHERE
clause
[BATCH REF NO] = STUFF(( SELECT DISTINCT '; ' + US.BatchNum
FROM OIBT US
WHERE US.Basenum = T0.BaseNum
AND US.WhsCode = T0.WhsCode
AND US.ItemCode = T0.ItemCode
AND US.SuppSerial = T0.SuppSerial
and US.Quantity <> 0
FOR XML PATH('')), 1, 1, '')
MYSQL: Select Query with multiple values from one column
You need to GROUP BY BookID
and a HAVING
clause with the condition that both keywords are linked to that BookID
:
SELECT b.BookID, b.Title
FROM Books b
INNER JOIN KeywordAssignment ka ON ka.BookID = b.BookID
INNER JOIN Keyword k ON k.KeywordID = ka.KeywordID
WHERE k.Keyword IN ('Magic', 'Fantasy')
GROUP BY b.BookID, b.Title
HAVING COUNT(DISTINCT k.Keyword) = 2
This code will return books that are linked to both 'Magic'
and 'Fantasy'
.
If you want either of the 2 keywords then remove the HAVING
clause.
Query with multiple values in a column
The best solution would be to normalize your schema. You should have a separate table with one row for each child, instead of a comma-delimited list. Then you can join with this table to find parent with a specific child. See @themite's answer for an example of this.
But if you can't do that for some reason, you can use FIND_IN_SET
:
WHERE FIND_IN_SET('Alex', children)
How to write Criteria Query that checks for multiple values in same column along with other WHERE conditions?
You can try this code :
ArrayList<String> values= new ArrayList<String>();
list.add("A");
list.add("B");
list.add("C");
//Your creteria
Criteria cr = session.createCriteria(People.class);
//Array of conditions
List<Criterion> conditions = new ArrayList<Criterion>();
Criterion nameCr= Restrictions.eq("name", "Tom");
conditions.add(nameCr);
Criterion codeCr =Restrictions.in("code", values) ;
conditions.add(codeCr);
Conjunction conjunction = Restrictions.conjunction();
for (Criterion criterion : conditions) {
conjunction.add(criterion);
}
cr.add(conjunction);
Related Topics
Create Line Break in Whatsapp Message
How to Change the Background Color of Dropdown List in Select Tag
Send Python Output to HTML Field
Align Text and Image Side to Side With Bootstrap
How to Check If MySQL Results Returned Empty in PHP
Generate an N-Digit Random Number
Php: Fastest Way to Handle Undefined Array Key
How to Remove the Extra Square Bracket from Json Having Multiple Arrays
Install PHP Intl Extension on Macos
Php - Mysqli_Fetch_Array() Expects Parameter 1 to Be Mysqli_Result, String Given
How to Get Data from Another Table Using Id from Another Table
Php Substring Remove Everything in a String After Certain Character
How to Get Multiple Value from Post Variable Using Same Name
Get Image Type from Base64 Encoded Src String
Php How to Determine the First and Last Iteration in a Foreach Loop
Calculate Size in Bytes of Json Payload Including It in the Json Payload in PHP