RE: basic query question
to create a second instance of the same table and this will probably show the relationship twice SELECT p1.lname, p1.fname, p2.lname, p2.fname FROM members AS 'p1' LEFT JOIN members AS 'p2' ON p1.engagedto = p2.id not sure if you can do the LEFT JOIN table AS 'table2' or not, can't get to mysql.com to check the documentation :( it will be something similar however your table being in this structure of course +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 18| | 273 | Simmons | Maria | 78| | 221 | Papa | Sharla | 123 | | 18 | Biehl | Ruth| 131 | | 302 | Vance | Alicia | 204 | | 123 | Goettl| Christopher | 221 | | 78 | Ellsworth | Morgan | 273 | | 204 | Millet| David | 302 | +-+---+-+---+ -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 2:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql> select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: "Roger Karnouk" <[EMAIL PROTECTED]> To: "Dean Householder" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.
Re: basic query question
Thank you all so much for helping me with this query. It works! Best of all, now I understand how to join tables and such! Thanks again! - Original Message - From: "Rick Emery" <[EMAIL PROTECTED]> To: "'Dean Householder'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 1:04 PM Subject: RE: basic query question You're very close: mysql> select p1.lname, p1.fname, p2.lname as engaged_lname, p2.fname as engaged_fname from members p1, members p2 where p1.id = p2.engagedto; but the REAL question is "What do you want out of the query"? -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 1:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql> select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: "Roger Karnouk" <[EMAIL PROTECTED]> To: "Dean Householder" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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)
RE: basic query question
You're very close: mysql> select p1.lname, p1.fname, p2.lname as engaged_lname, p2.fname as engaged_fname from members p1, members p2 where p1.id = p2.engagedto; but the REAL question is "What do you want out of the query"? -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 1:53 PM To: Roger Karnouk; [EMAIL PROTECTED] Subject: Re: basic query question Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql> select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: "Roger Karnouk" <[EMAIL PROTECTED]> To: "Dean Householder" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http
Re: basic query question
Okay, so I do this and all I end up with is +-+---+-+---+ | id | lname | fname | engagedto | +-+---+-+---+ | 131 | Hallows | Samuel | 131 | | 273 | Simmons | Maria | 273 | | 221 | Papa | Sharla | 221 | | 18 | Biehl | Ruth| 18| | 302 | Vance | Alicia | 302 | | 123 | Goettl| Christopher | 123 | | 78 | Ellsworth | Morgan | 78| | 204 | Millet| David | 204 | +-+---+-+---+ 8 rows in set (0.00 sec) The returning query doesn't seem very helpful. I guess what I want is mysql to return the results with each person they are engaged to in order. I added the p1.id and p1.engagedto fields. mysql> select p1.id, p1.lname, p1.fname, p1.engagedto, p2.engagedto from members p1, members p2 where p1.id = p2.engagedto; +-+---+-+---+---+ | id | lname | fname | engagedto | engagedto | +-+---+-+---+---+ | 131 | Hallows | Samuel | 18| 131 | | 273 | Simmons | Maria | 78| 273 | | 221 | Papa | Sharla | 123 | 221 | | 18 | Biehl | Ruth| 131 | 18| | 302 | Vance | Alicia | 204 | 302 | | 123 | Goettl| Christopher | 221 | 123 | | 78 | Ellsworth | Morgan | 273 | 78| | 204 | Millet| David | 302 | 204 | +-+---+-+---+---+ 8 rows in set (0.01 sec) This at least shows me the info I had in my original table. If I looked at it I could see that id# 131 should match up with id# 18 and such. I guess I'm looking for a query that will match these to up either in the same row or one after another. Any help is SOOO appreciated! Dean - Original Message - From: "Roger Karnouk" <[EMAIL PROTECTED]> To: "Dean Householder" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 12:17 PM Subject: RE: basic query question you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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 - 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: basic query question
you will have to join the table with itself and reference it as if it is two tables. this might be slow however so you might want to make some specific indexes to speed it up. This Query will return all the people who are engaged and a picture of their fiancé: select p1.lname, p1.fname, p1.pic, p2.pic from people p1, people p2 where p1.id = p2.engagedto; this query will return people whether they are engaged or not: select p1.lname, p1.fname, p1.pic, p2.pic from people p1 left join people p2 on (p1.id = p2.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 12:22 PM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
> select id, lname, fname, pic from database where engagedto=id; Sorry, mate, that won't work. That will find folks who are engaged to themselves, i.e., narcissists -Original Message- From: Oliver Heinisch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 11:30 AM To: [EMAIL PROTECTED] Subject: Re: basic query question At 19.02.2002 10:22, you wrote: >I'm fairly new to MySQL, so if anyone could help me I'd really appreciate >it! > >What I'm trying to do is join two pictures in a database to each other. My >database holds info about people with variables: > >id, lname, fname, engagedto, pic Try this, even if I´m careful due to my bad day, see todays threads ;-) select id, lname, fname, pic from database where engagedto=id; >The engagedto field contains the id of the person they are engaged to. I'm >trying to print the people that are engaged to each other next to each >other. I've played with the join command and a little with group but don't >really understand how these work... If anyone could point me in the right >direction regarding how to query the database to return these records >connected to each other, I would really appreciate it! > >Thanks so much > >Dean - 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: basic query question
assuming that engaedto refers to an id, your query might be: SELECT a.lname,a.fname,b.engagedto FROM mydata a LEFT JOIN mydata b ON(a.id=b.engagedto); -Original Message- From: Dean Householder [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 19, 2002 11:22 AM To: [EMAIL PROTECTED] Subject: basic query question I'm fairly new to MySQL, so if anyone could help me I'd really appreciate it! What I'm trying to do is join two pictures in a database to each other. My database holds info about people with variables: id, lname, fname, engagedto, pic The engagedto field contains the id of the person they are engaged to. I'm trying to print the people that are engaged to each other next to each other. I've played with the join command and a little with group but don't really understand how these work... If anyone could point me in the right direction regarding how to query the database to return these records connected to each other, I would really appreciate it! Thanks so much Dean - 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: basic query question
At 19.02.2002 10:22, you wrote: >I'm fairly new to MySQL, so if anyone could help me I'd really appreciate >it! > >What I'm trying to do is join two pictures in a database to each other. My >database holds info about people with variables: > >id, lname, fname, engagedto, pic Try this, even if I´m careful due to my bad day, see todays threads ;-) select id, lname, fname, pic from database where engagedto=id; >The engagedto field contains the id of the person they are engaged to. I'm >trying to print the people that are engaged to each other next to each >other. I've played with the join command and a little with group but don't >really understand how these work... If anyone could point me in the right >direction regarding how to query the database to return these records >connected to each other, I would really appreciate it! > >Thanks so much > >Dean - 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
