select users.usr as email, users.aktiv, users.newsletter, users.emailblock, users.htmlmode, users.none_registered, users_info.* from users, users_info WHERE users.id = users_info.cid That would work. There's no obvious way to join in the 3rd table, so you'll have to select that separately.. -Micah On Saturday 14 August 2004 11:05 am, Kim Steinhaug wrote: > Here are some tables to reflect the accual problem : > So if you can select the two records in one query I would be > very happy, :D > > What I want is this : > > users.id = users_info.cid > > I would like to select from users, > usr (which is the email ) as email, > aktiv, > newsletter, > emailblock, > htmlmode, > none_registered > > and * from users_info where users.id = users_info.cid > > At the same time from unregistered_users I would like > epost, > newsletter, > htmlmode, > newsletter, > emailblock > > Theese two should be merged together, if at all possible. > It would also be nessersary to include a new field which tells > from what table the result came from, meaning did it come from > users or from unregistered_users. > > Well, I might stick to ALTERNATIVE 3 it seems. > > DATABASE TABLES WITH 2 ENTRIES : > ---------------------------------------------- > CREATE TABLE `users` ( > `id` int(11) NOT NULL auto_increment, > `usr` varchar(100) NOT NULL default '', > `pas` varchar(32) NOT NULL default '', > `aktiv` tinyint(1) NOT NULL default '0', > `aktivcode` varchar(10) NOT NULL default '', > `level` tinyint(1) unsigned NOT NULL default '0', > `price_group` tinyint(1) unsigned NOT NULL default '1', > `shipping` tinyint(1) unsigned NOT NULL default '0', > `suspend` tinyint(1) unsigned NOT NULL default '0', > `newsletter` tinyint(1) unsigned NOT NULL default '0', > `emailblock` tinyint(1) unsigned NOT NULL default '0', > `emailstatus` tinyint(1) unsigned NOT NULL default '0', > `emailcount` int(5) unsigned NOT NULL default '0', > `htmlmode` tinyint(1) unsigned NOT NULL default '1', > `timecreate` int(10) unsigned NOT NULL default '1071245466', > `none_registered` tinyint(1) unsigned NOT NULL default '0', > PRIMARY KEY (`id`), > KEY `i_id` (`id`) > ) TYPE=MyISAM AUTO_INCREMENT=2 ; > > INSERT INTO `users` VALUES (1, 'kim@xxxxxxxxxxxxx', 'testing', 1, '123', 1, > 2, 1, 0, 1, 1, 0, 126, 1, 0, 0); > > CREATE TABLE `users_info` ( > `id` int(11) NOT NULL auto_increment, > `cid` int(11) NOT NULL default '0', > `cstatus` tinyint(1) unsigned NOT NULL default '0', > `navn` varchar(50) NOT NULL default '', > `etternavn` varchar(50) NOT NULL default '', > `adresse` varchar(100) NOT NULL default '', > `postnummer` varchar(10) NOT NULL default '', > `poststed` varchar(30) NOT NULL default '', > `telefon` varchar(13) NOT NULL default '', > `mobil` varchar(13) NOT NULL default '', > `epost` varchar(100) NOT NULL default '', > `brukernavn` varchar(100) NOT NULL default '', > `passord` varchar(32) NOT NULL default '', > `fdatod` int(2) NOT NULL default '0', > `fdatom` int(2) NOT NULL default '0', > `fdatoy` int(4) NOT NULL default '0', > `extraa` varchar(50) NOT NULL default '', > `extrab` varchar(50) NOT NULL default '', > `extrac` varchar(50) NOT NULL default '', > `extraba` varchar(255) NOT NULL default '', > `extrabb` varchar(255) NOT NULL default '', > `orgnr` varchar(15) NOT NULL default '', > `fnavn` varchar(50) NOT NULL default '', > `fadresse` varchar(100) NOT NULL default '', > `fpbox` varchar(100) NOT NULL default '', > `fpostnummer` varchar(10) NOT NULL default '', > `fpoststed` varchar(30) NOT NULL default '', > `lnavn` varchar(50) NOT NULL default '', > `ladresse` varchar(100) NOT NULL default '', > `lpbox` varchar(100) NOT NULL default '', > `lpostnummer` varchar(10) NOT NULL default '', > `lpoststed` varchar(30) NOT NULL default '', > PRIMARY KEY (`id`), > UNIQUE KEY `id` (`id`), > KEY `id_2` (`id`), > KEY `i_cid` (`cid`), > FULLTEXT KEY `all_fields` > (`navn`,`etternavn`,`adresse`,`postnummer`,`poststed`,`telefon`,`mobil`,`ep >o st`,`orgnr`,`fnavn`,`fadresse`,`fpbox`,`fpostnummer`,`fpoststed`) > ) TYPE=MyISAM AUTO_INCREMENT=2 ; > > INSERT INTO `users_info` VALUES (1, 1, 1, 'Kim', 'Steinhaug', 'PB 8149 > Vaagsbygd', '4622', 'Kristiansand', '8800945', '', 'kim@xxxxxxxxxxxxx', '', > '', 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', > ''); > > CREATE TABLE `unregistered_users` ( > `id` int(11) NOT NULL auto_increment, > `navn` varchar(100) NOT NULL default '', > `epost` varchar(50) NOT NULL default '', > `htmlmode` tinyint(1) unsigned NOT NULL default '0', > `aktivcode` varchar(15) NOT NULL default '', > `newsletter` tinyint(1) unsigned NOT NULL default '1', > `emailblock` tinyint(1) unsigned NOT NULL default '0', > PRIMARY KEY (`id`), > UNIQUE KEY `id` (`id`), > KEY `id_2` (`id`) > ) TYPE=MyISAM AUTO_INCREMENT=2 ; > > INSERT INTO `unregistered_users` VALUES (1, 'Steinhaug Webdesign', > 'firma@xxxxxxxxxxxxx', 1, 'kozexyimoc', 1, 0); > > > -- > Kim Steinhaug > ------------------------------------------------------------------------- > There are 10 types of people when it comes to binary numbers: > those who understand them, and those who don't. > ------------------------------------------------------------------------- > www.steinhaug.com - www.easywebshop.no - www.easycms.no www.webkitpro.com > ------------------------------------------------------------------------- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
