Tag Info

Hot answers tagged

6

Ok, but the column "item_cnt" does not exist in the tables, then what column is it serving as an alias? The column item_cnt may not exist in the base tables (we believe you, we didn't go and search the linked documents.) But this part of the code: FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i ...


5

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you: Sample tables and data CREATE TABLE SupplierCountry ( SupplierName varchar(50) NOT NULL, CountryName varchar(50) NOT NULL ); INSERT SupplierCountry (SupplierName, CountryName) VALUES ('Supplier A', 'USA'), ('Supplier A', ...


5

Edit regarding fields having different types, not just decimal. You can try to use sql_variant type. I never used it personally, but it may be a good solution for your case. To try it just replace all [decimal](38, 10) with sql_variant in the SQL script. The query itself remains exactly as it is, no explicit conversion is needed for performing the ...


5

You really should have a junction table for the courses a student is taking, rather than jamming comma-separated values into a single tuple. If you think this is the last problem you'll have because of this sub-optimal design, you're in for a big surprise. You really should have the owners of this project go read up on normalization - yes it's painful to ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


4

Here is another approach: SELECT di.name, di.date, x.field, x.oldValue, x.newValue FROM @diffInput AS di LEFT JOIN dbo.myTable AS mt ON mt.version = @version AND mt.name = di.name AND mt.date = di.date CROSS APPLY ( SELECT 'fieldA', mt.fieldA, di.fieldA WHERE NOT EXISTS (SELECT mt.fieldA ...


3

First, you have conditions duplicated in the ON and the WHERE clause. That is not needed, the condition are only needed once, preferably in the ON clause as they are used for the join of the two tables. But that is not what's causing the slow execution (and the lost connections). The reasons for being slow are three: you are joining a 63K with a 363K ...


2

Completely rearranged answer. I've added some data to the player and team tables to make the answers more general - see bottom of post for all DDL (CREATE TABLE tab_name...) and DML (INSERT INTO tab_name VALUES...) used in this answer. I also created the season table (unchanged from the OP's - i.e. your) original data. BTW, welcome to the forum. But you ...


2

I agree with oNare that adding a sqlFiddle that builds a simplified version of your data model, shows what you have tried so far, and shows the desired results would be most helpful. That said, you might want something like the following in order to avoid double-counting the sales for any customers that were given multiple offers. I've put inline comments ...


2

If I'm understanding your needs correctly, this would probably work... SELECT t1.Date, t1.Id, t1.For_Id, t1.New_Value, t2.Old_Value FROM table t1 JOIN table t2 ON t1.Date = t2.Date AND t1.Id = t2.Id AND t1.For_Id = t2.For_Id WHERE t1.New_Value IS NOT NULL AND t2.Old_Value IS NOT NULL;


2

Of course your data is denormalized, otherwise it would be a simple MIN(price) :-) If you got luck your DBMS supports a LEAST function: LEAST(price_one,price_two,price_three,price_four,price_five), A huge CASE is supported in every DBMS: CASE WHEN price_one <= price_two AND price_one <= price_three AND price_one <= price_four AND ...


2

Try this statement. UPDATE ProcurementPortal.orders SET orders.lat = geo.latitude, orders.lon = geo.longitude FROM ProcurementPortal.orders AS orders INNER JOIN ProcurementPortal.cities_extended AS geo ON orders.city = geo.city AND orders.state = geo.state_code


2

The composite index on (state, city) will not be used if you use the function trim in your query. You may want to update both field in both tables first: UPDATE orders SET oState=TRIM(oState), oCity=TRIM(oCity); UPDATE cities_extended SET state_code=TRIM(state_code), city=TRIM(city); Then run the query without trim On a side note, your index is better to ...


2

Assuming you always have pair , the following should give you desired result: SELECT a.event_id ,a.pair,a.versus, a.score as score1, b.score as score2 FROM table_name a INNER JOIN table_name b on (a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair) Side notes. 1. It makes sense to have a surrogate primary key column in ...


1

Same solution as the one provided by Aaron Bertrand when it comes to building the comma separated values but a bit different in connecting CourseMaster.CourseId with the values in StudentMaster.Course. SQL Fiddle MS SQL Server 2014 Schema Setup: create table dbo.CourseMaster ( CourseId char(2), CourseName char(3) ); create table dbo.StudentMaster ( ...


1

You could use multiple SELECT Statements, like this: SELECT CHT.ID, (SELECT SUM(TOTAL_E + TOTAL_F + TOTAL_STOC) FROM DEP_DEZ WHERE FK_ID_CHESTIONAR = cht.ID), (SELECT SUM(MASA_TOTALA) FROM CAR_EXP WHERE FK_ID_CHESTIONAR = CHT.ID) FROM CHEST cht GROUP BY CHT.ID


1

Part of the problem is that DOUBLE(8,2) rounds to 2 decimal places, thereby giving a different value than DOUBLE. Do not use (m,n) on FLOAT or DOUBLE. Suggest you make that change before doing the UPDATEs. Since you are talking about cities, DOUBLE is gross overkill and takes 16 bytes for the pair. DECIMAL(4,2) for latitude and DECIMAL(5,2) would add up ...


1

The problem as shown is transforming relational calculus, of which SQL is a variant, into relational algebra, which consists of the original operators Codd defined on relations. I will assume that the EMP, ASG, and PROJ represent employees, projects, and the assignment of employees to projects. The query, as stated in relational calculus, is asking for the ...


1

I would create an index on (monitor_id, timestamp). It should be enough. If not, I'd use LATERAL JOIN. SELECT T.id ,T.timestamp ,T.value FROM controller_monitor INNER JOIN LATERAL ( SELECT controller_monitor_reading.id ,controller_monitor_reading.timestamp ...


1

Your query is giving you 12 num_of_players because your counting just the subquery returned rows, if you run SELECT COUNT(*) FROM teams INNER JOIN players ON teams.team_id = players.team_id; you will see what you're really doing. To fix your syntax just one more LEFT JOIN: SELECT teams.team_name, COUNT(players.player_id) as num_of_players, ...


1

This query requests the cartesian product between incidents_incident and json_array_elements(...). When one of the elements of a product is empty, the result is empty. This result doesn't depend on the WHERE clause or the fact that one the product comes from json_array_elements(). An empty table would have the same effect. Presumably you want an ...


1

Check http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle. Here the LISTAGG function is explained. This functionn delivers what you need.



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