KoldFuzun
asked on
Select two records based upon value of both
SELECT COUNT(A.ParamValue) AS StateCount, S.StateName
FROM tblAppLogger A INNER JOIN
tblStates S ON A.ParamValue = S.StateID AND A.ParamName = 'StateID'
GROUP BY A.ParamValue, S.StateName
ORDER BY StateCount DESC, S.StateName
The above query selects records to display the States and how many times they occur in my table.
I would also like to do the same where ParamName = City, but I want to associate the City with the state searched. Unfortunately because of the way the logging system works, the state is contained in another record, ParamName = 'StateID' & ParamValue = (the actual ID). The commonality is that there is a user Identifier (GuestCookieUUID) and a matching time they submitted their search (DateTimeSubmitted).
Is this even possible?
FROM tblAppLogger A INNER JOIN
tblStates S ON A.ParamValue = S.StateID AND A.ParamName = 'StateID'
GROUP BY A.ParamValue, S.StateName
ORDER BY StateCount DESC, S.StateName
The above query selects records to display the States and how many times they occur in my table.
I would also like to do the same where ParamName = City, but I want to associate the City with the state searched. Unfortunately because of the way the logging system works, the state is contained in another record, ParamName = 'StateID' & ParamValue = (the actual ID). The commonality is that there is a user Identifier (GuestCookieUUID) and a matching time they submitted their search (DateTimeSubmitted).
Is this even possible?
ASKER
The city is actually held in ParamValue in tblAppLogger
the stateID is held in ParamValue as well, in tblAppLogger
The StateName is held in tblStates
This is from an app which logs form and URL params, and I am running reports on the param values
the stateID is held in ParamValue as well, in tblAppLogger
The StateName is held in tblStates
This is from an app which logs form and URL params, and I am running reports on the param values
ASKER
just to clarify:
ParamName = URL or Form param name
ParamValue = URL or Form Param value
ParamName = URL or Form param name
ParamValue = URL or Form Param value
You may need to clean up a few things like setting the actual parameter name for the GuestCookieUUID and the DateTimeSubmitted but this may get you the results you were looking for.
Basically, create another join to the tblAppLogger on the cookie and timestamp since this seems to be your ownly references. You should be able to pick up the ParamName by joining back to itself like this.
SELECT COUNT(A.ParamValue) AS StateCount, ISNULL(C.CityName, '') as CityName, S.StateName
FROM tblAppLogger A
JOIN tblStates S
ON A.ParamValue = S.StateID AND A.ParamName = 'StateID'
JOIN tblAppLogger C
ON A.(GuestCookieUUID) = C.(GuestCookieUUID)
AND A.(DateTimeSubmitted) = C.(DateTimeSubmitted)
AND C.ParamName = 'CityID'
GROUP BY A.ParamValue, S.StateName, C.ParamName
ORDER BY StateCount DESC, S.StateName
Basically, create another join to the tblAppLogger on the cookie and timestamp since this seems to be your ownly references. You should be able to pick up the ParamName by joining back to itself like this.
SELECT COUNT(A.ParamValue) AS StateCount, ISNULL(C.CityName, '') as CityName, S.StateName
FROM tblAppLogger A
JOIN tblStates S
ON A.ParamValue = S.StateID AND A.ParamName = 'StateID'
JOIN tblAppLogger C
ON A.(GuestCookieUUID) = C.(GuestCookieUUID)
AND A.(DateTimeSubmitted) = C.(DateTimeSubmitted)
AND C.ParamName = 'CityID'
GROUP BY A.ParamValue, S.StateName, C.ParamName
ORDER BY StateCount DESC, S.StateName
ASKER
I think you are definitely close. The only problem is it seems to be only returning the first record....
SELECT COUNT(C.ParamValue) AS CityCount, A.ParamValue AS City, C.ParamValue AS StateID, S.StateName
FROM tblAppLogger C INNER JOIN
tblStates S ON C.ParamValue = S.StateID AND C.ParamName = 'StateID' INNER JOIN
tblAppLogger A ON C.CookieUUID = A.CookieUUID AND C.DateTimeSubmitted = A.DateTimeSubmitted AND A.ParamName = 'City'
GROUP BY A.ParamValue, S.StateName, C.ParamValue
ORDER BY CityCount DESC, S.StateName
SELECT COUNT(C.ParamValue) AS CityCount, A.ParamValue AS City, C.ParamValue AS StateID, S.StateName
FROM tblAppLogger C INNER JOIN
tblStates S ON C.ParamValue = S.StateID AND C.ParamName = 'StateID' INNER JOIN
tblAppLogger A ON C.CookieUUID = A.CookieUUID AND C.DateTimeSubmitted = A.DateTimeSubmitted AND A.ParamName = 'City'
GROUP BY A.ParamValue, S.StateName, C.ParamValue
ORDER BY CityCount DESC, S.StateName
I get the impression that you want the CityCount for all cities for a chosen state. If this is true, you are too close...there is extra code for chosing a city as well...here it is without the chosen city:
SELECT CityCount = COUNT(C.ParamValue)
,City = A.ParamValue
,StateID = C.ParamValue
,S.StateName
FROM tblAppLogger C
,tblStates S
,tblAppLogger A
WHERE C.ParamValue = S.StateID
AND C.ParamName = 'StateID'
AND C.CookieUUID = A.CookieUUID
AND C.DateTimeSubmitted = A.DateTimeSubmitted
GROUP BY A.ParamValue
,S.StateName
,C.ParamValue
ORDER BY CityCount DESC
,S.StateName
SELECT CityCount = COUNT(C.ParamValue)
,City = A.ParamValue
,StateID = C.ParamValue
,S.StateName
FROM tblAppLogger C
,tblStates S
,tblAppLogger A
WHERE C.ParamValue = S.StateID
AND C.ParamName = 'StateID'
AND C.CookieUUID = A.CookieUUID
AND C.DateTimeSubmitted = A.DateTimeSubmitted
GROUP BY A.ParamValue
,S.StateName
,C.ParamValue
ORDER BY CityCount DESC
,S.StateName
ASKER
Well technically what I wanted was :
City Count (How many times each city is in the table)
City Names
StateNames
I don't think I can get across what I want to do. Also, it's too early in the morning to remember but there was a reason why i decided this probably wasnt possible. I ended up not doing the join and just did a distinct city count
City Count (How many times each city is in the table)
City Names
StateNames
I don't think I can get across what I want to do. Also, it's too early in the morning to remember but there was a reason why i decided this probably wasnt possible. I ended up not doing the join and just did a distinct city count
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you have one table that holds values for the StateID and....there should be a table that holds City values as well???
If so you could Join that table into the query to retreive your cities as well.
SELECT COUNT(A.ParamValue) AS StateCount, C.CityName, S.StateName
FROM tblAppLogger A
JOIN tblStates S
ON A.ParamValue = S.StateID
AND A.ParamName = 'StateID'
JOIN tblCities C
ON S.StateID = C.StateID
GROUP BY A.ParamValue, S.StateName, C.CityName
ORDER BY StateCount DESC, S.StateName, C.CityName
If you have no way to relate the State and City tables...which would seem like a very odd architecture...you could Join to the tblCities table where the GuestCookieUUID and DateTimeSubmitted are the same.
Does this fall in line with what you are after??