Tagged Questions
0
votes
1answer
55 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
1
vote
1answer
32 views
Joining data from legacy database
I'm trying to figure out how to write this query.
Table 1 - Booking
BOOKING_ID | BOOKING_DATE | etc...
1 | 2013/01/01 |
2 | 2013/01/01 |
Table 2 - BOOKING_PROCEDURE_TYPE
...
1
vote
3answers
158 views
Will performance be better if I specify a filter multiple times?
Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN?
To clarify using code:
select *
from #main_select
left outer join
(
select
main_id
...
-2
votes
0answers
102 views
Cross-product generates duplicate rows, how to unique/distinct the result?
CREATE TABLE tbl_T0(fid int, haz float);
CREATE TABLE tbl_T1(fid int, foo text);
CREATE TABLE tbl_T2(fid int, bar money);
Populate each table with some data, then create views with ROW_NUMBER():
...
6
votes
3answers
189 views
Splitting SQL query with many joins into smaller ones helps?
We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
2
votes
0answers
86 views
How can row estimates be improved in order to reduce chances of spills to tempdb
I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
-2
votes
3answers
97 views
Left Join not giving desired result
I have two Datasets
FEES PAYMENTS
CrsCode InstNo FEE Regno CRSCODE Instno Payment
CA1 -2 100 R1 CA1 -2 100
CA1 -1 200 ...
0
votes
1answer
33 views
Getting the name of the column that was used in a join?
This may be a bit of a weird question:
Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get ...
0
votes
1answer
71 views
Why is there such a huge performance difference in these two join statements?
I have a query which i first wrote as (query has been simplified a bit) :
select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = ...
1
vote
1answer
76 views
Join Calender Table
I have a Datatable and a Calendartable.
I do a join on this tables to get sequential dates if there is no data for this day.
My problem now is that there a several id's and i need a count for each ...
-1
votes
1answer
105 views
Want to show 3 tables in the same gridview and want to update/delete in parallel [closed]
Suppose I have 3 tables. But I want to show them it in same gridview and want to update and delete operations in parallel. How can I do that?
table 1: workshop
id contents demonstration ...
3
votes
2answers
219 views
Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.
We need ...
2
votes
2answers
88 views
Joining 4 tables and 2 counts
I have four tables.
UserMaster
----------
UserKey - PK
UserName
UserID - UNIQUE
UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
LoginTime
LogoutTime
CallCenter
----------
...
-3
votes
3answers
481 views
Join tables on a partial string match
input: I have a table containing (any number of) user-supplied strings:
╔═══════╗
║ Value ║
╠═══════╣
║ To ║
║ An ║
╚═══════╝
And a table of user details:
╔════════╦══════════╗
║ UserID ║ ...
3
votes
2answers
235 views
SQL ANSI JOIN precedence
I have a query that looks like this:
SELECT *
FROM TBLA A
LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
JOIN TBLD D ON C.Col5 = D.Col6
In which order ...
1
vote
0answers
136 views
Multi-Table Join to Single Destination
I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.
To ...
1
vote
1answer
129 views
Querying multiple tables
I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.
I have the following tables/fields:
SR_service: stores ticket ...
3
votes
1answer
241 views
Build a three table join with a recusive table in the middle?
I have three relevent tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key
Part part number
PartGroupID Foreign key
sample data:
1 ...
0
votes
3answers
477 views
INNER JOIN making COUNT(*) slow
I have a very simple query:
SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id
With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
3
votes
3answers
449 views
CROSS JOIN with temp table and CASE statement
I'm trying to CROSS JOIN a table with a view. Here is my code:
SELECT *
FROM ( SELECT * ,
CASE TypeId
WHEN 1 THEN InGs828S
WHEN ...
9
votes
2answers
1k views
SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
0
votes
3answers
165 views
Is adding rows better then adding columns in case their value is not consistent?
In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
5
votes
2answers
263 views
How to use merge hints to isolate complex queries in SQL Server
I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
6
votes
1answer
179 views
Table order in Join Query
if i say:
Table1 left join Table2
is it the same as saying
Table2 right join Table1
?
In other words, should i expect to get the same results from 2 identical queries where the only thing that is ...
2
votes
1answer
526 views
Is it correct, order of where clause doesn't matter when it is used with join?
I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.
I tried options like disabling rules and checked actual query ...
6
votes
3answers
1k views
Why is this Full Outer Join not working?
I've used Full Outer Joins before to get my desired results, but maybe I don't fully understand the concept because I am not able to accomplish what should be a simple join.
I have 2 tables (which ...
5
votes
2answers
652 views
The order of INNER JOIN's
I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.
I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead ...
0
votes
1answer
120 views
How should row-specific metadata be created handled for an outer join view?
I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
0
votes
2answers
1k views
Joining two tables on multiple criteria
I have two tables products and positions.
products:
id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)
positions:
id BIGINT PRIMARY KEY,
position ...
1
vote
3answers
814 views
Grouping results to get unique rows after multiple joins
this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer.
disclaimer : I don't have full control over the db ...
6
votes
2answers
2k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
3
votes
2answers
266 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
1
vote
1answer
345 views
SQL Server: join two tables with “valid-to” dates
I have two tables like so:
T1_Key, T1_Data, T1_ValidUntil
1 A 2000-01-01
1 B 2000-06-30
2 C 2005-05-31
3 D 2004-12-31
3 E ...
2
votes
1answer
437 views
SQL Return Count of Answers with 0's for Choices from another table
My question went dead after one unhelpful answer on SO here
I need this to be optimized because it will be called often. It is going into a stored procedure.
I am given @slide (tblILDSlide.id) and ...
4
votes
3answers
1k views
Query to normalize table/combine row text
I have a table (call it oldTable) with columns like so:
ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)
The primarykey is multi-part: ID+Rank+TextLineNumber.
I'm trying to ...
