<html><head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type"></head>
<body>
<div>
<div style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif">Landon,<br><br>I wrote some SQL to create reporting views for another system. Our RT was on Oracle, but the SQL might be similar enough to use as an example. I create a flattened view of Various Custom Fields, including Dates.<br>
<br>Let me know if you're interested.<br><br>Kenn<br><br>Sent from my Windows Phone</div></div>
<div dir="ltr">
<hr>
<span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif;FONT-WEIGHT:bold">From: </span><span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif"><a href="mailto:lstewart@iweb.com">Landon Stewart</a></span><br><span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif;FONT-WEIGHT:bold">Sent: </span><span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif">3/3/2014 3:02 PM</span><br>
<span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif;FONT-WEIGHT:bold">To: </span><span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif"><a href="mailto:rt-users@lists.bestpractical.com">RT Users</a></span><br>
<span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif;FONT-WEIGHT:bold">Subject: </span><span style="FONT-SIZE:11pt;FONT-FAMILY:Calibri,sans-serif">[rt-users] MySQL Question (joins and stuff)</span><br><br></div></body></html>
<div dir="ltr">Hello,<div><br></div><div>I'd never modify the database without the API but I'd like a flattened version of the data as a snapshot every so often for statistical purposes. If I run the following MySQL query I basically get a line for every CustomField Value and it duplicates all the T.* fields while writing new data for the OCFV.* values on each line of course.</div>
<div><br></div><div><div>SELECT T.id,Q.Name,T.Subject,T.Status,T.Created,T.Resolved,CF.Name,OCFV.Content</div><div><span class="" style="white-space:pre"> </span>FROM Tickets T, ObjectCustomFieldValues OCFV,CustomFields CF,Queues Q </div>
<div><span class="" style="white-space:pre"> </span>WHERE T.id = OCFV.ObjectID AND OCFV.CustomField = CF.id AND T.Queue = Q.id </div><div><span class="" style="white-space:pre"> </span>AND Q.Name = "Incidents" </div>
<div><span class="" style="white-space:pre"> </span>AND T.Status != "abandoned"</div><div><span class="" style="white-space:pre"> </span>AND OCFV.Disabled = 0</div><div><span class="" style="white-space:pre"> </span>LIMIT 1000;<br>
</div><div><br></div><div>OUTPUT:</div><div><div>id<span class="" style="white-space:pre"> </span>Name<span class="" style="white-space:pre"> </span>Subject<span class="" style="white-space:pre"> </span>Status<span class="" style="white-space:pre"> </span>Created<span class="" style="white-space:pre"> </span>Resolved<span class="" style="white-space:pre"> </span>Name<span class="" style="white-space:pre"> </span>Content</div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>Constituency<span class="" style="white-space:pre"> </span>EDUNET</div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>IP<span class="" style="white-space:pre"> </span>10.0.0.220</div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>CCName<span class="" style="white-space:pre"> XXXX</span></div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>ClientName<span class="" style="white-space:pre"> </span>Johnny Appleseed</div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>Customer<span class="" style="white-space:pre"> 9877659</span></div>
<div>16478020<span class="" style="white-space:pre"> </span>Incidents<span class="" style="white-space:pre"> </span>open resolver - This host is most likely running an open DNS resolver.<span class="" style="white-space:pre"> </span>open<span class="" style="white-space:pre"> </span>2013-09-19 19:19:41<span class="" style="white-space:pre"> </span>2013-10-04 16:25:04<span class="" style="white-space:pre"> </span>PreferredLanguage<span class="" style="white-space:pre"> </span>English</div>
</div><div><br></div><div>What I'd like to do is have the output with the T.* columns like normal and each CF.Name as a column name with the value from OCFV.Content would be desired. I think I know this involves using the right INNER or OUTER or FULL JOIN or sub-queries or something but I'm afraid that's over my head here. I'm familiar with JOINs but not turning a table on it's side. It's either this or have a ridiculous amount of output feed into some ridiculous kludgy script to reformat it.</div>
<div><br></div><div>If anyone knows how I could flatten this data so a snapshot of each ticket (within a date range based on Tickets.Created or Tickets.Resolved) on one line with CF names as columns and CF values as values can be achieved I would really really appreciate it. Failing that if anyone knows of any tips to figure this out (like a primer on turning tables on their side) I'd appreciate any advice you can give me. :-D</div>
<div><br></div><div><br></div>-- <br><div dir="ltr"><div><span style="font-family:arial;font-size:small">Landon Stewart :: </span><a href="mailto:lstewart@iweb.com" style="font-family:arial;font-size:small" target="_blank">lstewart@iweb.com</a><br>
</div><span style="font-family:arial;font-size:small">Lead Specialist, Abuse and Security Management</span><br style="font-family:arial;font-size:small"><span style="font-family:arial;font-size:small">Spécialiste principal, gestion des abus et sécurité</span><br style="font-family:arial;font-size:small">
<span style="font-family:arial;font-size:small"><a href="http://iweb.com" target="_blank">http://iweb.com</a> :: +1 (888) 909-4932</span><br><div><span style="font-family:arial;font-size:small"><br></span></div><div><span style="font-family:arial;font-size:small"><br>
</span></div></div>
</div></div>