cross joining 3 tables
I have 3 tables...:
CREATE TABLE `users` (
`user_ID` mediumint(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`lastname` varchar(45) NOT NULL,
`email` varchar(255) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL,
`hash` varchar(32) DEFAULT NULL,
`usertype` enum('regular_user','buz_user','superadmin','reg_buz_user','deleted') DEFAULT NULL,
`Reg_date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`registr_hash` varchar(32) DEFAULT NULL,
`active` int(1) NOT NULL DEFAULT '0',
`reset_pass_token` varchar(60) DEFAULT NULL,
`token_expire` int(60) DEFAULT NULL,
PRIMARY KEY (`user_ID`),
UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8
CREATE TABLE `business_users` (
`crID` mediumint(11) unsigned NOT NULL,
`address` varchar(90) DEFAULT NULL,
`url` varchar(90) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`city` varchar(100) DEFAULT NULL,
`municipality` varchar(100) DEFAULT NULL,
`bus_user_type` smallint(2) DEFAULT NULL,
`same_d_appt` tinyint(4) DEFAULT NULL,
`f_words` tinytext,
`apps_timeslot` tinyint(3) unsigned DEFAULT NULL,
`prices_visibility` tinyint(1) unsigned DEFAULT NULL,
`pack_selected` smallint(6) unsigned NOT NULL,
PRIMARY KEY (`crID`),
KEY `fk_business_users_buz_usertype1_idx` (`bus_user_type`),
KEY `pack` (`pack_selected`),
CONSTRAINT `fk_business_users_buz_usertype1` FOREIGN KEY (`bus_user_type`) REFERENCES `buz_usertype` (`Type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_business_users_users1` FOREIGN KEY (`crID`) REFERENCES `users` (`user_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `pack_fk` FOREIGN KEY (`pack_selected`) REFERENCES `packages` (`package_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `buz_usertype` (
`User_type` varchar(25) NOT NULL,
`Type_id` smallint(2) NOT NULL,
PRIMARY KEY (`Type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I want to get the lastname LIKE...from the USERS table and the User_type from the buz_usertype...so...I want to create a row out of these two.(for example..."John Dow","car shop")
If you notice the business_users table references the buz_usertype table when it comes to the bus_user_type column found in the former table.
In other words....if I want to find the buz_usertype.User_type I must look into first the business_users.bus_user_type and then I have to find business_users.crID to which user refers to...by looking at users.user_ID.
Anyway..it's a mess...I tried using some WHERE clauses but with no success.
I hear your suggestions...thanks