Jump to content

Archived

This topic is now archived and is closed to further replies.

spyke01

Cant select the right rows

Recommended Posts

hi guys, once again my arch enemy MySQL is beating me, heres the query im trying to get to work:
[code]
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 != '')
[/code]

i end up getting multiple copies of some rows, ive checked and theres only 1 copy of each, heres exports of these tables:
[code]
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;

[/code]
i think that i may need to do some joins, but i dont know

Share this post


Link to post
Share on other sites
Try to enforce the `shipaddresses_order_id` constraint as well.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
Yes -- web designer, project leader, software developer, DBA, etc. -- you name it.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.