Diamondback wrote:
[color=blue]
> I have two tables, WIDGETS and VERSIONS. The WIDGETS table has
> descriptive information about the widgets while the VERSIONS table
> contains IDs relating to different iterations of those widgets over the
> years. The idea is that from any widget in the database you can look
> forward and backward to see different versions of the widget through
> the years.
>
> Now my query gets me somewhere in the vicinity, but not where I want to
> be.
>
> select w.widget, w.widget_id, v.new_id, v.old_id
> from widgets w, versions v
> where w.widget_id = 712
> and w.widget_id = v.new_id
> order by w.widget;
>
> ... and I get ...
>
> WIDGET WIDGET_ID NEW_ID OLD_ID
>
> Widget 1 712 712 535
> Widget 1 712 712 287
> Widget 1 712 712 1
> Widget 1 712 712 205
> Widget 1 712 712 458
>
> What I would like is a list that shows the widget names related to
> OLD_ID, rather than NEW_ID. But I'm not sure how to get there. Help
> is appreciated.[/color]
Maybe there is a plain sql solution out there, but it will definitely
not be an easy one. Maybe it's possible with a join/subquery
combination or stuff in that direction.
For code transparency and general ease of use I'ld suggest an API
approach. You could execute 2 separate queries where the first one ties
each [v]![old_id] to its corresponding [w]![name]. The second query
could then use these stored values from the first query.
I set up the following test environment:
SELECT * FROM w;
---------|--------|-----------|----
widget_id|name |description|year
---------|--------|-----------|----
40|Widget 0| |
400|Widget 1| |
500|Widget 2| |
600|Widget 3| |
700|Widget 4| |
712|Widget 5| |
---------|--------|-----------|----
SELECT * FROM v;
----|------|------
v_id|new_id|old_id
----|------|------
1|712 |400
2|712 |500
3|712 |600
4|712 |700
5|713 |50
6|713 |80
7|714 |40
----|------|------
Now the API could first perform the following query:
SELECT v.old_id, w.name
FROM v
LEFT JOIN w
ON v.old_id = w.widget_id
And then tie those results to, let's say, a hash:
$widgetname{"$var_old_id"} = $var_name;
(supposed that $var_old_id is the first field and $var_name the second
one from your query result set)
Then, doing a query like:
SELECT v.old_ID AS name, w.widget_id, v.new_id, v.old_id
FROM w, v
WHERE w.widget_id =712
AND w.widget_id = v.new_id
ORDER BY w.name
will give you:
----|---------|------|------
name|widget_id|new_id|old_id
----|---------|------|------
400 |712 |712 |400
500 |712 |712 |500
600 |712 |712 |600
700 |712 |712 |700
----|---------|------|------
Finally, before showing results to screen, replace the first column by
its descriptive name that was stored in your hash.
I realise this all sounds rather cryptic. Here is a small perl program
(tested):
#!/usr/bin/perl
use DBI;
# uncomment the following 2 lines if CGI
# print "Content-Type: text/html\n\n<html>\n<pre>\n";
# use CGI::Carp qw(fatalsToBrowser);
$db = DBI->connect("DBI:mysql:NAME:HOST","USR", "PW");
$query = $db->prepare("SELECT
v.old_id, w.name
FROM v
LEFT JOIN w
ON v.old_id = w.widget_id
");
$query->execute;
$numrows = $query->rows;
while (@array = $query->fetchrow_array)
{
my ($var_old_id, $var_name)= @array;
$widgetname{"$var_old_id"} = $var_name;
}
$query->finish;
$db->disconnect;
print "w.name|w.widget_id|v.new_id|v.old_id|\n";
$db = DBI->connect("DBI:mysql:NAME:HOST","USR","PW");
$query = $db->prepare("SELECT
v.old_ID, w.widget_id,
v.new_id, v.old_id
FROM w, v
WHERE w.widget_id =712
AND w.widget_id = v.new_id
ORDER BY v.old_ID");
$query->execute;
$numrows = $query->rows;
while (@array = $query->fetchrow_array)
{
print "$widgetname{$array[0]}|";
print $array[1]."|";
print $array[2]."|";
print $array[3]."|\n";
}
$query->finish;
$db->disconnect;
# uncomment this line if CGI
print "</pre>\n</html>";
Output result (when ran as non-CGI):
w.name|w.widget_id|v.new_id|v.old_id|
Widget 1|712|712|400|
Widget 2|712|712|500|
Widget 3|712|712|600|
Widget 4|712|712|700|
The same script can be made in your favourite api language if you're
not a perl man.
Hope this helps.
--
Bart
