MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Peter J. Schoenster Date:March 26 2000 7:15pm
Subject:Re: Tricky SQL query dealing with multiple selections
View as plain text  
On 26 Mar 00, at 0:28, Andy Leung wrote:

> As you can see, each item_id has one model_no but can have
> multiple locations.

> So basically I want to "group by" item_id and then "collapse"
> each item_id's multiple locations into a single list of
> locations.
> 
> Does anyone know how to do it in SQL? Or is it impossible to do
> it in SQL and I'd need to do it in (in my case) PHP? How?

I always hate to do in programming what can be done in SQL ... 
but given that I know my programming language much better than 
SQL I usually go there ... ... but I don't know how to solve 
that in SQL. Here is how I would do it in Perl (assuming 
model_no does not change ... otherwise put it in an array as 
well).  You will run into memory issues ... I am really not 
happy with this solution but it's just a quick I idea that might 
lead you to a better solution. In essence you are creating 
unique buckets whose names are the item_id. You are then dumping 
in the locations into the proper bucked.  In addition I am 
cheaply (in my time) creating another set of buckets which can 
only hold one item at a time (the model_no).  Then I go through 
these buckets and dump their contents as you see below.

use DBI;
use strict;

my $data_source = qq|dbi:mysql:database=domains;host=localhost|;
my $dbh = DBI->connect($data_source, 'root',) || 
die("$DBI::errstr");
$dbh->{RaiseError} = 1;	

my %location = ();
my %model_no =();

my $sql = qq|select item_id,model_no,location from iml where 
model_no = "AA"|;
my $sth = $dbh->prepare(qq{ $sql });
$sth->execute;
my ($item_id,$model_no,$location) = ('','','');
$sth->bind_columns(undef,\($item_id,$model_no,$location));
while($sth->fetch) {
	print qq|$item_id,$model_no,$location\n|;
	push(@{$location{$item_id}},$location);
	$model_no{$item_id} = $model_no;
}
$dbh->disconnect();
my $k = '';
my $g = '';
foreach $k(keys %location) {
	print qq|$k\t $model_no{$k}\t@{$location{$k}}\n|;
}

Peter

---------------------------
"Reality is that which, when you stop believing in it, doesn't go
away".
                -- Philip K. Dick
Thread
Tricky SQL query dealing with multiple selectionsAndy Leung26 Mar
  • Re: Tricky SQL query dealing with multiple selectionssinisa26 Mar
  • Re: Tricky SQL query dealing with multiple selectionsPeter J. Schoenster26 Mar