Search the Community
Showing results for tags 'mysql join'.
-
So I have a left join "SELECT * FROM `dailyMasterLogs` AS logs LEFT JOIN `vessels` AS vessel ON ( logs.logVesselID = vessel.vesselName) ORDER BY $field $order" This works, but I need to add in a where clause on the logVesselID. I want to select all from the dailymasterlogs where logvesselid = foo as logs, but thats not working. Any help on this?
-
Below is my query: <?php if (isset($_POST['search_all_submit'])){ $search_all = strtolower($_POST['search_all']); echo "<table id='qbook'>"; echo "<tr>"; echo "<td> CID </td>"; echo "<td> Company </td>"; echo "<td> Property </td>"; echo "<td> Contact </td>"; echo "</tr>"; $sql = " SELECT contract.*, customer.*, contact.* FROM customer INNER JOIN contract ON customer.customer_id = contract.customer_id INNER JOIN contact ON customer.customer_id = contact.customer_id WHERE customer.company_name like '%".$search_all."%' or customer.billing_address like '%".$search_all."%' or contract.prop_name like '%".$search_all."%' or contract.prop_address like '%".$search_all."%' or contact.contact_fname like '%".$search_all."%' or contact.contact_lname like '%".$search_all."%' "; $results = mysql_query($sql)or die(mysql_error()); while ($row = mysql_fetch_array($results)){ $company_name = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['company_name']))); $billing_address = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['billing_address']))); $prop_name = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['prop_name']))); $prop_address = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['prop_address']))); $fname = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['contact_fname']))); $lname = implode('<span class="high">'.$search_all.'</span>',explode($search_all,strtolower($row['contact_lname']))); $CID = $row['CID']; echo "<tr>"; echo "<td> $CID </td>"; echo "<td> $company_name <br /> $billing_address </td>"; echo "<td> $prop_name <br /> $prop_address </td>"; echo "<td> $fname $lname</td>"; echo "</tr>"; } echo "</table>"; } ?> The query above gives me results like: 10008 | jw property management | creekside crossing | mike pudelek | 4816 green bay rd | 9219 66th ave | 10008 | jw property management | creekside crossing | sam johnson | 4816 green bay rd | 9219 66th ave | 10008 | jw property management | creekside crossing | dean zierk | 4816 green bay rd | 9219 66th ave | What I would like to see is: 10008 | jw property management | creekside crossing | dean zierk | 4816 green bay rd | 9219 66th ave | mike pudelek | sam johnson I am pretty sure that I need to use some type of GROUP CONCAT and possibly a GROUP BY. However all of my attemps have failed. Any help would be appreciated.
- 2 replies
-
- concat
- mysql join
-
(and 3 more)
Tagged with:
-
Hi, I have two MySQL queries - posted below that pulls the select info I need and without delay. However, i need to - somehow - combine those two queries because I need the total count for (l.reqid) from leads table added to the results from the clicks table. I've tried all possible joing adding COUNT(l.reqid) from leads to the other sql query, however, it hogs the CPU resources and takes forever so i know I'm preparing the statement wrong. Would appreciate some insight. BTW: not every 'reqid' found in the 'clicks' table logged in the 'leads' table so that's definitely "LEFT JOIN so non-matches in leads is NULL. Ok, here are the two queries - if anyone can suggest how best to combine the two, much appreciated (duplicate columns can be eliminated as they the same in each query). # THIS QUERY PULLS THE TOTAL LEAD COUNT VIA 'l.reqid' - that's the only required column. The rest duplicates to 2nd query select count(l.reqid) as lead_totals, a.aname, c.camp_name,c.campid,c.payout from leads as l JOIN affiliates as a ON l.affid = a.affid JOIN campaign as c ON l.campid = c.campid group by a.aname,c.camp_name; --------------------------------------- # THIS QUERY PULLS TOTAL CLICK COUNT - MATCHING TO SPECIFIED AFFILIATES - WORKS JUST LIKE I NEED (I just need to combine the two so both TOTAL LEADS and TOTAL CLICKS IN SET RESULTS select count(cl.reqid), a.aname,c.camp_name from clicks as cl JOIN affiliates as a ON cl.affid_sid = a.affid JOIN campaign as c ON cl.camp_sid = c.campid group by a.aname; Again, I need to, somehow, combine those two queries where not all row match from 'leads' table, but every row from 'clicks' table counted - thus, NULL will result in 'leads' table if LEFT JOINED to 'clicks'.
-
I have a table in MYSQL as follows Id-------NAME 1-------Black 2-------Red-- 3-------Green 4-------Black 5-------Green 6-------Yellow 7-------Orang 8-------White 9-------Black 10-------violet 11-------maroA 12-------indiA 13-------Blue 14-------Yellow 15-------Orane 16-------White 17-------White 18-------White I need to get the id of each color names where the condition matches unique names for each set of five rows and if any duplicate occurs then it has to be moved to end of the result. Note: I don’t want the count (max/min) as a result for each color names output: ID: 1 8 6 NAME: Black | white | yellow | violet | orange | - Black | white | yellow | violet | orange | - Blue| maroon | white| white | white | Kindly guide if this is possible to get MYSQL query