Complex MySQL query advice needed

Posted on 2011-02-11
Last Modified: 2013-12-13
I have a non-normalized table that I need to extract data from. I am using PHP and MySQL and am adept at displaying the records once I submit the query

A simplified example is this:
size_sort      wt_ft             bill_lgth         analysis_desc
9/32"             0.2113          12'                 silver
9/32"             0.2113          12'                 gold
11/32"           0.3156          12'                 silver
11/32"           0.3156          20'                 gold
11/32"           0.3156          12'                 copper

(Desired results)
Size          Weight          silver      gold        copper
9/32"         0.2113          12'          12'          -
11/32"       0.3156          12'          20'         12'

(my results now)
Size          Weight       silver     gold     copper
9/32"         0.2113       12'         -           -
9/32"         0.2113        -           12'        -
11/32"       0.3156       12'         -           -
11/32"       0.3156        -           20'        -
11/32"       0.3156        -           -           12'

So basically I need a query that will select distinct wt_ft and add size_sort along with the distinct analysis_desc's as result fields populated with the bill_length.
This is one of 100s of "sub-tables" I need to extract from this one main table. I thought of creating views "on the fly" using php and am trying to create a query that gives me the results (see code) but I get an error (#1242 - Subquery returns more than 1 row). Is there a better way to acheive the results?

Unfortunately there is no relationship between grade_sort and analysis_desc
) AS wt_ft, size_sort, (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "gold"
) AS `gold` , (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "silver"
) AS `silver` , (

SELECT bill_lgth
FROM ecoin_stock
WHERE analysis_desc = "copper"
) AS `copper` 

FROM mf_stock
grade_sort = "34120"
OR (
grade_sort = "34090"
ORDER BY wt_ft

Open in new window

Question by:jabberwok_e
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3

Expert Comment

ID: 34875380
the code below does the job in PHP.  It can be further generalized so that it can do the 100 other queries you have to do.  Haven't thought of an SQL way of doing it yet.

replace *** with your host user and pass

$database_link1 = mysql_connect($HOST, $USER, $PASS) OR die ("$filename: Connecting problem: ".mysql_errno()." .". mysql_error() .". tis all.");
mysql_select_db("test", $database_link1);

// wrapper function for queries so that query format and error checking happen in one place
function transaction($query="", $database_link) {
      global $filename;
      if (strlen(trim($query)) == 0) {
            return false;
      $result = mysql_query($query, $database_link) OR die ("$filename: Query problem: ". mysql_error() ." [$query].");
      return $result;

function showRow($size, $wt, $temp_array) {
      print "<TR><TD>$size</TD>\n<TD>$wt</TD>\n";
      for ($i=0; $i< count($temp_array); $i++) {
            print "<TD>".$temp_array[$i] . "</TD>\n";
      print "</TR>\n\n";

// create place for unique analysis_desc  (instead of hardcoding 'gold' 'silver' 'copper' we could discover this for ourselves)
$analysis_desc = array("silver" => 0,"gold" => 1, "copper" => 2);//var_dump($analysis_desc);
// alternatively you could write the code below to discover the unique values
/*$analysis_desc = array();
$query = "SELECT DISTINCT analysis_desc FROM ecoin_stock";
$statement = transaction($query, $database_link1);
$column = 0;
while ($value = mysql_fetch_row($statement)) {
      $analysis_desc{$value[0]} = $column++;      

// table header
print "<TABLE border=1><TR><TD>size_sort</TD><TD>wt_ft</TD>";
foreach ($analysis_desc as $key=>$value) {
      echo "<TD>".$key ."</TD>";
print "</TR>\n";

// now we have the extra columns needed for each row of our table of distinct information
// ie:   9/32"            0.2113            [gold            silver            copper]
$temp_array = array();
$query = "SELECT * FROM ecoin_stock ORDER BY size_sort, wt_ft";
$statement = transaction($query, $database_link1);
$prev_size = $prev_wt_ft = "";
while ($value = mysql_fetch_assoc($statement)) {
      if (!(strcmp($prev_size,$value{'size_sort'})==0 && strcmp($prev_wt_ft, $value{'wt_ft'})==0) && strlen($prev_size) != 0) { // not same record  then print and reset temp_array
            showRow($prev_size, $prev_wt_ft, $temp_array);
      $temp_array[$analysis_desc{$value{'analysis_desc'}}] = $value{'bill_lgth'};
      $prev_size = $value{'size_sort'};      $prev_wt_ft = $value{'wt_ft'};
showRow($prev_size, $prev_wt_ft, $temp_array);

print "</TABLE>";

Accepted Solution

ropenner earned 500 total points
ID: 34876336
this does it.  It isn't pretty and it has only been tested on your sample.

select DISTINCT final.size_sort, final.wt_ft, group_concat(, group_concat(final.silver), group_concat(final.copper) from (
select t1.size_sort, t1.wt_ft, t2.bill_lgth as gold, t3.bill_lgth as silver, t4.bill_lgth as copper FROM ecoin_stock  t1
LEFT JOIN ecoin_stock t2 ON t1.size_sort=t2.size_sort AND t1.wt_ft=t2.wt_ft AND t1.analysis_desc=t2.analysis_desc and t1.analysis_desc="gold"
LEFT JOIN ecoin_stock t3 ON t1.size_sort=t3.size_sort AND t1.wt_ft=t3.wt_ft AND t1.analysis_desc=t3.analysis_desc and t1.analysis_desc="silver"
LEFT JOIN ecoin_stock t4 ON t1.size_sort=t4.size_sort AND t1.wt_ft=t4.wt_ft AND t1.analysis_desc=t4.analysis_desc and t1.analysis_desc="copper") as final
group by final.size_sort, final.wt_ft

Author Comment

ID: 34898387
Thank you for the information. This query locks up the server, but is giving me some ideas on how I might be able to get it done by using views. I will post the results of my attempts.
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


Assisted Solution

ropenner earned 500 total points
ID: 34899951
if you do it in stages with views, I was going to add that the inner query gives the same result you have and the outer one shrinks it to one line per.

outer ==

select DISTINCT final.size_sort, final.wt_ft, group_concat(, group_concat(final.silver), group_concat(final.copper) from (

) as final
group by final.size_sort, final.wt_ft

So if you already have the view you call "my results now" and put those in a table... then the insides of this query would be "SELECT * FROM my_view"

Author Comment

ID: 34942702
I've been tied up with other projects, but I think you helped put me on the right track.

I'm going to close this.

Author Closing Comment

ID: 34942723
The proposed solution pointed me in the right direction of using views or temporary tables to joint the data with itself.


Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question