Stuck on a beast of a query

[roopurt18]

[quote]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, [color=red]-- PROBLEM IS RIGHT HERE[/color]
  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
[/quote]

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 [i]onsite[/i], [i]offsite[/i], or [i]both[/i].  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!

[shoz]

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.

[code]
SUM(IF(t.onsite = 1, 1, 0)) AS OnSiteCount
[/code]

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

[roopurt18]

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.

[shoz]

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.

[roopurt18]

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

[b]wv_user[/b]
There are other columns in this table, but these are the relevant ones.  Each row is unique.
[img]http://www.lap-wow.com/junk/wv_user.JPG[/img]

[b]wssubc[/b]
There are other columns in this table, but each row is unique.
[img]http://www.lap-wow.com/junk/wssubc.JPG[/img]

[b]user_activity[/b]
None of the rows are necessarily unique.  There are additional columns.
[img]http://www.lap-wow.com/junk/user_activity.jpg[/img]

[b]SubProjectAccess[/b]
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.
[img]http://www.lap-wow.com/junk/SubProjectAccess.JPG[/img]

[b]wstrades[/b]
Each row is unique and the tcodes are also unique.
[img]http://www.lap-wow.com/junk/wstrades.JPG[/img]


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.

[shoz]

[quote author=roopurt18 link=topic=112706.msg457632#msg457632 date=1161809286]
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.
[/quote]

If the only requirement is to determine if the user has all offsite/onsite or a combination then you can try the following
[code]
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
[/code]

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.

[code]
if anyonsite && anyoffsite
{
  combo
}
elseif (!anyonsite)
{
  alloffsite
}
else
{
  allonsite
}
[/code]

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.

[roopurt18]

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!

[shoz]

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

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

The MAX() is/was missing.