Count Subquery

[Eleven]

Hi everybody,

I hope someone will be able to help me out, i'm quite new to databases.

I've got 3 tables, check the image below.

I've created a view, now I want to have another one that will use the current view to show all canisters that have been through more than one terminal. I know i need to have a COUNT somewhere but I'm not really sure how to go about doing it.
This new view should contain the TT number, Dallas number and DateTime of the last time they went through a terminal and which terminal it was.

Your help will be greatly appreciated!

[amitpatel66]

Hi everybody,

I hope someone will be able to help me out, i'm quite new to databases.

I've got 3 tables, check the image below.

I've created a view, now I want to have another one that will use the current view to show all canisters that have been through more than one terminal. I know i need to have a COUNT somewhere but I'm not really sure how to go about doing it.
This new view should contain the TT number, Dallas number and DateTime of the last time they went through a terminal and which terminal it was.

Your help will be greatly appreciated!

Not able to view your image.
Anyways, could you provide the structure of your view for reference of our experts.

[Eleven]

Not able to view your image.
Anyways, could you provide the structure of your view for reference of our experts.

I have 3 tables, CanisterTrackin g, Terminals, and Canisters.

CanisterTrackin g:
------------------------------
fkTerminalID
TrackingDateTim e

Terminals:
----------------------
fkTerminalKey
TerminalId
fkBankId

Canisters
-------------------
fkCanisterLongI d [Dallas Number]
CanistersTTNum
fkBankKey

SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
                      dbo.CanisterTracking.TrackingDateTime
FROM         dbo.CanisterTracking INNER JOIN
                      dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
                      dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime

Let me know if I left anything out.

Thanks!

[amitpatel66]

I have 3 tables, CanisterTrackin g, Terminals, and Canisters.

CanisterTrackin g:
------------------------------
fkTerminalID
TrackingDateTim e

Terminals:
----------------------
fkTerminalKey
TerminalId
fkBankId

Canisters
-------------------
fkCanisterLongI d [Dallas Number]
CanistersTTNum
fkBankKey

SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
                      dbo.CanisterTracking.TrackingDateTime
FROM         dbo.CanisterTracking INNER JOIN
                      dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
                      dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime

Let me know if I left anything out.

Thanks!

Try this:
[code=sql]

SELECT CanisterID, CanisterTTNum, COUNT(TerminalI d) FROM
(SELECT c.fkCanisterLon gId AS "CanisterID ", c.CanistersTTNu m AS "CanisterTTNum" ,t.TerminalId AS "TerminalID",ct .TrackingDateTi me AS "TrackingDateTi me" FROM TrackingDateTim e ct, Terminals t, Canisters c WHERE t.terminalid = ct.fkTerminalID AND t.fkbankid = c.fkbankkey)
GROUP BY CanisterID, CanisterTTNum

[/code]

[Eleven]

Try this:
[code=sql]

SELECT CanisterID, CanisterTTNum, COUNT(TerminalI d) FROM
(SELECT c.fkCanisterLon gId AS "CanisterID ", c.CanistersTTNu m AS "CanisterTTNum" ,t.TerminalId AS "TerminalID",ct .TrackingDateTi me AS "TrackingDateTi me" FROM TrackingDateTim e ct, Terminals t, Canisters c WHERE t.terminalid = ct.fkTerminalID AND t.fkbankid = c.fkbankkey)
GROUP BY CanisterID, CanisterTTNum

[/code]

Thanks Amit..

Tried it and the count is 9814 for all the records, that can't be right.

I made a couple of changes to the code coz i kept getting errors
[code=sql]
SELECT CanisterID, CanisterTTNum, COUNT(TerminalI D) AS Count
FROM (SELECT c.fkCanisterLon gID AS CanisterID, c.CanistersTTNu m AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDate Time
FROM dbo.Canisters AS c INNER JOIN
dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
dbo.CanisterTra cking AS ct ON t.fkTerminalKey = ct.fkTerminalID ) AS derivedtbl_1
GROUP BY CanisterID, CanisterTTNum
[/code]

And I also need to have the date and the TerminalID on the results.

[amitpatel66]

Thanks Amit..

Tried it and the count is 9814 for all the records, that can't be right.

I made a couple of changes to the code coz i kept getting errors
[code=sql]
SELECT CanisterID, CanisterTTNum, COUNT(TerminalI D) AS Count
FROM (SELECT c.fkCanisterLon gID AS CanisterID, c.CanistersTTNu m AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDate Time
FROM dbo.Canisters AS c INNER JOIN
dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
dbo.CanisterTra cking AS ct ON t.fkTerminalKey = ct.fkTerminalID ) AS derivedtbl_1
GROUP BY CanisterID, CanisterTTNum
[/code]

And I also need to have the date and the TerminalID on the results.

I had a feeling that the query might not display correct results because there is no proper join with canister table. It is not correct to join using bankkey because same bankid can be assigned to more than one canister and the query will take wron count. One change that I would suggest you is to include canisterlongID in canisterTrackin g table so that we can use that column for joining. The table design is INCORRECT which is causing this problem

[Eleven]

I had a feeling that the query might not display correct results because there is no proper join with canister table. It is not correct to join using bankkey because same bankid can be assigned to more than one canister and the query will take wron count. One change that I would suggest you is to include canisterlongID in canisterTrackin g table so that we can use that column for joining. The table design is INCORRECT which is causing this problem

There is already a fkCanisterId in the CanisterTrackin g table that matches the CanisterLongID in the Canisters table, i just didn't think about using that.
I didn't design the database, so i'm also noticing some things now.

[amitpatel66]

There is already a fkCanisterId in the CanisterTrackin g table that matches the CanisterLongID in the Canisters table, i just didn't think about using that.
I didn't design the database, so i'm also noticing some things now.

Now Here you go. This will make the job simpler.

Try this:

[code=sql]

SELECT c.canisterlongI D, c. canisterTTNum,k .cnt,x.terminal id,x.trackingti me FROM
(select fkcanisterid, COUNT(terminali d) cnt FROM canistertrackin g GROUP BY fkcanisterid) k,(select ct.fkcanisterid , ct.terminalid,c t.trackingtime FROM canistertrackin g ct, terminal t WHERE t.terminalid = ct.terminalid AND ct.trackingtime = (SELECT MAX(trackingtim e) FROM canisertracking WHERE fkcanisterid = ct.fkcanisterid )) x, canister c
WHERE c.canisterlongI D = k.fkcanisterID
AND k.fkcanisterID = x.fkcanisterID

[/code]