Tag Info

New answers tagged

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%'. ...


0

If you need all users data who commented then inner join is enough as you have already implemented. SELECT com_des,com_doc,usr_name FROM tbl_user JOIN tbl_comment ON usr_id=stud_id above query will return only those records from tbl_user table where these users have records in tbl_comment. If a user in tbl_user has no corresponding record in tbl_comments ...


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 = ...


0

I found one way that works, finally. 'm not sure why I was throwing SQL syntax errors before (I didn't save a copy of my old query attempts) but the following worked, as a JOIN inside a SUBQUERY: SELECT skill_list_expanded.description FROM skill_list_expanded WHERE skill_list_expanded.skill_id = 1 AND skill_list_expanded.subclass = 1 AND ...


0

This works - I had the JOIN wrong UPDATE `events` INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`) INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`) SET events.Name = plays.Play WHERE events.Name IS NULL and plays_in_events.PlayID = 1 and the earlier comments were quite right, but it took me ...


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


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 ...


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 ...


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 ...


0

Proposed Solution #1 ALTER TABLE cities ADD UNIQUE INDEX idx_city_state (city,state_code); ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code); CREATE TABLE cities_new SELECT A.city,A.state_code FROM cities A LEFT JOIN cities_extended B USING (city,state_code) WHERE B.city IS NULL ; INSERT INTO cities_extended ...


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, ...


0

Try the following query, select t1.id, t1.city, t1.state, t2.state, t2.zip, t2.lat, t2.lon, t2.county from city2 t1,city1 t2 where t1.city=t2.city; Hope this helps!


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` ...


0

This makes some assumptions but it will do: SELECT prd.id, prd.height, prd.fabric, MAX(pri.price) AS price FROM products AS prd JOIN prices AS pri ON (prd.height >= pri.min_ht OR pri.min_ht IS NULL) AND (prd.fabric = pri.fabric OR pri.fabric IS NULL) GROUP BY prd.id ; The above assumes that the more details a ...


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 ...


0

t is not always accurate to the business requirements to unpivot the table as you propose. For example, in a tournament scheduling database the table for Game will always have two distinct FK's to Team, one labelled HomeTeam and the other labelled VisitingTeam. Unpivoting this eliminates the business requirement that a Game is always between exactly two ...


0

Alternative - joining once on [list].[id] IN (reference_1, reference_2,...) and then pivoting results may give your better execution plan, but you need to check it. In some cases multiple joins worked faster for me, in others - one join outperformed multiple...


0

Well unfortunately your query is wrong. There is no location column in the ps table as you have declared, so you cannot use it as the key for joining the two tables together. Try this and use product_reference as your joining key, as it exists in both tables: SELECT p.product_reference, p.productName, ps.physicalStock, FROM products AS ...


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 ...


0

Check this post out from a similar request to find records NOT IN another table. MySQL “NOT IN” query SELECT * FROM Table2 WHERE Table2.principal NOT IN (SELECT principal FROM table1) Hope that helps!


0

My naive sugestion is select * from (<your first query>) as one inner join (<your second query>) as two on one.customer_id = two.customer_id; There are a lot of performance improvement that could be made over this, however, with more thorough refactoring.


0

Here is what you asked for, but I think you would want to further aggregate this result set by GROUP BY customer_id to roll up the joined table. If that is the case, then simply add GROUP BY o.customer_id. SELECT o.increment_id, o.customer_id, b.LastOrderMonth, ROUND(o.base_grand_total * o.base_to_global_rate,2) as revGBP, ...


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 ...



Top 50 recent answers are included