Re: Join vs. Where (help)
Hi Bruce, tlr7425 and MySQl List, 2003年 6月 26日 木曜日 10:25、Nils Valentin さんは書きました: > Hi Bruce; > > I havent read Pauls book, but I will try to make an sample, also to confirm > for myself what I stated before. > > I will come back to it once more. I am not sure if I should create a now > post for this or continue this threat as it s kind of attached to it. > > Thank you for your reply. > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 6月 25日 水曜日 22:09、Bruce Feist さんは書きました: > > Nils Valentin wrote: > > SELECT * FROM machines INNER JOIN people ON > > > > >machines.peopleID=people.peopleID; > > > > Inludes NULL records > > > > > >I meant the first statement returns also empty record fields or should I > > > say incomplete data records ? > > > > > >Is not producing the same results as this: > > > > > >SELECT lastname, model FROM people, machines WHERE machines.peopleID > > >= > > >people.peopleID; > > > > Dosent include NULL records > > > > > >This one doesnt return any entries with incomplete data records (no data > > > in them). So if you have an entry for machines.peopleID but not for > > >people.peopeID than it wont show up while it does in the first > > > statement. > > > > Nils, I disagree. The INNER JOIN should be completely equivalent to the > > (corrected) "WHERE" version. This is supported by Paul DuBois's > > "MySQL", second edition, which states that the INNER JOIN is equivalent > > to the comma operator except that it allows and requires the ON clause. > > It is also supported by standard SQL usage in other RDBMSs. > > > > Bruce Feist I double checked it. Bruce is correct - I am the looser ;-). I mistook the INNER JOIN. My appologize for that. All credits to Bruce. Please ignore my posting for this topic. Thank you for pointing this out. Best regards Nils Valentin Tokyo/Japan > > -- > --- > Valentin Nils > Internet Technology > > E-Mail: [EMAIL PROTECTED] > URL: http://www.knowd.co.jp > Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Hi Bruce; I havent read Pauls book, but I will try to make an sample, also to confirm for myself what I stated before. I will come back to it once more. I am not sure if I should create a now post for this or continue this threat as it s kind of attached to it. Thank you for your reply. Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 22:09、Bruce Feist さんは書きました: > Nils Valentin wrote: > SELECT * FROM machines INNER JOIN people ON > > >machines.peopleID=people.peopleID; > > Inludes NULL records > > > >I meant the first statement returns also empty record fields or should I > > say incomplete data records ? > > > >Is not producing the same results as this: > > > >SELECT lastname, model FROM people, machines WHERE machines.peopleID > >= > >people.peopleID; > > Dosent include NULL records > > > >This one doesnt return any entries with incomplete data records (no data > > in them). So if you have an entry for machines.peopleID but not for > >people.peopeID than it wont show up while it does in the first statement. > > Nils, I disagree. The INNER JOIN should be completely equivalent to the > (corrected) "WHERE" version. This is supported by Paul DuBois's > "MySQL", second edition, which states that the INNER JOIN is equivalent > to the comma operator except that it allows and requires the ON clause. > It is also supported by standard SQL usage in other RDBMSs. > > Bruce Feist -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help) -(I got it!)
In the second query, the server may switch the order of the join, producing the same output in different order. Try adding an ORDER BY to both and see what happens. [EMAIL PROTECTED] wrote: Ok, should be this: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Ted This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Nils Valentin wrote: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Inludes NULL records I meant the first statement returns also empty record fields or should I say incomplete data records ? Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Dosent include NULL records This one doesnt return any entries with incomplete data records (no data in them). So if you have an entry for machines.peopleID but not for people.peopeID than it wont show up while it does in the first statement. Nils, I disagree. The INNER JOIN should be completely equivalent to the (corrected) "WHERE" version. This is supported by Paul DuBois's "MySQL", second edition, which states that the INNER JOIN is equivalent to the comma operator except that it allows and requires the ON clause. It is also supported by standard SQL usage in other RDBMSs. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
>>> SELECT * FROM machines INNER JOIN people ON > >>> machines.peopleID=people.peopleID; > >> > >> Inludes NULL records I meant the first statement returns also empty record fields or should I say incomplete data records ? > >> > >>> Is not producing the same results as this: > >>> > >>> SELECT lastname, model FROM people, machines WHERE machines.peopleID > >>> = > >>> people.peopleID; > >> > >> Dosent include NULL records This one doesnt return any entries with incomplete data records (no data in them). So if you have an entry for machines.peopleID but not for people.peopeID than it wont show up while it does in the first statement. Does it make it a bit clearer ? Sorry dont know how to better explain it really. Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 17:[EMAIL PROTECTED] さんは書きました: > What do you mean when you say, in this case, "does not return the NULL > record"? > > I have no records that are completely "NULL" though some records do > have a NULL field or two? > > Thanks, > Ted > > On Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: > > Hi tlr7425, > > > > Besides what I said in my last e-mail I completely overlooked that > > > >>> SELECT * FROM machines INNER JOIN people ON > >>> machines.machinesID=people.peopleID; > > > > should be > > > > SELECT * FROM machines INNER JOIN people ON > > machines.peopleID=people.peopleID; > > > > Even though my first statement should still remain correct. The > > statement > > above does not return the NULL record while the other one does INNER > > JOIN) > > > > Best regards > > > > Nils Valentin > > Tokyo/Japan > > > > 2003年 6月 25日 水曜日 15:37、Nils Valentin さんは書きました: > >> 2003年 6月 25日 水曜日 15:[EMAIL PROTECTED] さんは書きました: > >>> This: > >>> > >>> SELECT * FROM machines INNER JOIN people ON > >>> machines.machinesID=people.peopleID; > >> > >> Inludes NULL records > >> > >>> Is not producing the same results as this: > >>> > >>> SELECT lastname, model FROM people, machines WHERE machines.peopleID > >>> = > >>> people.peopleID; > >> > >> Dosent include NULL records > >> > >>> Can someone please tell why, what's wrong? > >> > >> Nothing is wrong thats the way it is supposed to be. > >> > >>> (What happens is that the wrong person are listed with the wrong > >>> machine -using the INNER JOIN shown above, the second statement I > >>> listed works as expected, right person with right machine.) > >>> > >>> Thanks in Advance, > >>> Ted Rogers > >> > >> This is a good example which is also described on page 202-203 Michael > >> Kofler Mysql (Edition1 ) > >> > >> Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
What do you mean when you say, in this case, "does not return the NULL (Brecord"? (B (BI have no records that are completely "NULL" though some records do (Bhave a NULL field or two? (B (BThanks, (BTed (B (B (BOn Wednesday, June 25, 2003, at 02:42 AM, Nils Valentin wrote: (B (B> Hi tlr7425, (B> (B> Besides what I said in my last e-mail I completely overlooked that (B> (B>>> SELECT * FROM machines INNER JOIN people ON (B>>> machines.machinesID=people.peopleID; (B> (B> should be (B> (B> SELECT * FROM machines INNER JOIN people ON (B> machines.peopleID=people.peopleID; (B> (B> Even though my first statement should still remain correct. The (B> statement (B> above does not return the NULL record while the other one does INNER (B> JOIN) (B> (B> Best regards (B> (B> Nils Valentin (B> Tokyo/Japan (B> (B> (B> 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:37$B!"(BNils Valentin $B$5$s$O=q$-$^$7$?(B: (B>> 2003$BG/(B 6$B7n(B 25$BF|(B $B?eMKF|(B 15:01$B!"([EMAIL PROTECTED] $B$5$s$O=q$-$^$7$?(B: (B>>> This: (B>>> (B>>> SELECT * FROM machines INNER JOIN people ON (B>>> machines.machinesID=people.peopleID; (B>> (B>> Inludes NULL records (B>> (B>>> Is not producing the same results as this: (B>>> (B>>> SELECT lastname, model FROM people, machines WHERE machines.peopleID (B>>> = (B>>> people.peopleID; (B>> (B>> Dosent include NULL records (B>> (B>>> Can someone please tell why, what's wrong? (B>> (B>> Nothing is wrong thats the way it is supposed to be. (B>> (B>>> (What happens is that the wrong person are listed with the wrong (B>>> machine -using the INNER JOIN shown above, the second statement I (B>>> listed works as expected, right person with right machine.) (B>>> (B>>> Thanks in Advance, (B>>> Ted Rogers (B>> (B>> This is a good example which is also described on page 202-203 Michael (B>> Kofler Mysql (Edition1 ) (B>> (B>> Best regards
Re: Join vs. Where (help)
Hi tlr7425, Besides what I said in my last e-mail I completely overlooked that > > SELECT * FROM machines INNER JOIN people ON > > machines.machinesID=people.peopleID; should be SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Even though my first statement should still remain correct. The statement above does not return the NULL record while the other one does INNER JOIN) Best regards Nils Valentin Tokyo/Japan 2003年 6月 25日 水曜日 15:37、Nils Valentin さんは書きました: > 2003年 6月 25日 水曜日 15:[EMAIL PROTECTED] さんは書きました: > > This: > > > > SELECT * FROM machines INNER JOIN people ON > > machines.machinesID=people.peopleID; > > Inludes NULL records > > > Is not producing the same results as this: > > > > SELECT lastname, model FROM people, machines WHERE machines.peopleID = > > people.peopleID; > > Dosent include NULL records > > > Can someone please tell why, what's wrong? > > Nothing is wrong thats the way it is supposed to be. > > > (What happens is that the wrong person are listed with the wrong > > machine -using the INNER JOIN shown above, the second statement I > > listed works as expected, right person with right machine.) > > > > Thanks in Advance, > > Ted Rogers > > This is a good example which is also described on page 202-203 Michael > Kofler Mysql (Edition1 ) > > Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
2003年 6月 25日 水曜日 15:[EMAIL PROTECTED] さんは書きました: > This: > > SELECT * FROM machines INNER JOIN people ON > machines.machinesID=people.peopleID; Inludes NULL records > > Is not producing the same results as this: > > SELECT lastname, model FROM people, machines WHERE machines.peopleID = > people.peopleID; Dosent include NULL records > > Can someone please tell why, what's wrong? Nothing is wrong thats the way it is supposed to be. > > (What happens is that the wrong person are listed with the wrong > machine -using the INNER JOIN shown above, the second statement I > listed works as expected, right person with right machine.) > > Thanks in Advance, > Ted Rogers This is a good example which is also described on page 202-203 Michael Kofler Mysql (Edition1 ) Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help)
Erm, the first sql as i can see doesn't connect on the same field as the second one that produces the correct result. George Christoforakis - Original Message - From: <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Wednesday, June 25, 2003 9:01 AM Subject: Join vs. Where (help) > This: > > SELECT * FROM machines INNER JOIN people ON > machines.machinesID=people.peopleID; > > Is not producing the same results as this: > > SELECT lastname, model FROM people, machines WHERE machines.peopleID = > people.peopleID; > > Can someone please tell why, what's wrong? > > (What happens is that the wrong person are listed with the wrong > machine -using the INNER JOIN shown above, the second statement I > listed works as expected, right person with right machine.) > > Thanks in Advance, > Ted Rogers > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join vs. Where (help) -(I got it!)
Ok, should be this: SELECT * FROM machines INNER JOIN people ON machines.peopleID=people.peopleID; Ted This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers
Join vs. Where (help)
This: SELECT * FROM machines INNER JOIN people ON machines.machinesID=people.peopleID; Is not producing the same results as this: SELECT lastname, model FROM people, machines WHERE machines.peopleID = people.peopleID; Can someone please tell why, what's wrong? (What happens is that the wrong person are listed with the wrong machine -using the INNER JOIN shown above, the second statement I listed works as expected, right person with right machine.) Thanks in Advance, Ted Rogers
