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 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!
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.
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!
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!
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!
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
