Query With Multiple Values in a Column

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);