All Questions
20 questions
0
votes
1
answer
299
views
Doesn't T-SQL support correlated CROSS JOINs?
This query works
SELECT QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM SWOPS.MIGR.QMCAddresses QMC
CROSS JOIN (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC WHERE FK=8055909) t1
CROSS JOIN ...
0
votes
1
answer
628
views
How to map the newly created records in parent table with the child table?
have a table
Employees
Id Name EmployeeType_ID
1 John 1
2. Mathw 2
EmployeeTypes
Id TypeName
1 Perm
2. Temp
3. Regular
Now I am copying data from one server to ...
0
votes
3
answers
71
views
Query to fine "Order_num" that has no two equal "product_name"
I have a table with below structure:
create table A_TEST
(
order_num int,
product_name varchar(50),
product_amount int,
product_price int
);
Sample data :
insert into a_test (...
- 1,486
0
votes
2
answers
91
views
T-SQL: how to show the corresponding column's row to an aggregating function's result?
Everyone,
I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function?
Related to the dataset below, the task is to find the ...
- 151
1
vote
1
answer
995
views
T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.
How should the query look like to achieve this, since the AVG function is an ...
- 151
1
vote
1
answer
8k
views
Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON
Bear with me - the example code has bit going on and I'll do my best to explain it, below.
with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
...
- 1,645
0
votes
1
answer
2k
views
Query to get the physical file name of the database
Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\...
- 617
3
votes
1
answer
2k
views
Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?
I have a cursor. Within my loop I select a single column and assign the result to a variable. When the select returns an empty rowset, nothing is assigned to the variable. In particular, the variable ...
- 1,645
1
vote
1
answer
98
views
SQL multiple select with where conditions performs really poorly
Data structure is something like this:
Number| ID | DateTime | Status
50000001, 101, 1/1/09 0:10, PO
50000001, 110, 1/1/09 0:11, PO
50000001, 102, 1/1/09 0:15, PO
50000001, 101, 1/1/09 0:10, PCK
...
- 65
2
votes
2
answers
979
views
Overcome SubQuery Returned More Than 1 Result
I found this syntax already answered to a diff question, but I am getting the error of
More than 1 result returned
what do I need to alter in my syntax in order to defeat this error?
Declare @ONE ...
0
votes
1
answer
211
views
Date comparsion within subquery fails with: "out-of-range value"
I have a an update statement which uses a subquery to filter records. My input table contains dates in a varchar field, and some of them are invalid.
CREATE TABLE [dbo].[input](
[id] int,
[...
- 3,243
8
votes
1
answer
1k
views
How can I get the hierarchical values from the below query?
I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.
I need to get all the category IDs and their subcategory ...
- 471
8
votes
3
answers
16k
views
No advantage of using Cross Apply or CTE over inline sub-query
I came across a query like this:
SELECT (SELECT COUNT(1) FROM Orders o WHERE i.ItemId = o.ItemId) [C]
FROM Items i
I changed it to following
;WITH cte_count
AS
(
SELECT COUNT(1) c, OrderId FROM ...
- 287
3
votes
1
answer
2k
views
How to union four different tables
I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL.
I have two other tables that only uses a WHERE clause and both of these are combined using ...
- 267
0
votes
2
answers
665
views
Update specific records of table based on subquery's output (sql server)
have this query which produce below output :
select sec.site,sec.Name,sec.Freq from
( select Name from sec group by name having count(Freq) > 3)as dt
inner join sec on dt.Name = sec.Name
where sec....
- 669
8
votes
2
answers
6k
views
Optimize select on subquery with COALESCE(…)
I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this:
SELECT ...
- 183
-2
votes
2
answers
5k
views
how to solve slow running sub query TSQL
I have a problem with a slow running query.
I have analysed the query with the sql server profiler but I can't seem to find a good solution.
I did find why it is running slow. If ask the full ...
- 11
0
votes
1
answer
1k
views
Take query results and insert into new table [closed]
So I have the following query:
DECLARE @tbl TABLE(value int)
*--The same function but with the number of the random numbers
;WITH Nbrs (n) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM ...
- 1
4
votes
2
answers
5k
views
Why do I need to use a sub query to filter down a grouped select?
If I do this --
SELECT dv.Name
,MAX(hb.[DateEntered]) as DE
FROM
[Devices] as dv
INNER JOIN
[Heartbeats] as hb ON hb.DeviceID = dv.ID
WHERE DE < '2013-03-04'
GROUP BY dv.Name
...
- 1,707
31
votes
2
answers
49k
views
Subqueries' aliases same as main queries' aliases
I have an SQL query whose aliases are the same as some of its subquery's aliases.
For example:
select *
from ROOM r
where ...
(
select *
from ROAD r
...
- 517