Need to Return Two Sets of Data With Two Different Where Clauses

Where clause, multiple sets of conditions

'NOT' acts only on one condition. If you don't want any of the other conditions to be returned if true... try surrounding all conditions following the 'NOT' keyword in parentheses. Also, double check where you have your parentheses. I'm still seeing an odd number of those maybe due to pasting.

WHERE NOT 
    (
        (Condition 1)
         OR
        (Condition 2)
     )

You can also try getting away from the 'NOT' keyword by using <> references instead.

Two sql sums in one query with two where clauses

You can combine the two rows with a CROSS JOIN. Those are usually recommended against because if you cross join two tables with M and N rows each, you get MxN rows in the result (which can be a lot), but in this case you know for a fact that M and N are both 1, so MxN is also 1.

SELECT *
FROM (
    SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalSponsor
    FROM Fees WHERE(Sponsor = 'True')
) AS x
CROSS JOIN (
    SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalLoan
    FROM Fees WHERE(StudentLoan = 'True')
) AS y

SQL Two Different WHERE Conditions for Two Columns

If you specify a column name for the COUNT function, it doesn't count NULL values.

So, the simple way would be to use CASE statements to convert the values you don't want counted to NULL

SELECT
    Name,
    COUNT(CASE
            WHEN Occurred_Date >= '2010-01-01' AND Occurred_Date < '2011-01-01'
                THEN Occurred_Date
            ELSE NULL
            END) AS [YTD]
    COUNT(CASE
            WHEN Occurred_Date >= '2010-06-01' AND Occurred_Date < '2011-07-01'
                THEN Occurred_Date
            ELSE NULL
            END) AS [MTD]
FROM Table1
GROUP BY Name

I'm not 100% certain the query engine will let you use CASE within COUNT (I'm not even sure what DB platform you're using), but it gives you the idea. If this way doesn't work, you can write the query using a derived table that will give you the same result.

SQL Statement using Where clause with multiple values

Try this:

select songName from t
where personName in ('Ryan', 'Holly')
group by songName
having count(distinct personName) = 2

The number in the having should match the amount of people. If you also need the Status to be Complete use this where clause instead of the previous one:

where personName in ('Ryan', 'Holly') and status = 'Complete'

Return multiple values for 'Then' clause in an SQL case expression

You can try below - use UNION

DEMO

    select * from
    (
    select a.fileid,contolnames,file1 as files
    from Files a inner join sampletable b on a.fileid=b.fileid
    union
    select a.fileid,contolnames,file2 as files 
    from Files a inner join sampletable b on  a.fileid=b.fileid
  )A order by fileid

OUTPUT:

fileid  contolnames files
1         abc   abc.pdf
1         abc   abcdef.pdf
2         ghf   ghf.pdf
2         ghf   ghfjkl.pdf
3         hjy   hjyui.pdf
3         hjy   hjy.pdf

SELECTING with multiple WHERE conditions on same column

You can either use GROUP BY and HAVING COUNT(*) = _:

SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list

(assuming contact_id, flag is unique).

Or use joins:

SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'

If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.

C# LINQ join with conditional where clause on two different data sets

var collectionAIds = new HashSet<int>(collectionA.Select(colAItem => colAItem.Id));
var itemsInCollectionBNotInCollectionA = collectionB.Where(colBItem => 
                                             !collectionAIds.Contains(colBItem.Id));

basically, we want to get the Ids in collection A, and then select all items from collection B, not in A's list of ids.

A HashSet is optional. it just avoids repetitive O(n) lookups, if you don't use that variable.

p.s. i am assuming int, as the type of id.. use the data type of id for the Hashset.

select rows based on WHERE clause which return multiple rows

You could try using the "IN" operator instead of = in your where clause. This allows you to to do a "contains" instead of comparing with a single value.

select * from Mentor m
where m.id IN
( select ml.id from MentorLanguage ml, StudentLanguage sl
  where ml.language like sl.language 
  group by ml.id )

---

Related Topics