bruckerrlb Posted October 19, 2009 Share Posted October 19, 2009 I seem to be having a problem joining tables for a report I'm working on and wanted to see if I could get some feedback. I'm trying to join tables that aren't directly relational, I have the following tables: users tickets customfields customfieldvalues The only way I can establish a relationship between users and customfields and their values is through the following method users.userid = tickets.userid tickets.ticketid = customfieldvalues.typeid customfieldvalues.customfieldid = customfields.customfieldid I've been reading about joins but can't seem to figure out which one would be best, can anyone give some feedback on the best way to join these tables? Quote Link to comment Share on other sites More sharing options...
fenway Posted October 19, 2009 Share Posted October 19, 2009 That depends which fields you need, and from which tables -- you might need a 4 table join. Quote Link to comment Share on other sites More sharing options...
Mchl Posted October 19, 2009 Share Posted October 19, 2009 The best way depends on what result you want to achieve. Show us example of data in these tables, and a result you want to get. Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted October 19, 2009 Author Share Posted October 19, 2009 Thanks for the fast response, first I'll show the tables Users userid usergroupid fullname phone userpassword userpasswordtxt dateline lastvisit lastactivity enabled loginapi_moduleid loginapi_userid languageid timezoneoffset enabledst useremailcount allowemail slaplanid slaexpiry ismanager Tickets ticketid ticketmaskid departmentid ticketstatusid priorityid emailqueueid userid staffid ownerstaffid assignstatus fullname email lastreplier replyto subject dateline lastactivity laststaffreplytime slaplanid ticketslaplanid duetime totalreplies ipaddress flagtype hasnotes hasattachments isemailed edited editedbystaffid editeddateline creator charset transferencoding timeworked dateicon lastpostid firstpostid tgroupid messageid escalationruleid hasdraft hasbilling isphonecall isescalated phoneno autoclosetimeline islabeled lastuserreplytime escalatedtime followupcount Custom Fields customfieldid customfieldgroupid title fieldtype fieldname defaultvalue isrequired usereditable staffeditable regexpvalidate displayorder description Custom Field Values customfieldvalueid customfieldid typeid fieldvalue isserialized I"m trying to output all users where customfields.customfieldid the value for 51 is not null, and then I ultimately want to spit out the following: username --> users.fullname usergroup --> users.usergroupid Phase --> customfieldvalues.customfieldid = 50 Number --> customfieldvalues.customfieldid = 51 Report # --> customfieldvalues.customfieldid = 52 Address --> customfieldvalues.customfieldid = 22 city --> customfieldvalues.customfieldid = 5 state --> customfieldvalues.customfieldid =6 zip --> customfieldvalues.customfieldid = 30 phone --> customfieldvalues.customfieldid = 7 status --> customfieldvalues.customfieldid = 34 That's what my report is going to consist of, but I'm having difficulties writing this sql statement. Quote Link to comment Share on other sites More sharing options...
bruckerrlb Posted October 19, 2009 Author Share Posted October 19, 2009 After doing some more research, found out that the tickets table isn't necessary, because users.userid = customfieldvalues.typeid. So now I"m trying to do a join specifically from the users table to customfieldvalues and customfields and I have something along these lines $query_customfield = mysql_query("SELECT `swusers` LEFT JOIN `swcustomfieldvalues` as cfvalues ON swusers.userid = cfvalues.typeid") or die(mysql_error()); This is my query as it stands now, but it's not shooting out anything. What I'm trying to do is figure out a way I can test to see if a user has a customfieldvalue.customfieldid that is equal to 51, then I want to be able to bring back users.fullname, as well as fieldname.customfields and fieldvalue.customfieldvalues for that user (users.fullname) Any recommendations? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.