Count With Join

May 2, 2006

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!

View 3 Replies


ADVERTISEMENT

Using COUNT With JOIN

Feb 23, 2007

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

View 3 Replies View Related

Join And Count

Apr 28, 2007

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.

View 28 Replies View Related

COUNT(*) With A JOIN??

Mar 9, 2007

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

View 3 Replies View Related

Get A Row Count Using An Inner Join

Apr 15, 2007

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

View 4 Replies View Related

How Do I Join Two Tables To Get A Row Count?

Jan 10, 2007

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

View 3 Replies View Related

Count On Inner Join Tables

Jul 23, 2003

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

View 7 Replies View Related

Count Rows With Join

Jun 21, 2014

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?

View 4 Replies View Related

Need Help With Inner Join / Count On Sports Db

Nov 4, 2007

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

View 6 Replies View Related

Using Count On Both Sides Of A Join

Mar 13, 2008

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

View 5 Replies View Related

Compare Count Left Join?

Jul 10, 2014

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

View 7 Replies View Related

Join Tables, Count Problems

Apr 19, 2006

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

View 7 Replies View Related

Join Tables Count Problems

May 8, 2006

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];

 

View 6 Replies View Related

Getting Count(*) From Left Side Of Join Using WHERE On Right Side Of Join

Jul 3, 2006

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:

View 2 Replies View Related

COUNT Function And INNER JOIN On Multiple Tables

Jun 23, 2014

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:

View 5 Replies View Related

Simple Group By And Count With Join Not Matching Between Sql Server And Sql CE

Apr 2, 2007

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

View 3 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

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

View 5 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

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

View 1 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

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.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

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'

View 9 Replies View Related

In SQL 2000 Can I Use Count() To Count A Column?

Nov 26, 2007

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
 

View 5 Replies View Related

Table Row Count + Index Row Count

Jul 23, 2005

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 Related

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

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.

View 5 Replies View Related

Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used

Dec 23, 2014

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.

View 9 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

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?

View 1 Replies View Related

Obtain Unit Percent With Unit Count Divided By Total Count In Query

Aug 21, 2007

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

View 1 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

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.

View 2 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

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 Related

Merge Join (Full Outer Join) Never Finishes.

Jun 5, 2006

I 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 Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'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















View 1 Replies View Related

Page 2 - Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

Apr 24, 2007

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

View 2 Replies View Related

Changing From Implicit Join To Explicit Join

Dec 24, 2013

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.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved