Asked  7 Months ago    Answers:  5   Viewed   35 times

I have 2 tables as below

Notes Table

??????????????????????????????
? nid      ?    forDepts     ?
??????????????????????????????
? 1        ? 1,2,4           ?
? 2        ? 4,5             ?
??????????????????????????????

Positions Table

??????????????????????????????
? id       ?    name         ?
??????????????????????????????
? 1        ? Executive       ?
? 2        ? Corp Admin      ?
? 3        ? Sales           ?
? 4        ? Art             ?
? 5        ? Marketing       ?
??????????????????????????????

I am looking to query my Notes table and associate the 'forDepts' column with values from the Positions table.

The output should be:

    ?????????????????????????????????????????
    ? 1        ? Executive, Corp Admin, Art ?
    ? 2        ? Art, Marketing             ?
    ?????????????????????????????????????????

I know that the database should be normalized but I cannot change the database structure for this project.

This is going to be used to export a excel file with the code below.

<?PHP

    $dbh1 = mysql_connect($hostname, $username, $password); 
    mysql_select_db('exAdmin', $dbh1);


    function cleanData(&$str)
  {
    $str = preg_replace("/t/", "\t", $str);
    $str = preg_replace("/r?n/", "\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

    $filename = "eXteres_summary_" . date('m/d/y') . ".xls";

    header("Content-Disposition: attachment; filename="$filename"");
    header("Content-Type: application/vnd.ms-excel");
    //header("Content-Type: text/plain");

    $flag = false;


    $result = mysql_query(
    "SELECT p.name, c.company, n.nid, n.createdOn, CONCAT_WS(' ',c2.fname,c2.lname), n.description 
     FROM notes n 
     LEFT JOIN Positions p ON p.id = n.forDepts
     LEFT JOIN companies c ON c.userid = n.clientId
     LEFT JOIN companies c2 ON c2.userid = n.createdBy"
     , $dbh1);



    while(false !== ($row = mysql_fetch_assoc($result))) {
        if(!$flag) {
            $colnames = array(
                'Created For' => "Created For",
                'Company' => "Company",
                'Case ID' => "Case ID",
                'Created On' => "Created On",
                'Created By' => "Created By",
                'Description' => "Description"
            );
            // display field/column names as first row
            echo implode("t", array_keys($colnames)) . "rn";
            $flag = true;
    }


        $row['createdOn'] = date('m-d-Y | g:i a', strtotime($row['createdOn']));

    array_walk($row, 'cleanData');
    echo implode("t", array_values($row)) . "rn";
  }
  exit;

  ?>

This code outputs only the first value of 'forDepts'

Exa: Executive (instead of Executive, Corp Admin, Art)

Can this be accomplished by CONCAT or FIND_IN_SET?

Please help! Thanks in advance!

 Answers

55
SELECT  a.nid,
        GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
        INNER JOIN Positions b
            ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP   BY a.nid
  • SQLFiddle Demo
Wednesday, March 31, 2021
 
avon_verma
answered 7 Months ago
38

This is a sign of bad DB design you should first look into Database Normalization and if you can change the structure then first normalize it by using a junction table ,As of now you can use FIND_IN_SET() to find your corresponding record id in your comma separated ids column

SELECT * FROM table 
WHERE  FIND_IN_SET('id1',column) > 0
AND FIND_IN_SET('id2',column) > 0
AND FIND_IN_SET('id3',column) > 0

Change the operator as per your wish i have shown example with AND operator,note you need to use FIND_IN_SET as many times as the no of ids you have in your array that you need to compare with your column

Saturday, May 29, 2021
 
freeMagee
answered 5 Months ago
87

I think that you are looking for this:

select distinct *
from (SELECT date,
             if(group_concat(distinct cust_price), from_product_id, null)as from_product_id,
             if(group_concat(distinct comp_price), to_product_id, null)  as to_product_id,
             group_concat(distinct cust_price)                           as cust_price,
             group_concat(distinct comp_price)                           as comp_price
      FROM (select cust_hist.date,matches.from_product_id,
                   matches.to_product_id,cust_hist.price cust_price,
                   comp_hist.price                       comp_price
            from tmp_match matches
                   inner join tmp_price_history cust_hist on matches.from_product_id = cust_hist.product_id
                   inner join tmp_price_history comp_hist on matches.to_product_id = comp_hist.product_id
            WHERE comp_hist.date = cust_hist.date
            union
            select comp_hist.date,matches.from_product_id,
                   matches.to_product_id,null as cust_price,
                   comp_hist.price               comp_price
            from tmp_price_history comp_hist
                   join tmp_match matches
                     on matches.to_product_id = comp_hist.product_id # and matches.from_product_id is null

            union
            select cust_hist.date,matches.from_product_id,
                   matches.to_product_id,
                   cust_hist.price cust_price,
                   null            comp_price
            from tmp_price_history cust_hist
                   join tmp_match matches
                     on matches.from_product_id = cust_hist.product_id # and matches.to_product_id is null

            order by DATE, from_product_id, to_product_id, cust_price, comp_price) as u
      group by date,from_product_id,to_product_id) g

Your idea about sql snippet was great!

Sunday, August 22, 2021
 
cletus
answered 2 Months ago
48

Use FIND_IN_SET() function:

Try this:

SELECT A.allocationId, 
       B.className, 
       GROUP_CONCAT(C.subjectShortName) AS subjectName
FROM subjectAllocation A
INNER JOIN class_Master B ON A.classId = B.classId 
INNER JOIN subject_Master C ON FIND_IN_SET(C.subjectId, A.subjectId) 
GROUP BY A.allocationId;
Friday, August 27, 2021
 
amustill
answered 2 Months ago
33

You need the group by for the not aggregated columns

SELECT tab_sector.sector_id,tab_sector.sector_title,tab_sector.sector_desc
    ,tab_sector.sector_image,group_concat(tab_sector_subdivisions.subdiv_id )
LEFT JOIN tab_sector_subdivisions 
        ON tab_sector_subdivisions.sector_id = tab_sector.sector_id 
              AND tab_sector.active = 'Y'
GROUP BY  tab_sector.sector_id,tab_sector.sector_title,
     tab_sector.sector_desc, tab_sector.sector_image
Wednesday, September 1, 2021
 
Viralk
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :