managing to botch my first simple join in mysql :(

[hugeness]

Hello

 

unfortunately having a problem with my first Join for two tables (below) using as a test the following mysql statement:

 

SELECT duplicate.un, upload.pw

FROM duplicate, upload

WHERE duplicate.un = upload.un

 

i have seeded the tables with the same values in the 'un' field and values in the pw fields to call up.. but the query is still coming up empty..

any ideas what i am doing wrong?

 

thank you!

 

 

---

CREATE TABLE IF NOT EXISTS `upload` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(30) NOT NULL,

  `type` varchar(30) NOT NULL,

  `size` int(11) NOT NULL,

  `content` mediumblob NOT NULL,

  `pw` varchar(50) NOT NULL,

  `un` varchar(200) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

---

 

 

---

CREATE TABLE IF NOT EXISTS `duplicate` (

  `lud` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  `id` int(11) NOT NULL AUTO_INCREMENT ,

  `un` varchar(200) NOT NULL ,

  `pw` varchar(50) NOT NULL ,

  `ttl` varchar(150) NOT NULL ,

  `fn` varchar(250) NOT NULL ,

  `ln` varchar(250) NOT NULL ,

  `tel` varchar(20) NOT NULL ,

  `conteml` varchar(200) NOT NULL ,

  `cnm` varchar(250) NOT NULL ,

  `cyr` year(4) NOT NULL ,

  `add1` varchar(250) NOT NULL,

  `add2` varchar(250) NOT NULL,

  `city` varchar(250) NOT NULL,

  `state` varchar(2) NOT NULL,

  `zip` varchar(9) NOT NULL,

  `stel` varchar(20) NOT NULL ,

  `seml` varchar(200) NOT NULL ,

  `usp` varchar(256) NOT NULL ,

  `http` text NOT NULL ,

  `part_note` text NOT NULL ,

  `othf` text NOT NULL ,

  `newRight` text NOT NULL,

  `ts5` text NOT NULL,

  `ts7` varchar(250) NOT NULL ,

  `tc1` varchar(100) NOT NULL ,

  `tc2` varchar(100) NOT NULL,

  `tc3` varchar(100) NOT NULL,

  `tst1` varchar(2) NOT NULL ,

  `tst2` varchar(2) NOT NULL,

  `tst3` varchar(2) NOT NULL,

  `tst4` varchar(2) NOT NULL,

  `tst5` varchar(2) NOT NULL,

  `tst6` varchar(2) NOT NULL,

  `nw` varchar(3) NOT NULL ,

  `pr` varchar(3) NOT NULL ,

  `ps1` varchar(3) NOT NULL ,

  `ps6` varchar(3) NOT NULL,

  `ps16` varchar(3) NOT NULL,

  `ps26` varchar(3) NOT NULL,

  `ps46` varchar(3) NOT NULL,

  `ps100` varchar(3) NOT NULL,

  `cent1` tinyint(4) NOT NULL ,

  `cent6` tinyint(4) NOT NULL,

  `cent16` tinyint(4) NOT NULL,

  `cent26` tinyint(4) NOT NULL,

  `cent46` tinyint(4) NOT NULL,

  `cent100` tinyint(4) NOT NULL,

  `fos` varchar(3) NOT NULL ,

  `clnt_no` int(11) NOT NULL ,

  `hod_no` int(11) NOT NULL ,

  `more_hod` varchar(3) NOT NULL ,

  `av_bill` int(11) NOT NULL ,

  `clnt_new` int(11) NOT NULL ,

  `fte_no` int(11) NOT NULL ,

  `geek_no` int(11) NOT NULL ,

  `off_yn` varchar(3) NOT NULL ,

  `off_more` varchar(3) NOT NULL ,

  `cre` date NOT NULL,

  KEY `id` (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

---

[akitchin]

my first guess is that you may have some reserved keywords in there that require backticks, or that your values aren't identical due to the use of VARCHAR for the column types. do you receive any error?

[hugeness]

nope, all green on the message front:

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec )

hmmm.. will check for reserves.. yes i could imagine '

duplicate' could be an issue now you come to mention it!

thanks!

 

 

my first guess is that you may have some reserved keywords in there that require backticks, or that your values aren't identical due to the use of VARCHAR for the column types. do you receive any error?

[hugeness]

can't see any reserved keywords from the list here..

http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-1.html

[fenway]

I don't believe there's anything in those tables... prove it.

[hugeness]

I don't believe there's anything in those tables... prove it.

well theres nothing in the tables i have posted, because i emptied them before posting, but have just been filling with test data, such as putting test in the pw and un fields...

so should the code work from what you know?

[fenway]

I mean demonstrate that simple queries work.

[hugeness]

I mean demonstrate that simple queries work.

 

they are being populated from 2 seperate forms.

 

INSERT INTO duplicate (un,pw,ttl,fn,ln,tel,conteml,cnm,cyr,add1,add2,city,state,zip,stel,seml,usp,http,othf,newRight,ts5,ts7,tc1,tc2,tc3,tst1,tst2,tst3,tst4,tst5,tst6,nw,pr,ps1,ps6,ps16,ps26,ps46,ps100,cent1,cent6,cent16,cent26,cent46,cent100,fos,clnt_no,hod_no,more_hod,av_bill,clnt_new,fte_no,geek_no,off_yn,off_more,cre)

 

VALUES ( '$un','$pw','$ttl','$fn','$ln','$tel','$conteml','$cnm','$cyr','$add1','$add2','$city','$state','$zip','$stel','$seml','$usp','$http','$othf','$newRight','$ts5','$ts7','$tc1','$tc2','$tc3','$tst1','$tst2','$tst3','$tst4','$tst5','$tst6','$nw','$pr','$ps1','$ps6','$ps16','$ps26','$ps46','$ps100','$cent1','$cent6','$cent16','$cent26','$cent46','$cent100','$fos','$clnt_no','$hod_no','$more_hod','$av_bill','$clnt_new','$fte_no','$geek_no','$off_yn','$off_more','$cre')";

 

and

 

"INSERT INTO upload (name, size, type, content,pw,un ) ".

"VALUES ('$fileName', '$fileSize', '$fileType', '$content','$pw','$un')";

 

work so the simple stuff works.

[akitchin]

i think what he meant was, show the query and its returned recordset from something simple like "SELECT * FROM duplicate WHERE un='value'" and "SELECT * FROM upload WHERE un='value'".

[hugeness]

SELECT *

FROM `duplicate`

WHERE un = 'jjokoja'

LIMIT 0 , 30

 

Showing rows 0 - 0 (1 total, Query took 0.0005 sec)

 

so that worked fine.

 

but the second table, upload, wont work!

very strange! why would a table with the same setup for the same two columns fail a simple select?

 

SELECT * FROM `upload` WHERE un = 'jjokoja'

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec )

[akitchin]

have you tried just selecting the whole table?

 

SELECT * from `upload`

 

that should show you.

[hugeness]

That actually works, it selects the one row in the table...

Showing rows 0 - 0 (1 total, Query took 0.0003 sec)

 

d name type size content pw un

Edit Delete 1 ellie2.gif image/gif 10615 [bLOB - 10.4  KiB] truncate truncate

[akitchin]

and did you confirm that the row indeed has a `un` value of 'jjokoja'?

[hugeness]

It has a value of 'truncate' now.. but yes, the result is outputted in my last post.