Hot answers tagged

5

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way. Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query ...


4

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering: SELECT SUM(owe) FROM ( SELECT (expenses.amount/count(*)) AS owe FROM expenses ...


4

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT. Action: ...


4

Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list: SELECT c.customer_id, p.product_id, CASE WHEN EXISTS ( SELECT * FROM "order" o WHERE ...


4

You have at most 1000 rows from the natural join of R1 and R2 because there are two possible cases: either 1) all the values of R1.C are present in R2.C (i.e. R1.C is a “foreign key” for R2), or, 2) there are values in R1.C not present in R2.C. In the first case you have exactly 1000 rows in the join, since the for each row of R1 there is exactly one row ...


3

Here is a solution that uses CROSS APPLY to find the value for the latest quarter that ends on or before the daily date. If your table is indexed by (ID, Date), this query will be quite efficient with a one-row seek to look up the quarterly value for each daily date. This solution also does not require a calendar table and makes no assumption about the ...


3

Using the PIVOT clause, you can achieve the result like this: SELECT Vehicle_ID, Doors, [Engine Size], [Tire Size], [Color] FROM dbo.YourView PIVOT ( MAX(Value) FOR Property IN (Doors, [Engine Size], [Tire Size], [Color]) ) AS p ; Now, to join the results to your main vehicle table, you can use the above as a derived table (same as ...


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...


2

You can make use of the FOR XML PATH() construct to create the list of features. For instance: USE tempdb; IF OBJECT_ID('dbo.Features') IS NOT NULL DROP TABLE dbo.Features; IF OBJECT_ID('dbo.Versions') IS NOT NULL DROP TABLE dbo.Versions; GO CREATE TABLE dbo.Versions ( VersionID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1) , ...


2

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out. Drawing in all the tables as follows would work: SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2 , ct.specialAttributeA , ...


2

Provided customer_id and p.product_id are primary keys in customer and product select c.customer_id, c.name, p.product_id, nvl((select 'Y' from dual where exists (select null from order o where o.product_id=p.product_id and o.customer_id=c.customer_id)),'N') order_stat from customer c full outer join product p on 1=1


2

There are quite a few ways to achieve your desired results. Undeterministic methods (in the event that many rows in table 2 match one in table 1) UPDATE T1 SET address = T2.address, phone2 = T2.phone FROM #Table1 T1 JOIN #Table2 T2 ON T1.gender = T2.gender AND T1.birthdate = T2.birthdate Or a slightly more concise ...


2

@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 NULL structure you already have. The change you need is minimal. Just add the s.status_message = 'Delivered' condition in the joining ON clause: SELECT ...


2

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, created_at::date AS full_day, COUNT(*) as evs FROM logged_events WHERE logged_events.created_at >= current_date-6 GROUP BY user_id, full_day ), ...


2

The query can be made easier: select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*) from tbl_profile_follow group by least(profile_id,followed_id), greatest(profile_id, followed_id) having count(*) > 1; Using this approach you can also create a unique index on the table create unique index idx_unique_pair on ...


2

Since you have defined quarters using the traditional calendar definition, you don't need to store the "end" of a quarter anywhere, particularly once for every single day in a ~90-day period. (And this could be dangerous if the underlying data is ever expanded to include time, as you may lose all data from the last day of the quarter except that stamped at ...


1

Two remarks: This is called an associative table, not pivot. You already do a join, you just use the old syntax: SELECT * FROM tbl_profile_follow A JOIN tbl_profile_follow B ON (B.profile_id = A.profile_id AND B.followed_id = A.followed_id) AND (B.profile_id = A.followed_id AND B.followed_id = A.profile_id) This is another way ...


1

A date table would be very useful in constructing your query. Look into how to create a data table. Here is a quick video on that, https://www.brentozar.com/archive/2014/12/simply-must-date-table-video/ This query uses a very limited date table to list the Quarter start and end dates for the date you have listed. A real date table would have many more dates ...


1

Something like this: SELECT id FROM main WHERE id NOT IN ( SELECT main_id FROM status_history WHERE status_message IN ('Delivered') ) will do the trick.


1

EXISTS don't actually return any rows. They check for the existence and then move on. Your problem is probably a duplicate caused by the JOINs to the second two tables. Try running this: SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt FROM Person.Person AS p JOIN HumanResources.Employee AS e ON e.BusinessEntityID = p.BusinessEntityID ...


1

When doing your last test trying to pop out the errant row with an except, you need to do a select of all columns (*) not just the few you want. This will almost certainly reveal something. Based on the table name it will likely be that an employee was in one department and then moved to another department, and so with the joins query you were accidentally ...


1

The any Sql DBMS fast runnig pivot is just (MS SQL, you may need replace [] with "" or something for other Sql DBMS) select Vehicle_ID ,max(case Property when 'Doors' then Value end) as [Doors] ,max(case Property when 'Engine Size' then Value end) as [Engine Size] --... from theView group by Vehicle_ID Which MS SQL abbreviates to select ...


1

Answering my own question because I found an answer on StackOverflow that was immensely helpful: http://stackoverflow.com/a/1343174/123729 Using the logic in the question above, the query that works for me doesn't need a PIVOT operation, just a GROUP BY and MIN (or MAX) with a CASE statement, like so: Select Vehicle_ID, Min(Case Property When 'Doors' ...


1

try this - you might get the best of both select manyfield from ( select manyfield , row_number() over (partition by t1.id order by t1.timestamp desc) as rownum from inserted left join sometable t1 on inserted.id = t1.id ) tt where rownum = 1 or t1.id is null


1

Let's say that your tables are called (in order of you mentioning them): Segment, FK, JC_type_A, JC_type_B, etc The main problem that I can see is that you don't know which junc_type will be for which junc. So you will have to left join back to their respective tables and select the one that is not null in your select statement. Something along the ...



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