Tag Info

New answers tagged

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


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


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


0

I think you're looking at something like this: Select A.Max_id, harddrives.id As id1, harddrives.serial_number, brands.name, harddrives.size, location.name As name1, encryption_type.name As name2, resource.name As name3, backups.start_time, backups.end_time FROM ( Select backups.resource_id, Max(backups.id) As Max_id FROM ...


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


0

harddrives.id As id1 Update it to (Max)harddrives.id As id1 I think that should work.


0

A common pattern for avoiding dups goes something like this: SELECT ... FROM ( SELECT ... SUM(...) FROM w ... GROUP BY ... ) AS x JOIN y ON ... WHERE ... ORDER BY ... This assumes table w has multiple entries that you want to see only once in the result. Hence the GROUP BY to get one copy of each, plus any aggregates you desire. Then you ...


0

Not really an answer, but too long for a comment. There has to be some missing information in your question. If Koza has 3 appointments and 2 payments, customers JOIN addresses JOIN appointments should have a cardinality of 6, but you say that it is 3? Are there some relationship between appointments and addresses that is missing in the question? Why should ...


0

I think this stack exchange link gives a good rundown of why it is doing what it is doing, and I believe it has most to do with the size of the results sets and how they are indexed/sorted in your case. Order and Order Details, from this database are 830 rows and 2155 rows respectively. These are similar results sets where one has 2 rows (roughly) for ...


0

Your problem is [Job Info] isn't defined anywhere. You're asking the parser to join on a column in [Job Info] but you don't list it in your FROM clause.


0

Try wrapping () around it, It could prevent SQL from getting confused with the [] that are wrapped around alias names with spacing. I can see that you are wrapping [] around all the names which is not necessary, you need the [] around table names with spaces between the alias name.. Also for future reference you may want to consider using "_" when creating ...


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


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


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


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


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


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


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

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


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


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;


0

If there are multiple delete and inserts in the same day, then you would either need to sort by date and time or some other sort mechanism to determine the latest value in the table. Based on what you have posted, the same day edits won't have differentiating factors. If your intention is to combine the rows in a select output, you will need a self inner ...


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


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


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


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.


0

It is easy to do if you unpivot the data with SQL Server: Select p.price_id, p.product_id, min(price) From prices as p Unpivot( price for p in (price_one, price_two, price_three, price_four, price_five) ) as p group by p.price_id, p.product_id order by p.price_id Whole query with test data: -- your data ; With prices(price_id, product_id, price_one, ...


0

Using UNION in MySQL, this could work: SELECT price.price_id, price.product_id, MIN(price.min_price) AS MinPrice FROM ( SELECT p.price_id, p.product_id, p.price_one AS min_price FROM test.price AS p UNION ALL SELECT p.price_id, p.product_id, ...


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


0

If you only need to JOIN table A and B, but the configuration is on table C: SELECT A.*, B.b1 FROM db.A JOIN db.C ON (C.a_id=A.a_id) JOIN db.B ON (B.b_id=C.b_id) WHERE `table`.`field` = `parameter` # <-- Aditional


4

I would use select distinct on instead of window function, then just join the days. select distinct on (date, client_id) date, id from orders inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date on start_date <= date and (end_date is null or date <= end_date) order by date, client_id, order_type desc ...


0

Write a function that takes a single date as a parameter and returns a list of date + id's that have an order. Then, use the generate_series as you suggested and call the function over the date range. This is a common strategy when dealing with complex conditions in SQL. I've included some code below, but the SQL answer above is much simpler. Here's ...



Top 50 recent answers are included