Tag Info

Hot answers tagged

8

No, a self join would not really help, but if your database system has an EXCEPT operator (or something similar, in Oracle it is called MINUS) it would. Here's how you get your result: SELECT customer FROM Sales WHERE item = 1 AND customer NOT IN (SELECT customer FROM Sales WHERE item = 3) If you have an EXCEPT operator: SELECT customer FROM Sales WHERE ...


4

Another approach using aggregation: SELECT customer FROM sales GROUP BY customer HAVING MAX( CASE item WHEN 1 THEN 1 WHEN 3 THEN 2 ELSE 0 END ) = 1; the Case converts the item-list into 1 for the good item and 2 for the unwanted item, every other item to zero. The maximum will ten be 1 if a good item is ...


3

In this many-to-many relation between customers and items, you want to find customers with item A and with nonexistent item B. To add a "row does not exist" condition to an SQL query, you can use a LEFT JOIN, which represents nonexistent rows as NULL values, followed by a WHERE some_NOT_NULL_column IS NULL. In a nonexistence join, the ON clause has to ...


2

Performance It depends A LOT on the actual data (are there many people having read a few books each, or a few people having read many books), skewing (are there some power-readers?) and the books which you query - like ypercube mentioned the bible. And this is before the optimizer really sets in and decides to completely rewrite your query because it ...


2

Without query execution plan, my first thoughts are: I. Use datetime variables to remove implicit convertion and use index. DECLARE @day as datetime2(2) = '20141007' DECLARE @dayStart as datetime2(2) = CONCAT(@day,' 00:00:00.000') DECLARE @dayEnd as datetime2(2) = CONCAT(@day,' 23:59:59.999') II. Change EXISTS for INNER JOIN EXISTS (Select * FROM Acks ...


2

I suggest your first option, with two improvements and some simplifications. ( SELECT 1 -- irrelevant what you select here FROM client_category_price WHERE sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1 -- may be redundant ) UNION ALL -- not just UNION ... UNION ALL ( SELECT 1 FROM work_order_item WHERE sellable_id = ...


1

The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders. You'll have to move the condition about o.date from the WHERE clause to the joining ON: SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total FROM people p LEFT OUTER JOIN orders o ON ...


1

select * from dbo.Sales s cross apply ( select top (1) * from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date order by ph.Date desc ) ca;


1

It says like syntax error, which you missed the , SELECT Customer.CustNo, Customer.FirstName, Customer.LastName, `Order`.OrderNo, `Order`.OrderDate, Employee.EmpNo, Employee.FirstName, Employee.LastName, Product.ProdNo, Product.ProdName, CONCAT('$', ProductInOrder.Qty * SUBSTRING(Product.Price, 2)) AS `Order Cost` ...


1

You could use a self join, but I would recommend using EXISTS (or in this case, NOT EXISTS) instead (and it is more efficient than using IN): SELECT * FROM foo WHERE item = 1 AND NOT EXISTS (SELECT 1 FROM foo AS f WHERE f.customer = foo.customer AND item = 3);


1

There are many ways to do this. Most common are with a LEFT JOIN / IS NULL check, NOT IN or NOT EXISTS subquery. Here's a solution with the 3rd option: SELECT t2.id, t2.name FROM table2 AS t2 WHERE NOT EXISTS ( SELECT * FROM table3 AS t3 WHERE t3.table1_id = @t1_id -- the t1.id value (choice) that you want to check AND ...


1

Solution this is not exactly what i asked for but it solves the problem of the original query to fail on H2 Database thanks ypercube your suggestion is correct SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT CASE WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid END AS nlsGuid ...


1

Looking at your comments, I can see that the problem is not the JOINs, but the LIKE '%term%' operators. There are several options here, but assuming you are using MyISAM for your tables, or InnoDB and a MySQL version equal or newer than 5.6, you may use FULLTEXT indexes. MySQL implementation is not perfect, but it will work way better than using '%LIKE%'. ...


1

It looks like your currency_pairs_ticker is joining the wrong fields. your existing join: left join currency_pairs_ticker as T on P.pair_id = T.currency_ticker_id corrected join: left join currency_pairs_ticker as T on P.pair_id = T.currency_pairs_pair_id The complete sql SELECT distinct E.exchange_id, E.exchange_name, P.pair_id, ...



Only top voted, non community-wiki answers of a minimum length are eligible