Solved

Select two records based upon value of both

Posted on 2004-10-01
8
214 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:KoldFuzun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 

Expert Comment

by:EnniK
ID: 12202875
So if I am reading you correctly...
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??
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 12202893
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
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 12202900
just to clarify:

ParamName = URL or Form param name
ParamValue = URL or Form Param value

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Expert Comment

by:EnniK
ID: 12203069
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
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 12204852
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
0
 
LVL 4

Expert Comment

by:strangelynormal1
ID: 12223085
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
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 12227162
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
0
 
LVL 4

Accepted Solution

by:
strangelynormal1 earned 500 total points
ID: 12228569
To get the city name, you'll have to have the table with the city names on it.  I will assume there is a table called tblCities.  

CREATE TABLE tblCities(
CityID     INT
,CityName  VARCHAR(50))

Also, it appears that you don't want to specify a particular city or state...then it can still be done with:

SELECT      CityCount      = COUNT(*)
      ,C.CityName
      ,S.StateName
FROM      tblAppLogger      ALC
      ,tblCities            C
      ,tblAppLogger      ALS
      ,tblStates            S
WHERE      ALC.ParamValue            = C.CityID
AND      ALS.ParamValue            = S.StateID
AND      ALC.CookieUUID            = ALS.CookieUUID
AND      ALC.DateTimeSubmitted      = ALS.DateTimeSubmitted
GROUP BY C.CityName
      ,S.StateName
ORDER BY COUNT(*)      DESC
      ,S.StateName
      ,C.CityName
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question