How about: mysql> select count(*) from usernames INNER JOIN pictures ON usernames.user=pictures.user where state = "Delaware" order by ask limit 25;
-----Original Message----- From: Boex,Matthew W. [mailto:[EMAIL PROTECTED]] Sent: Friday, January 04, 2002 2:09 PM To: [EMAIL PROTECTED] Subject: JOIN question howdy, i have a table, pictures, with usernames and pictures like below. mysql> select * from pictures; +------+-----------------------------------------------+ | user | pictures | +------+-----------------------------------------------+ | 189 | aftermath189.jpg | | 189 | OSEIiod189.jpg | | 189 | Isla_de_la_vida_1024189.jpg | | 189 | energynew189.jpg | | 189 | emblem189.jpg | | 189 | small_oasis189.jpg | +------+-----------------------------------------------+ i have another table, usernames, with a ton of columns, name, date, address, etc... what i want to do is select all the columns from usernames based on the state, and if they have a picture, give me a yes or no. here is what i have so far... mysql> select usernames.user,ask,pass,pictures.user from usernames INNER JOIN pictures ON usernames.user=pictures.user where state = "Delaware" order by ask limit 25; +--------+-------------+---------+-------+ | user | ask | pass | user | +--------+-------------+--------+--------+ | 189 | 2222222 | F | 189 | | 189 | 2222222 | F | 189 | | 189 | 2222222 | F | 189 | | 189 | 2222222 | F | 189 | | 189 | 2222222 | F | 189 | | 189 | 2222222 | F | 189 | +--------+-------------+--------+--------+ since this user has six pictures( they can have from 0 to 6), it gives me 6 lines of output. what i am looking for is just one line of output. something like below... +--------+-------------+---------+-------------+ | user | ask | pass | pictures | +--------+-------------+--------+--------------+ | 189 | 2222222 | F | (something that says yes or no, 1 or 0, anything) | +--------+-------------+--------+--------------+ i need all of the requested columns from usernames, and just one from pictures. is this possible? matt --------------------------------------------------------------------- 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