vet911 Posted March 29, 2012 Share Posted March 29, 2012 I would like to combind these 2 querys but have no clue how to do this. I'm hoping someone will help. Both work fine but the first one has more listings because the units are not grouped together. The second doesn't join the tables together. What would be the right way to make this work? $query = "SELECT lakestmill.id_company, units_1.id_unit, lakestmill.firstname, lakestmill.lastname, lakestmill.company, lakestmill.address, lakestmill.phone, lakestmill.email, lakestmill.city, lakestmill.state, lakestmill.zip, units_1.unit FROM lakestmill LEFT JOIN units_1 ON lakestmill.company = units_1.company ORDER BY company"; $result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); and this one: $query = "SELECT *, GROUP_CONCAT(unit SEPARATOR ', ') as value_list FROM units_1 GROUP BY company ASC"; $result = mysql_query($query); Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted March 29, 2012 Share Posted March 29, 2012 Not sure what's going on with your data, or exactly what you want to get from your query, however: GROUP BY company ASC Is this supposed to be ORDER BY? have you tried adding GROUP BY company to your 1st query? Quote Link to comment Share on other sites More sharing options...
vet911 Posted March 29, 2012 Author Share Posted March 29, 2012 I tried to post images of the results of each query but it doesn't allow me to do so. What happens is the first query (which joins 2 tables) shows all the units in a column along with corresponding company value. The second query (only uses 1 table) shows all units in 1 row with the corresponding company. I want to use the joined tables to produce the infomation shown in the second listing below. I hope this helps. 1st ******************************** Collins Precision Machine Mike Collins 3-7 CSJL John Lawrence 1-6 D.L.Raymond Dennis Raymond B-2 Dependable Lock Service William Diebert 1-2 Diagnostic Medical Systems Ron DeMayo 1-7 Duane Erickson Duane Erickson 1-13 H6 Systems, Inc. Bill Hunter 3-6 Joseph Fariz Joseph Fariz 4-2 Joseph Kaleshain Joseph Kaleshain B-9 Joseph Kaleshain Joseph Kaleshain B-11 Joseph Kaleshain Joseph Kaleshain B-14 Joseph Kaleshain Joseph Kaleshain B-16 2nd ***************************** Collins Precision Machine 3-7 CSJL 1-6 D.L.Raymond B-2 Dependable Lock Service 1-2 Diagnostic Medical Systems 1-7 Duane Erickson 1-13 H6 Systems, Inc. 3-6 Joseph Fariz 4-2 Joseph Kaleshain B-9, B-11, B-14, B-16 Joyce C & H Inc. B-5, B-4 Joyce Cooling & Heating 1-3, 1-9, 1-11 KW Management 1-5 Lighthouse Technical Sales, LLC 3-8 Mark Prolman 1-1 Quality Press Inc 4-5, 4-7 Reo Machine B-1 Robert Scott 4-6 Sempco 2-1, 2-3, 2-2, 2-5, 2-4, 2-6, 2-8 ************************************* Quote Link to comment Share on other sites More sharing options...
fenway Posted March 31, 2012 Share Posted March 31, 2012 Define "combine". Quote Link to comment Share on other sites More sharing options...
vet911 Posted April 1, 2012 Author Share Posted April 1, 2012 I have 2 tables currently joined which shows the partial 1st listing above, I want the results from the joined tables to include all the units for each owner to show under the listing of one owner and not multiple listings of the owner and their units. The second query shows the owners with their units. I guess that is what I mean by combine the querys, putting the GROUP_CONCAT(unit) in the query that has the joined tables. I just can figure it out with the joined tables. Quote Link to comment Share on other sites More sharing options...
vet911 Posted April 2, 2012 Author Share Posted April 2, 2012 I have changed the query to add the GROUP_CONCAT as shown below. It works except for one particular company which it doesn't put the units in the field. Not sure what is going on: Any help would be greatly appreciated on that problem. I don't get any error messages. $query = "SELECT lakestmill.id_company, units_1.id_unit, lakestmill.firstname, lakestmill.lastname, lakestmill.company, lakestmill.address, lakestmill.phone, lakestmill.email, lakestmill.city, lakestmill.state, lakestmill.zip, lakestmill.website, lakestmill.webname, units_1.unit, GROUP_CONCAT(units_1.unit ORDER BY units_1.unit) as value_list FROM lakestmill LEFT JOIN units_1 ON lakestmill.company = units_1.company GROUP BY company"; $result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 3, 2012 Share Posted April 3, 2012 without having the first clue what company, how we ment to help? Quote Link to comment Share on other sites More sharing options...
vet911 Posted April 3, 2012 Author Share Posted April 3, 2012 Ok originally I showed this code that produces the following:(shown below). This table name is units_1 which has multiple listings of companies and their units(individual units of a condo association). If there is more than one listed under the company name grouped to show( 4-1, 4-2, 4-3 etc). When I use left join and the group_concat (units_1.unit) as value list it produces all the correct listings except for the Quality Press Inc listing which is left blank. The code at the bottom is the code for the left join tables. I hope this helps. *********************** Collins Precision Machine 3-7 CSJL 1-6 D.L.Raymond B-2 Dependable Lock Service 1-2 Diagnostic Medical Systems 1-7 Duane Erickson 1-13 H6 Systems, Inc. 3-6 Joseph Fariz 4-2 Joseph Kaleshain B-9, B-11, B-14, B-16 Joyce C & H Inc. B-5, B-4 Joyce Cooling & Heating 1-3, 1-9, 1-11 KW Management 1-5 Lighthouse Technical Sales, LLC 3-8 Mark Prolman 1-1 Quality Press Inc 4-5, 4-7 Reo Machine B-1 Robert Scott 4-6 Sempco 2-1, 2-3, 2-2, 2-5, 2-4, 2-6, 2-8 ************************************ $query = "SELECT *, GROUP_CONCAT(unit SEPARATOR ', ') as value_list FROM units_1 GROUP BY company ASC"; $result = mysql_query($query); ************************************** $query = "SELECT lakestmill.id_company, units_1.id_unit, lakestmill.firstname, lakestmill.lastname, lakestmill.company, lakestmill.address, lakestmill.phone, lakestmill.email, lakestmill.city, lakestmill.state, lakestmill.zip, lakestmill.website, lakestmill.webname, units_1.unit, GROUP_CONCAT(units_1.unit ORDER BY units_1.unit) as value_list FROM lakestmill LEFT JOIN units_1 ON lakestmill.company = units_1.company GROUP BY company"; $result = mysql_query($query) or die("There was a problem with the SQL query: " . mysql_error()); Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 3, 2012 Share Posted April 3, 2012 "ON lakestmill.company = units_1.company" This looks like you are joining on column that holds the company name as a string value. If this is the case is the spelling, white space and case exactly the same on both tables? Quote Link to comment Share on other sites More sharing options...
vet911 Posted April 3, 2012 Author Share Posted April 3, 2012 I can't check it out now, I'm at my work. I'll look tonite , would I be better using lakestmill.id_company, units_1.id_unit ?Thanks for the reply, I appreciate it! Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 3, 2012 Share Posted April 3, 2012 if those fields are directly relational integer fields then yeah you would. Quote Link to comment Share on other sites More sharing options...
vet911 Posted May 8, 2012 Author Share Posted May 8, 2012 Changed per last suggestion, works fine now, sorry for the delay in notification. 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.