Search Results

One of the subqueries (derived tables) returns 0 rows and thus the whole query - which is a cross join of 5 tables - returns 0 rows as well. You could rewrite with proper, explicit joins. If you make …

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY: SELECT cont.*, cred.*, 'main' AS contact_type FROM e_contacts AS c …

You have two right parenthesis more than left ones and a missing alias: Select * from wh_acct a -- alias added where a.rundate = (select max(a2.rundate) …

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calcul …

You can add this restriction with either a NOT EXISTS subquery or with LEFT JOIN / IS NULL: SELECT * FROM MyObject INNER JOIN ( SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar' ) as SQ1 on …

The standard behaviour is that in WHERE id = ... in the subquery the id refers to the closest table in scope. And that is t2. … You are lucky though because the WHERE id = t2.id - which is translated as WHERE t2.id = t2.id is not actually needed because you used WHERE id IN (subquery). …

No, they are not equivalent. If Confirmed_Attendance.rsCode is not nullable, then your second query is equivalent to this one: SELECT cs.RSCode , CustomerName , em.EmailId FROM Cu …

First, fix your schema. As soon as possible. There is absolutely no reason to be storing comma separated lists in a table column. Quite the opposite, there a lot of reasons not to. See these answers f …

You can reference a column in a inline subquery but not in a derived table inside an inline subquery. … This would require either using ROW_NUMBER() and encapsulating the whole query ina derived table or turning the inline subquery into a LEFT JOIN. …

Then we have the second part, the subquery with table2. …

@spthorn's answer is correct but IN gives sometimes unexpected results when the columns involved are nullable. If you want to avoid such surprises, it's better to use NOT EXISTS or the LEFT JOIN / IS …

The syntax of you query is not often called "correlated subquery". … A correlated subquery would be like this: SELECT ndo.sku AS ParentSKU, ( SELECT SUM(g.sessions) FROM gasessiondata AS g WHERE g.date >= ndo.FromDate AND g.date …

This would also work: SELECT COUNT(*) FROM ans_checkups WHERE (SELECT visit1_date AS visit UNION ALL SELECT visit2_date UNION ALL SELECT visit3_date UNION ALL …

Note how the WHERE condition of the subquery was moved to the ON clause of the join: SELECT uc.UserContractId, uc.UserContractName, oc.OrderID FROM tblUserContracts AS uc LEFT JOIN …

In the last, external query you cross join foo with t1 and t2, while you only need (and use) columns from foo and t2. So remove the t1 join: WITH ... SELECT FROM ( ... ) foo, t2 ; …