Re: quickie for a non-blonde!
Assuming that the lastest ID is the same as the current name - maybe something like : select * from sometab t1 where id=( select max(id) from sometab t2 where t1.code=t2.code ) On Thursday 13 July 2006 10:16, Helen M Hudson wrote: > If I have a table > primary key id | code| name > 1 | ABC | company 1 name > 2 | ABC | company 1 name > 3 | ABC | new company 1 name > 4 | ABC | new company 1 name > 5 | DEF| company 2 name > 6 | DEF| company 2 name > 7 | DEF| new company 2 name > 8 | DEF| new company 2 name > > (the company is changing name every now and then and i need to extract > their "latest" name) > > how can i get back > 4 | ABC | new company 1 name > 8 | DEF| new company 2 name -- Mike Aubury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quickie for a non-blonde!
If I have a table primary key id | code| name 1 | ABC | company 1 name 2 | ABC | company 1 name 3 | ABC | new company 1 name 4 | ABC | new company 1 name 5 | DEF| company 2 name 6 | DEF| company 2 name 7 | DEF| new company 2 name 8 | DEF| new company 2 name (the company is changing name every now and then and i need to extract their "latest" name) how can i get back 4 | ABC | new company 1 name 8 | DEF| new company 2 name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quickie!
Dear Jamie,
in PHP, the mysql_query(SELECT ... LEFT JOIN ...) will return exactly
what you need. Try something like
WHILE ($i < mysql_num_rows) {
for ($j = 0; $j < mysql_num_fields; $j++){
$i[$j];
}
$i++;
}
Regards,
Stefan Hinz (Geschäftsleitung)
iConnect e-commerce solutions GmbH
www.iConnect.de - www.js-webShop.de
TEL +49 30 46307-382 - FAX -388
---
- Original Message -
From: Jamie Burns <[EMAIL PROTECTED]>
To: Dennis Salguero <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Sunday, August 05, 2001 11:48 PM
Subject: Re: quickie!
> that is the answer, but i think i was looking for something different.
>
> because i am using php, i am used to results being returned in an
array, and
> was hoping to get this sort of thing out...
>
> $results[0]['id']// table1.id
> $results[0]['name']// table1.name
> $results[0]['subject'][0] // table2.subject
> $results[0]['subject'][1]// table2.subject
> $results[0]['subject'][2] // table2.subject
>
> i am guessing mysql doesnt have a notion of multidimensional
results...
>
> i will sort them myself ;o)
>
> thanks anyway, just me trying to make things too efficient...
>
> jamie.
>
> - Original Message -
> From: "Dennis Salguero" <[EMAIL PROTECTED]>
> To: "Jamie Burns" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Sunday, August 05, 2001 10:32 PM
> Subject: Re: quickie!
>
>
> > Are you looking for:
> >
> > SELECT table1.name, table2.subject
> > FROM table1, table2
> > WHERE table1.id=table2.student_id
> >
> > Good Luck,
> >
> > Dennis
> > ******
> > Beridney Computer Services
> > http://www.beridney.com
> >
> > - Original Message -
> > From: "Jamie Burns" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Sunday, August 05, 2001 2:25 PM
> > Subject: quickie!
> >
> >
> > hi..
> >
> > can someone tell me how i get the data from two tables, where table1
will
> > have one row, and table2 will have many rows?
> >
> > eg:
> >
> >
> >
> > table1:
> >
> > idname
> >
> > 1barney
> > 2fred
> > 3wilma
> >
> > table2:
> >
> > idstudent_id subject
> >
> > 11 english
> > 11 cookery
> > 11 maths
> > 11 science
> > 12 woodwork
> > 13 cookery
> >
> >
> >
> > do i have to use two queries to get all this data out in usable
form?
> >
> > thanks,
> >
> > jamie.
> >
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quickie!
Dear Jamie, try a JOIN (LEFT JOIN I would suppose for your problem). Read the MySQL manual chapter 7 (look for the join syntax there). It is something like SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id) Regards, Stefan Hinz (Geschäftsleitung) iConnect e-commerce solutions GmbH www.iConnect.de - www.js-webShop.de TEL +49 30 46307-382 - FAX -388 --- - Original Message - From: Jamie Burns <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 05, 2001 11:25 PM Subject: quickie! hi.. can someone tell me how i get the data from two tables, where table1 will have one row, and table2 will have many rows? eg: table1: idname 1barney 2fred 3wilma table2: idstudent_id subject 11 english 11 cookery 11 maths 11 science 12 woodwork 13 cookery do i have to use two queries to get all this data out in usable form? thanks, jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quickie!
that is the answer, but i think i was looking for something different. because i am using php, i am used to results being returned in an array, and was hoping to get this sort of thing out... $results[0]['id']// table1.id $results[0]['name']// table1.name $results[0]['subject'][0] // table2.subject $results[0]['subject'][1]// table2.subject $results[0]['subject'][2] // table2.subject i am guessing mysql doesnt have a notion of multidimensional results... i will sort them myself ;o) thanks anyway, just me trying to make things too efficient... jamie. - Original Message - From: "Dennis Salguero" <[EMAIL PROTECTED]> To: "Jamie Burns" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Sunday, August 05, 2001 10:32 PM Subject: Re: quickie! > Are you looking for: > > SELECT table1.name, table2.subject > FROM table1, table2 > WHERE table1.id=table2.student_id > > Good Luck, > > Dennis > ** > Beridney Computer Services > http://www.beridney.com > > - Original Message ----- > From: "Jamie Burns" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, August 05, 2001 2:25 PM > Subject: quickie! > > > hi.. > > can someone tell me how i get the data from two tables, where table1 will > have one row, and table2 will have many rows? > > eg: > > > > table1: > > idname > > 1barney > 2fred > 3wilma > > table2: > > idstudent_id subject > > 11 english > 11 cookery > 11 maths > 11 science > 12 woodwork > 13 cookery > > > > do i have to use two queries to get all this data out in usable form? > > thanks, > > jamie. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quickie!
Are you looking for: SELECT table1.name, table2.subject FROM table1, table2 WHERE table1.id=table2.student_id Good Luck, Dennis ** Beridney Computer Services http://www.beridney.com - Original Message - From: "Jamie Burns" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 05, 2001 2:25 PM Subject: quickie! hi.. can someone tell me how i get the data from two tables, where table1 will have one row, and table2 will have many rows? eg: table1: idname 1barney 2fred 3wilma table2: idstudent_id subject 11 english 11 cookery 11 maths 11 science 12 woodwork 13 cookery do i have to use two queries to get all this data out in usable form? thanks, jamie. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
quickie!
hi.. can someone tell me how i get the data from two tables, where table1 will have one row, and table2 will have many rows? eg: table1: idname 1barney 2fred 3wilma table2: idstudent_id subject 11 english 11 cookery 11 maths 11 science 12 woodwork 13 cookery do i have to use two queries to get all this data out in usable form? thanks, jamie.
