Re: select from two tables

[Johan Hook]

Hi,
if you are using 4.x or later you can use:
(select * from helpdesk where month(helpdesk.hdcreatedate) = 3)
UNION ALL
(select * from archived where month(archived.hdcreatedate) = 3)
 LIMIT 0, 30;
/Johan

rmck wrote:

Hi 

I have two tables in the same Db :

table 1:
 helpdesk | CREATE TABLE `helpdesk` (
  `wcalledname` varchar(50) default NULL,
  `wcalledphone` varchar(50) default NULL,
  `wcalledemail` varchar(50) default NULL,
  `typeof` varchar(50) default NULL,
  `status` varchar(25) default NULL,
  `reasoncall` text,
  `reasoncalls` varchar(128) default NULL,
  `whorespond` varchar(128) default NULL,
  `datecallin` varchar(50) default NULL,
  `passedon` varchar(15) default NULL,
  `datetimetores` varchar(128) default NULL,
  `wresponded` varchar(128) default NULL,
  `resol` text,
  `dateclosed` varchar(25) default NULL,
  `hdcreatedate` datetime default NULL,
  `hdupdate` datetime default NULL,
  `hdid` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`hdid`)
) TYPE=MyISAM 

table 2:
archived | CREATE TABLE `archived` (
  `wcalledname` varchar(50) default NULL,
  `wcalledphone` varchar(50) default NULL,
  `wcalledemail` varchar(50) default NULL,
  `typeof` varchar(50) default NULL,
  `status` varchar(25) default NULL,
  `reasoncall` text,
  `reasoncalls` varchar(128) default NULL,
  `whorespond` varchar(128) default NULL,
  `datecallin` varchar(50) default NULL,
  `passedon` varchar(15) default NULL,
  `datetimetores` varchar(128) default NULL,
  `wresponded` varchar(128) default NULL,
  `resol` text,
  `dateclosed` varchar(25) default NULL,
  `hdcreatedate` datetime default NULL,
  `hdupdate` datetime default NULL,
  `hdid` int(10) NOT NULL default '0',
  PRIMARY KEY  (`hdid`)
) TYPE=MyISAM


Once a month I archive records from the helpdesk table to the archived one if the record is "closed", but sometimes a record is still open so some records for the last month are still in helpdesk while all the closed are in archived.

My issue is I want to know how to query both tables for a list of all records that are created for a certain month weather closed or not. 

This works fine for one table:
select * from `helpdesk` where month(hdcreatedate) = 3 order by `hdcreatedate` limit 0 
, 30;
I was trying to use this but it lists the records more than once: Help:

select * from helpdesk,archived where month(helpdesk.hdcreatedate) = 3 and month(archived.hdcreatedate) = 3 LIMIT 0, 30;

But it seems to list records over and over again. Please help if this type of query I'm trying to do can work. 

Thanks,
Rob



--
Johan Höök, Pythagoras Engineering Group
                - MailTo:[EMAIL PROTECTED]
                - http://www.pythagoras.se
Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden
Phone: +46 8 760 00 10 Fax: +46 8 761 22 77


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]