Jump to content


Photo

Cant select the right rows


  • Please log in to reply
6 replies to this topic

#1 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 26 January 2006 - 12:38 AM

hi guys, once again my arch enemy MySQL is beating me, heres the query im trying to get to work:
SELECT * FROM `orders` o, `users` u, `shipaddresses` sa WHERE (o.orders_user_id = u.users_userid AND sa.shipaddresses_user_id=o.orders_user_id AND sa.shipaddresses_type='Ship' AND o.orders_status != '')

i end up getting multiple copies of some rows, ive checked and theres only 1 copy of each, heres exports of these tables:
CREATE TABLE `orders` (
  `orders_order_id` mediumint(8) NOT NULL auto_increment,
  `orders_user_id` mediumint(8) NOT NULL default '0',
  `orders_order_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `orders_shipping` varchar(50) NOT NULL default '',
  `orders_shipping_price` decimal(12,2) NOT NULL default '0.00',
  `orders_tax` decimal(12,2) NOT NULL default '0.00',
  `orders_rush_fee` decimal(12,2) NOT NULL default '0.00',
  `orders_items_total` decimal(12,2) NOT NULL default '0.00',
  `orders_ship1` decimal(12,2) NOT NULL default '0.00',
  `orders_ship2` decimal(12,2) NOT NULL default '0.00',
  `orders_ship3` decimal(12,2) NOT NULL default '0.00',
  `orders_price` decimal(12,2) NOT NULL default '0.00',
  `orders_status` varchar(100) NOT NULL default '',
  `orders_tracking` varchar(100) NOT NULL default '',
  `orders_comments` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`orders_order_id`)
) TYPE=MyISAM AUTO_INCREMENT=595030;

CREATE TABLE `shipaddresses` (
  `shipaddresses_user_id` mediumint(8) NOT NULL default '0',
  `shipaddresses_type` varchar(100) NOT NULL default '',
  `shipaddresses_first_name` varchar(50) NOT NULL default '',
  `shipaddresses_last_name` varchar(50) NOT NULL default '',
  `shipaddresses_street_1` varchar(100) NOT NULL default '',
  `shipaddresses_street_2` varchar(100) NOT NULL default '',
  `shipaddresses_city` varchar(50) NOT NULL default '',
  `shipaddresses_state` varchar(50) NOT NULL default '',
  `shipaddresses_zip` mediumint(15) NOT NULL default '0',
  `shipaddresses_day_phone` varchar(25) NOT NULL default '',
  `shipaddresses_night_phone` varchar(25) NOT NULL default '',
  `shipaddresses_fax` varchar(25) NOT NULL default '',
  `shipaddresses_order_id` mediumint(8) NOT NULL default '0',
  `shipaddresses_email_address` varchar(100) NOT NULL default ''
) TYPE=MyISAM;

CREATE TABLE `users` (
  `users_userid` mediumint(11) NOT NULL auto_increment,
  `users_username` varchar(255) NOT NULL default '',
  `users_password` varchar(255) NOT NULL default '',
  `users_first_name` varchar(50) NOT NULL default '',
  `users_last_name` varchar(50) NOT NULL default '',
  `users_email_address` varchar(100) NOT NULL default '',
  `users_signup_date` int(11) default NULL,
  `users_notes` text NOT NULL,
  `users_user_level` tinyint(1) NOT NULL default '0',
  `users_active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`users_userid`)
) TYPE=MyISAM AUTO_INCREMENT=6;

i think that i may need to do some joins, but i dont know

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 January 2006 - 01:45 AM

Try to enforce the `shipaddresses_order_id` constraint as well.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 26 January 2006 - 02:21 AM

thak you, its been a long day, im sure that that should fix it, thanks again fenway, btw how long have you been doing this?

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 January 2006 - 04:04 AM

I hope you get it working.

[!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]If by "this" you mean MySQL, then quite a few years (since v3.23) -- but it's not the amount of time that counts, but rather the sheer number of database tables I've had to set up since then (probably close to a thousand at this point).[!--sizec--][/span][!--/sizec--]
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 26 January 2006 - 12:08 PM

do you do this professionally? ie as a webdesigner?

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 January 2006 - 01:53 PM

Yes -- web designer, project leader, software developer, DBA, etc. -- you name it.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 spyke01

spyke01
  • Members
  • PipPipPip
  • Advanced Member
  • 74 posts

Posted 26 January 2006 - 02:02 PM

awesome, im network, website, and database admin/developer, its laways nice to have someone on the same page that knows his/her stuff, thanx again for all your help, ill probally be back for more questions later

till then,
thanx

[a href="http://www.fasttracksites.com" target="_blank"]Cheap PHP Hosting[/a]




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users