[deepman007]

I have two Tables set up in database and I want to select only email field from each table. How do you write the query for that? I know select one is "SELECT user_email FROM tbl_subscribe"

Table 1

CREATE TABLE `tbl_subscribe` (
  `subscribe_ID` int(11) NOT NULL auto_increment,
  `user_email` varchar(50) default NULL,
  PRIMARY KEY (`subscribe_ID`)
  );
  

Table 2

CREATE TABLE `tbl_customers` (
  `cst_ID` varchar(50) NOT NULL default '',
  `cst_Type_ID` int(11) default NULL,
  `cst_Email` varchar(50) default NULL,
  `cst_Username` varchar(20) default NULL,
  `cst_Password` varchar(20) default NULL,
  PRIMARY KEY  (`cst_ID`),
  UNIQUE KEY `cst_Email` (`cst_Email`),
  UNIQUE KEY `cst_Username` (`cst_Username`),
  KEY `tbl_Cust_Typetbl_Customers` (`cst_Type_ID`)
);

[rgfirefly24]

Do a search on Joins. You have options of Left Outer Inner joins as well as the basic one which i think will work perfect for you

SELECT p.cst_Email, k.user_email FROM tbl_customers as p, tbl_subscribe as k;

This post has been edited by rgfirefly24: 23 June 2008 - 03:14 PM

[Trogdor]

The problem in this case is that there is no obvious relation between the tables, making a join impractical.

Could it be that you are searching for those email adresses that are present in both tables?

[sphildreth]

Table 1

CREATE TABLE `tbl_subscribe` (
  `subscribe_ID` int(11) NOT NULL auto_increment,
  `cst_pkID` int NOT NULL,
  PRIMARY KEY (`subscribe_ID`)
  );
  

Table 2

CREATE TABLE `tbl_customers` (
  `cst_pkID` int IDENTITY(1,1) NOT NULL,
  `cst_ID` varchar(50) NOT NULL default '',
  `cst_Type_ID` int(11) default NULL,
  `cst_Email` varchar(50) default NULL,
  `cst_Username` varchar(20) default NULL,
  `cst_Password` varchar(20) default NULL,
  PRIMARY KEY  (`cst_ID`),
  UNIQUE KEY `cst_Email` (`cst_Email`),
  UNIQUE KEY `cst_Username` (`cst_Username`),
  KEY `tbl_Cust_Typetbl_Customers` (`cst_Type_ID`)
);

Query

SELECT c.*
FROM tbl_subscribe sc
LEFT JOIN tbl_customers c ON (c.cst_pkID = sc.cst_pkID)
WHERE (sc.subscribe_ID = 1)