If we insert a value into fieldname of one table.I should get the value into the same fieldname of second table and i want the count of another fieldname of second table in a gridview.
Can we write a single sql statement to join two tables and to count the fieldname of the second table.
Hi people,
How do I count the number of rows in a table, where those rows relate to another table?
eg. using a JOIN with COUNT
Hello m'dears... As you may have gather I need help...
I have three tables (blog/comment/WM)
the blog lists the entries into the blog i'm creating whereas the comments table holds comments user have made to the blog entries... The WM table holds the usernames of the users... the schema looks like this
blog:
blogId
blogTitle
blogBody
WMid
blogNotes
blogCreated
blogModified
comments:
commentId
blogId
WMid
commentBody
commentDate
WM:
WMid
WMusername
WMpassword
I want to list all blog entries with a JOIN to the WM table to show the username but i also want to COUNT the amount of comments made for each entry...
I'm looking at it and it SEEMS really simple but i can't get my head around it and time's running out any help or pointing out a topic i missed that covers this will be most appreciated
We're all going to hell... I guess I'll see you there!
I am a newbie..and here is my newbie question
I have 2 tables : table1 and table2
SELECT table1.cache_type, table2.log_owner_id
FROM table1, table2
WHERE table1.cache_id = table2.cache_id and table2.log_owner_id=62 and table1.cache_type = "traditional"
This returns 10 rows. But what I would like to do is return the total number of rows..the COUNT.
I tried the following but that does not work:
SELECT COUNT(*) table1.cache_type, table2.log_owner_id
FROM table1, table2
WHERE table1.cache_id = table2.cache_id and table2.log_owner_id=62 and table1.cache_type = "traditional"
any help would be greatly appreciated! thx
Hello,
I am trying to modify this query, I want to get the count of "reviews" that are related to this song. I added the follow line, but I get this error:
count(review.*) reviews,
Error:
Msg 102, Level 15, State 1, Procedure sp_wisetopic_artist_getAllMusic, Line 12
Incorrect syntax near '*'.
Here is the query:
CREATE procedure sp_wisetopic_artist_getAllMusic
@artistId int
as
select
Mp3.*,
Artist.[Name] ArtistName,
Album.*,
count(review.*) reviews,
Id3.bitRate,
Id3.frequency,
Id3.mode,
dbo.SecToMin(Id3.length) length
from wisetopic_mp3 Mp3
-- get artist info
left join wisetopic_artist Artist
on Mp3.userId = Artist.artistId
-- get album info (per song)
left join wisetopic_artist_album Album
on Mp3.albumId = Album.albumId
-- get id3 data (length)
left join wisetopic_mp3_id3 Id3
on Mp3.mp3Id = Id3.mp3Id
-- get review count
left join wisetopic_artist_song_review review
on Mp3.mp3Id = review.mp3Id
where Mp3.UserId = @artistId
order by Mp3.albumId
--select * from wisetopic_artist_song_review
--select * from wisetopic_mp3
I also tried this:
Review.count(*) reviews
Thanks
I have two tables: Thread and Reply and they both have a field called UserID
I need to know the number of rows in both tables where UserID="Chris"
I can do this with two stored procedures and add the results together:
SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'
SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'
but there must be a better way. Can this be written as one stored procedure with some sort of join?
Thanks, Chris
Hi all....
Have a bit of a dilema involving getting a count on an inner join table select.
The projects (WU_Title) are listed in one table (BPI_Upload) and the second table (BPI_ProjectFeedback) holds multiple rows that are added via form where PF_Project is the same as WU_Title.
Here is the current select I am using...
---------------------------------------
SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project
FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
WHERE t1.WU_ProjectStatus = 'Completed'
--------------------------------------
This presently returns:
----------------------------------
ID(1)Project(t1) ID(2) Project(t2)
1 project_a 1 project_a
1 project_a 2 project_a
1 project_a 3 project_a
---------------------------------
What I need is to return only the title and a count from the second table of how many times the title occurs there.
Ultimately ....
Title[project_a] Count[3]
I am having difficulty with where to place the COUNT() within the select.
Any suggestions would be appreciated.
Thanks...
This is my sql string. It counts all the rows in Questions table but it should only count the rows where id in Quizzes matches the quiz column in Questions table.
"select Quizzes.name, Quizzes.id, count(Questions.quiz) as total from Quizzes inner join Questions on Quizzes.id=Questions.quiz"
Why isnt it doing what I want it to do?
I'm very new to this so I will start from scratch and would love any advice from anyone who is more knowledgable than I.
So I have two tables, Game_Schedule and Standings
Relevant columns in Game_Schedule:
Team1 (Represents home team)
Team2 (Represents away team)
Win ('true' represents win for home team)
Loss ('true' represents win for away team)
Relevant column in Standings:
T_Name
Wins
T_Tier
THE OBJECTIVE is to count the number of wins (true values in 'Game_Schedule') for each team in 'Standings' and list them ordered by T_Tier and then Wins.
Sounded easy at first but I haven't had any success.
Here is my LATEST ATTEMPT:
SELECT (Select DISTINCT standings.T_Name from standings), Game_Schedule.Team1, standings.T_Tier, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss, (Select Count(*) FROM Game_Schedule WHERE (standings.T_Name=Game_Schedule.Team1 AND Game_Schedule.Win=1) OR (standings.T_Name=Game_Schedule.Team2 AND Game_Schedule.Loss=1)) AS Win_Counter FROM standings, Game_Schedule ORDER BY T_Tier ASC, Win_Counter DESC
Here is an INNER JOIN attempt that also did not work:
SELECT DISTINCT T_Name, T_Tier, Wins, Game_Schedule.Team1, Game_Schedule.Team2, Game_Schedule.Win, Game_Schedule.Loss FROM standings INNER JOIN Game_Schedule ON standings.T_Name=Game_Schedule.Team1 OR standings.T_Name=Game_Schedule.Team2 ORDER BY Game_Schedule.Win DESC
The query probably speaks for itself. You can see that I had to use a sub select to get the last column. It seems like there must be a better way, but we could not find it. We are on sql 2000. below is the query is the first few rows of the the result set. This query works, being new to sql I am just curious if the sub select could have been avoided in this case. Thanks!
SELECT count(distinct a.store)as [# Stores doing at least 1 Verified Deposit],
b.division,
b.district,
(select count(store_no) from store where district = b.district and fiscal_year = 2008
and current_status = 'a' and owner ='company')as '# of Stores in District'
FROMdepositvariance a,
store b
WHERE a.verified_date >'10/25/2007'
and a.store = b.store_no
and b.fiscal_year = 2008
and b.current_status = 'a'
and b.owner ='company'
Group BYb.division,
b.district
# Store that did at least 1 Verified Deposit division district # of Stores in District
-------------------------------------------- -------- -------- -----------------------
42 200 201 44
28 200 202 40
38 200 203 45
I have the following tables.
Employee table
EmpNum, Department, Gender
100 AAA M
101 AAA F
102 BBB M
103 BBB F
104 AAA M
105 BBB F
EmpProducts table
EmpNum,Item
100 A1
100 A1
100 A2
101 A2
102 B1
102 B3
103 B2
104 A1
104 A2
105 B1
Products table
Deparment, Item, QtyM, QtyF
AAA A1 2 1
AAA A2 1 0
AAA A3 1 1
BBB B1 1 1
BBB B2 2 3
BBB B3 3 3
Each employee need to have a specific amount of product that is assigned to the employees department I need to know if they have to little or to many. The result should be like this.
100 have the correct items for the gender and department so he should not be in the result.
101 A2 1 She is 1 over the 0 she should have.
102 B2 -2
102 B3 -2
103 B1 -1
103 B2 -2
103 B2 -3
104 A1 -1
104 A3 -1
105 B2 -3
105 B3 -3
I made this SQL but i got stock in the count.
Select ep.item, e.Empnum, p.item from employee e
INNER JOIN products p
on p.department = e.department
LEFT JOIN EmpProducts ep
on e.Empnum = ep.EmpNum
and p.items = ep.item
here I need the count of the specific item and compare it against the QtyM if gender is male and against QtyF if gender is female
where
EmpProductCount <> ProductsQty
order by Empnum
Hi,
I have the following problem: I want to join 2 tables but the table that I want to join on has duplicates of the same record.
This is what my query looks like:
SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS YearFROM executionreports AS e INNER JOIN accounts AS a ON e.account = a.accountWHERE (e.ord_status = '0')GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)ORDER BY Expr1
and the output looks like this:
1AA1AA328420061CC1CC45320061CD1CD8420061MA1MA1167320061MA1MA828420067TR7TR2420067TS7TS3696320067TS7TS2676420067TW7TW34420067TW7TW18320067UW7UW3320067VE7VE4320067YP7YP405320067YP7YP23142006TESTTEST142006
The problem is that the count is too high. This is because the account table has several entries with 1MA and 7TS for example. How can I correct it so i basically joins on a distinct set of the account table records?
Thanks,
Tom
Help,
I have 2 tables one called ProjectHeader and the other called ProjectSuppliers. There is a one to many relationships. I€™m trying to get a count of the total number of projects for a certain supplier where the Date Entered is less then 5/1/2206 with the Bid Date greater than 3/31/2206.
When I run the query below the count comes back wrong. I know it€™s because the ProjectSupplier table can have multiply entries of the supplier under the same Projectid.
For example, the query below gives me a count of 206 projects for Miller in Tampa.
Where he really only has 169 projects entered.
I know you can run two queries to get the results.
But because the data is being display on a Web page, I really would like the one SQL statement to handle this.
ProjectHeader Table
ProjectSuppliers Table
ProjectID
ProjectID
Bid Date
SupplierName
Date Entered
District
Modifed By
SELECT Count(ProjectHeader.ProjectID) AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID
WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))
GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]
HAVING (((Count(ProjectHeader.ProjectID))>0))
ORDER BY Count(ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];
Hi,I have 2 tables: tblStatements and tblLines (one to many) AnytblStatements record can have many associated records in tblLines.The search criteria is against tblLines (ie tblLines.fldDateofService
Quote:
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date
FROM ((project_manager
INNER JOIN files
ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending')
INNER JOIN projects
ON projects.project_id = project_manager.project_id)
GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
ORDER BY projects.status, projects.start_date DESC
result of this query:
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
I use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies View RelatedI have two xml source and i need only left restricted data.
how can i perform left restricted join?
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
       S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
       Production.Suppliers AS S LEFT OUTER JOIN
      (Production.Products AS P
        INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
       S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
       Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
I have two select statements, in between select statement taking UNION ALL . I need to avoid the error
Warning: The join order has been enforced because a local join hint is used.
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
View 4 Replies View RelatedI have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies View RelatedI'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
Below is my Implict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow
[Code] ....
and below is explict join query
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow
ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
[Code] ....
the count returned from both the queries is different.
I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.