Re: cross joining 3 tables

[Dimitris Papageorgiou]

I must add something...in the busines_users table U have added a field...comp_name

CREATE TABLE `business_users` (
`crID` mediumint(11) unsigned NOT NULL,
`comp_name` varchar(45) DEFAULT 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



So in the query you gave me...I must this field also..something like that:

select u.name,u.lastname,t.user_type,b.comp_name
from users u
join business_users b on u.user_id=b.crid
join buz_usertype t on b.bus_user_type=t.type_id
where u.lastname or b.comp_name like 'doe%%';

The above just gives me comp_name...I want both u.lastname and b.compname.

As such if user table has one row and business users another...the result of the above query must be 2 rows.