Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ashisht4u
Starting Member
1 Post |
 Posted - 2011-08-30 : 03:30:23
|
| I have to generate one report for the users logged into our system from 4 tables:Profile_maxSession_maxSession_LiveHouseHoldEach of these table has b_id as its primary key.I have to show following fields:Customer_name : this can be retrieved from Profile_maxaccount_number: this can be retrieved from house_hold, house_hold has two columns one is customer and other is account_number. This table only contains the household accounts. there can be many customers which are in profile_max but not in house_hold.Last_login: this field can be retrieved from session_max and session_live. session_live will have customer and its live session information whereas session_max will have completed session information for each customer.While generating the report there could be many customers logged into the system so there information should come from session_live.Please review if the following query should work and please assist:SELECT s.customer, h.account_number, max(s.start_time),max(a.start_time) FROM (profiles_max p LEFT JOIN sessions_max s ON p.customer=s.customer) LEFT JOIN sessions_live a ON s.customer=a.customer LEFT JOIN house_hold h ON p.customer = h.customer WHERE p.b_id='WS2555'AND s.b_id='WS2555' AND a.b_id='WS2555' AND h.b_id='WS2555' GROUP BY s.customer, h.account_number ORDER BY s.customer |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-08-30 : 06:43:41
|
your left joins become inner joins because of your WHERE clause. to be absolutely sure you are getting what you want from this query, we need some DDL, sample data, and desired output.Or Madhi will use his crystal ball and give you the answer.  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
|
|
|
Join query