Jump to content


Photo

Stuck on a beast of a query


  • Please log in to reply
7 replies to this topic

#1 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 25 October 2006 - 07:17 PM

SELECT
  u.user_wvid AS UserID,
  LOWER( u.login ) AS Login,
  LOWER( u.password ) AS Password,
  u.subname AS SubName,
  s.full_name AS FullName,
  COUNT( DISTINCT pa.Trade ) AS TradeCount
  COUNT( DISTINCT t.onsite <>0 ) AS OnSiteCount, -- PROBLEM IS RIGHT HERE
  MAX( a.timestamp ) AS LastActiveStamp,
  IFNULL( DATE_FORMAT( a.timestamp, '%a. %b. %D, %Y' ) , '-' ) AS LastActiveDisp
FROM wv_user u
CROSS JOIN wssubc s ON u.subname = s.subname
CROSS JOIN SubProjectAccess pa ON u.subname = pa.SubName
CROSS JOIN wstrades t ON pa.Trade = t.tcode
LEFT JOIN user_activity a ON u.user_wvid = a.user_wvid
WHERE u.role_wvid =2
GROUP BY UserID


Basically I'm selecting a user summary from five tables.  The problem lies in I'm trying to figure out if each user is considered onsite, offsite, or both.  The tables to determine this are the SubProjectAccess and wstrades, which are linked on a trade code.  wstrades has an onsite column with a 1 if the trade is onsite and 0 if it is offsite.  SubProjectAccess lists which trades each user is linked to.

I have already figured out how to determine the number of trades each user has access to.  What I'd like to add is a count of how many of those trades are onsite, which would enable me to determine which category the user falls into.

I'm hoping someone with more MySQL knowledge is out there!
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 07:27 PM

I haven't tried to understand any other part of your query so you'll have to ensure that you're getting the results you're looking for.

SUM(IF(t.onsite = 1, 1, 0)) AS OnSiteCount

Btw, make sure you're using DISTINCT for the right reasons.

#3 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 25 October 2006 - 07:34 PM

Appreciate the response, but the results are incorrect.  I had previously tried variations of SUM and always end up with results like:

User has 5 trades, 480 of them are onsite, which is what I got again with what you posted.

I think some form of COUNT with DISTINCT on a few fields may give me what I'm looking for.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 08:11 PM

I'd have to know more about your tables to give an accurate response. When you JOIN on tables with multiple 1 to many or many to many relationships and then try to use aggregate functions you'll get undesired result unless the query is written with the relationships in mind.

Post an example of or describe in greater detail the actual data in the tables. Is pa.Trade unique for each row in the table? Which is why I assume COUNT(DISTINCT....)  gives an accurate result.

EDIT: Removed a query that will fail.


#5 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 25 October 2006 - 08:48 PM

I've inserted jpgs to help show the data in the tables.

wv_user
There are other columns in this table, but these are the relevant ones.  Each row is unique.
Posted Image

wssubc
There are other columns in this table, but each row is unique.
Posted Image

user_activity
None of the rows are necessarily unique.  There are additional columns.
Posted Image

SubProjectAccess
Basically this table is used to map which users have access to which projects and under which trades.  So every row is unique, but none of the columns are.
Posted Image

wstrades
Each row is unique and the tcodes are also unique.
Posted Image


So really what I'm trying to accomplish is pull some basic data from wv_user and wssubc.  Then I want to join on user_activity to determine the user's last login.  I would also like to join with the other two tables to determine if the user has all onsite, all offsite, or a combination of trade types.

Thanks again.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 25 October 2006 - 09:51 PM

I would also like to join with the other two tables to determine if the user has all onsite, all offsite, or a combination of trade types.


If the only requirement is to determine if the user has all offsite/onsite or a combination then you can try the following
SELECT
  u.user_wvid AS UserID,
  LOWER( u.login ) AS Login,
  LOWER( u.password ) AS Password,
  u.subname AS SubName,
  s.full_name AS FullName,
  SUM(IF(t.onsite = 1, 1, 0)) > 0 AS anyonsite,
  SUM(IF(t.onsite = 0, 1, 0)) > 0 AS anyoffsite,
  MAX( a.timestamp ) AS LastActiveStamp,
  IFNULL( DATE_FORMAT( a.timestamp, '%a. %b. %D, %Y' ) , '-' ) AS LastActiveDisp
FROM wv_user u
INNER JOIN wssubc s ON u.subname = s.subname
INNER JOIN SubProjectAccess pa ON u.subname = pa.SubName
INNER JOIN wstrades t ON pa.Trade = t.tcode
LEFT JOIN user_activity a ON u.user_wvid = a.user_wvid
WHERE u.role_wvid =2
GROUP BY UserID

Then you can test the combination that the user has. Note that because we're using an INNER JOIN no test is done to determine if the user has neither. If a user may not have an entry in the SubProjectAccess table and subsequently none in the wstrades table, then you can do a LEFT JOIN on those and add the other test.

if anyonsite && anyoffsite
{
   combo
}
elseif (!anyonsite)
{
   alloffsite
}
else
{
   allonsite
}

Btw, I tried to think of a simple way of getting the COUNTs, but although it's possible I only thought of more complicated queries to accomplish the task.

#7 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 25 October 2006 - 10:00 PM

There is no need to test if the user has neither since it's not possible in the case of this user type.  Based off your advice what I'll probably do is check if the user has onsites, check if they have offsites, and then use a CASE WHEN off those values to return the proper user category.

Thanks for your help!
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#8 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 26 October 2006 - 06:23 AM

Just thought I'd mention an error with "LastActiveDisp"

IFNULL( DATE_FORMAT(MAX(a.timestamp), '%a. %b. %D, %Y' ) , '-' ) AS LastActiveDisp

The MAX() is/was missing.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users