nam797 Posted January 29, 2009 Share Posted January 29, 2009 Hello All, I am having trouble with a mysql query. I have 5 tables jobsmain: Fields - jobid,custid,empid,siteaddress,sitecity,sitestate empmain: fields - employeeid,empname jobswo: Fields - WOID,JobID,Installdate,TradeID,WOTypeID,CrewID,WOStatusID custmain: fields - custid,custfirst,custlast here is the query: SELECT DISTINCT jobsmain.JobID, jobsmain.SiteAddress, jobsmain.SiteCity, jobsmain.SiteState, jobsmain.SiteZip, jobswo.WOID FROM jobsmain INNER JOIN jobswo ON jobsmain.JobID = Jobswo.JobID INNER JOIN empmain ON jobsmain.Salesperson = empmain.EmployeeID INNER JOIN custmain ON jobsmain.CustomerID = custmain.CustomerID i have the query working but not the way i would like it too. here is my example of my results. JOBID Address City State ZIP WOID 2168 121 Willow Dr. SW St. Michael MN 55376 1 2168 121 Willow Dr. SW St. Michael MN 55376 2 what i am trying to do is get it to show just the 1 jobid and then when they click show/hide they will see the work orders below the job. example JOBID Address City State ZIP 2168 121 Willow Dr. SW St. Michael MN 55376 [link]SHow/Hide[/link] WOID InStalldate TradeID WOTypeID CrewID WOStatusID 1 1/29/2009 3 2 36 5 2 2/5/2009 2 3 45 3 maybe i am making this harder then it should be. Its got me stumped. I hope there is enough information here for someone to help me. Thanks nam797 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 29, 2009 Share Posted January 29, 2009 Forget about the output for a minute... get back the two rows first. Quote Link to comment Share on other sites More sharing options...
nam797 Posted January 29, 2009 Author Share Posted January 29, 2009 What do you mean? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 2, 2009 Share Posted February 2, 2009 There are fields in your example output that don't show up from your query. First hint -- drop DISTINCT. Quote Link to comment Share on other sites More sharing options...
nam797 Posted February 2, 2009 Author Share Posted February 2, 2009 Thanks for the reply's. here is my query SELECT jobsmain.JobID, jobsmain.Supervisor, custmain.CustFirst, custmain.CustLast, jobsmain.SiteAddress, jobsmain.SiteCity, jobsmain.SiteState, jobsmain.SiteZip, empmain.EmpName FROM jobsmain LEFT JOIN jobswo ON jobsmain.JobID = Jobswo.JobID LEFT JOIN empmain ON jobsmain.Salesperson = empmain.EmployeeID LEFT JOIN custmain ON jobsmain.CustomerID = custmain.CustomerID WHERE jobswo.WOStatusID = '1' AND jobsmain.ProjectStatus = '4' Here is my results: 10872 8 Alex & Jackie Hansen 8733 Troy Marquette Dr. Monticello MN 55362 Jared Braun 11666 93 Reyes & Ermalinda Aguilar 1811 Grand Park Dr Missouri City TX 77489 NULL 11985 93 Alberto Romero 17006 Chapel Parkway Houston TX 77059 NULL 11985 93 Alberto Romero 17006 Chapel Parkway Houston TX 77059 NULL 11924 45 Mark Ourada 1110 Innsbrook ln Buffalo MN 55313 Roger Tiernan 11643 93 John Olivarez 15806 Boridge Cr Houston TX 77053 Brian Johnsted 11643 93 John Olivarez 15806 Boridge Cr Houston TX 77053 Brian Johnsted 11592 93 Steve Strother 5047 E. Ridge Creek Dr Houston TX 77053 NULL I'm trying to get it so i don't get duplicates. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 6, 2009 Share Posted February 6, 2009 Where are these duplicates coming from? are there many to many relationships 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.