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, '[EMAIL PROTECTED]', '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', '', '[EMAIL PROTECTED]', '',
> '', 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',
> '[EMAIL PROTECTED]', 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
