Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I run this query from the command line, it continuously loops. I'm not sure why this is occurring. Could anyone explain this to me, and please offer a fix.

I will be using a query similar to this in a Python script and need to ensure I get the expected data.

SELECT `leads`.`status`, `leads_cstm`.`apptdateourtime_c`
FROM leads, leads_cstm
WHERE ((`leads`.`status` = 'Converted') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00'))
OR    ((`leads`.`status` = 'AppointmentScheduled') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00'));

I'm using MySQL, version 5.1.69

Table structure for leads:

 leads | CREATE TABLE `leads` (
  `id` char(36) NOT NULL,
  `tbl_id` int(11) DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `description` text,
  `deleted` tinyint(1) DEFAULT '0',
  `assigned_user_id` char(36) DEFAULT NULL,
  `salutation` varchar(255) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `do_not_call` tinyint(1) DEFAULT '0',
  `phone_home` varchar(100) DEFAULT NULL,
  `phone_mobile` varchar(100) DEFAULT NULL,
  `phone_work` varchar(10) DEFAULT NULL,
  `phone_other` varchar(100) DEFAULT NULL,
  `phone_fax` varchar(100) DEFAULT NULL,
  `primary_address_street` varchar(150) DEFAULT NULL,
  `primary_address_city` varchar(100) DEFAULT NULL,
  `primary_address_state` varchar(2) DEFAULT NULL,
  `primary_address_postalcode` varchar(20) DEFAULT NULL,
  `primary_address_country` varchar(255) DEFAULT NULL,
  `alt_address_street` varchar(150) DEFAULT NULL,
  `alt_address_city` varchar(100) DEFAULT NULL,
  `alt_address_state` varchar(100) DEFAULT NULL,
  `alt_address_postalcode` varchar(20) DEFAULT NULL,
  `alt_address_country` varchar(255) DEFAULT NULL,
  `assistant` varchar(75) DEFAULT NULL,
  `assistant_phone` varchar(100) DEFAULT NULL,
  `converted` tinyint(1) DEFAULT '0',
  `refered_by` varchar(100) DEFAULT NULL,
  `lead_source` varchar(100) DEFAULT NULL,
  `lead_source_description` text,
  `status` varchar(100) DEFAULT NULL,
  `status_description` text,
  `reports_to_id` char(36) DEFAULT NULL,
  `account_name` varchar(255) DEFAULT NULL,
  `account_description` text,
  `contact_id` char(36) DEFAULT NULL,
  `account_id` char(36) DEFAULT NULL,
  `opportunity_id` char(36) DEFAULT NULL,
  `opportunity_name` varchar(255) DEFAULT NULL,
  `opportunity_amount` varchar(50) DEFAULT NULL,
  `campaign_id` char(36) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `portal_name` varchar(255) DEFAULT NULL,
  `portal_app` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_lead_acct_name_first` (`account_name`,`deleted`),
  KEY `idx_lead_last_first` (`last_name`,`first_name`,`deleted`),
  KEY `idx_lead_del_stat` (`last_name`,`status`,`deleted`,`first_name`),
  KEY `idx_lead_opp_del` (`opportunity_id`,`deleted`),
  KEY `idx_leads_acct_del` (`account_id`,`deleted`),
  KEY `idx_del_user` (`deleted`,`assigned_user_id`),
  KEY `idx_lead_assigned` (`assigned_user_id`),
  KEY `idx_lead_contact` (`contact_id`),
  KEY `idx_reports_to` (`reports_to_id`),
  KEY `idx_lead_phone_work` (`phone_work`),
  KEY `idx_leads_id_del` (`id`,`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

leads_cstm is as follows:

| leads_cstm | CREATE TABLE `leads_cstm` (
  `id_c` char(36) NOT NULL,
  `buinessdescription_c` varchar(255) DEFAULT NULL,
  `businessstatus_c` varchar(100) DEFAULT NULL,
  `numberofemployees_c` int(5) DEFAULT NULL,
  `numberofyearsinbusiness_c` varchar(3) DEFAULT NULL,
  `felonyorbankruptcy_c` tinyint(1) DEFAULT '0',
  `encouragedbyagency_c` tinyint(1) DEFAULT '0',
  `annualrevenue_c` decimal(26,6) DEFAULT NULL,
  `currency_id` char(36) DEFAULT NULL,
  `productsservices_c` tinyint(1) DEFAULT '0',
  `investmoney_c` tinyint(1) DEFAULT '0',
  `fivestar_c` tinyint(1) DEFAULT '0',
  `socioeconomicstatus_c` text,
  `visamastercard_c` tinyint(1) DEFAULT '0',
  `sixreferences_c` tinyint(1) DEFAULT '0',
  `investigatedgsa_c` tinyint(1) DEFAULT '0',
  `faxedinfopack_c` tinyint(1) DEFAULT '0',
  `apptdateourtime_c` datetime DEFAULT NULL,
  `apptdatetheirtime_c` datetime DEFAULT NULL,
  `apptstatus_c` varchar(100) DEFAULT NULL,
  `topic_c` varchar(100) DEFAULT NULL,
  `secondaryphone_c` varchar(13) DEFAULT NULL,
  `extension_c` varchar(6) DEFAULT NULL,
  `timezome_c` varchar(100) DEFAULT NULL,
  `cleadsource_c` varchar(255) DEFAULT NULL,
  `timeissuedatebegan_c` date DEFAULT NULL,
  `timingissuelength_c` varchar(100) DEFAULT NULL,
  `oldsmarteam_c` varchar(100) DEFAULT NULL,
  `manual_enter_c` tinyint(1) DEFAULT '0',
  `suggestedcloser_c` varchar(100) DEFAULT NULL,
  `lead_type_c` varchar(100) DEFAULT 'gsalead',
  `qualifier_c` varchar(30) DEFAULT NULL,
  `secondarycontact_c` varchar(50) DEFAULT NULL,
  `siccode_c` varchar(8) DEFAULT NULL,
  `pamcoqualifiermain_c` varchar(255) DEFAULT NULL,
  `typeofsource_c` varchar(100) DEFAULT NULL,
  `l_printed_c` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id_c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
share|improve this question
2  
Can you post the table structure of both tables? At this point you are using a CROSS JOIN between the two tables which will generate a Cartesian result, you are not joining on a particular column, is this the intended behavior? Do you have a column that you can join the two tables on? –  bluefeet Jun 12 '13 at 15:05
    
It's unlikely this is "looping", but it could be hanging up –  JNK Jun 12 '13 at 15:05
    
@JNK I've run it, and I know there are only about 100 that should be returned between that date and time. It runs until I Control+C to interrupt it. –  novafluxx Jun 12 '13 at 15:08
    
@bluefeet I'm not sure what you mean by the table structure. I can tell you that status is varchar(100). The only column they both share is the id column. –  novafluxx Jun 12 '13 at 15:12
    
@novafluxx that doesn't mean there's a loop, it's probably as bluefeet says you are not using a join key and are creating a cartesian product. –  JNK Jun 12 '13 at 15:17

2 Answers 2

up vote 0 down vote accepted

You need to join the two tables together or you'll get a combination of the two that's equal to size of leads * size of leads_cstm.

It looks like table leads has an id field that appears to tie to id_c in the leads_cstm table. This is similar to how Goldmine worked too. Ugh.

Try this:

SELECT `leads`.`status`, `leads_cstm`.`apptdateourtime_c`
FROM leads, leads_cstm
WHERE
  leads.id = leads_cstm.id_c 
AND 
 (((`leads`.`status` = 'Converted') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00'))
OR    ((`leads`.`status` = 'AppointmentScheduled') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00')));

That's one way to do it... Not the best, though. The better way to be explicit in your join:

SELECT `leads`.`status`, `leads_cstm`.`apptdateourtime_c`
FROM leads inner join leads_cstm
    ON leads.id = leads_cstm.id_c
WHERE
 ((`leads`.`status` = 'Converted') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00'))
OR    ((`leads`.`status` = 'AppointmentScheduled') 
  AND  (`leads_cstm`.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' 
                                             AND '2013-06-05 00:00:00'));
share|improve this answer

Without seeing your table structure, I believe part of your problem is you are not currently joining the tables on a particular column. Your current query is generating a Cartesian result which means that if leads has 17 rows and leads_cstm has 10 rows the result could be up to 170 rows because each row in the first table will match to each row in the second table.

I am guessing that is not the intended behavior of the query. Based on your comment that the tables share an id column, it seems like you should be able to use something similar to the following:

SELECT l.`status`, lc.`apptdateourtime_c`
FROM leads l
INNER JOIN leads_cstm lc
  on l.id = lc.id
WHERE 
(
  l.`status` = 'Converted'
  AND  lc.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' AND '2013-06-05 00:00:00'
)
OR    
(
  l.`status` = 'AppointmentScheduled' 
  AND  lc.`apptdateourtime_c` BETWEEN '2013-06-04 00:00:00' AND '2013-06-05 00:00:00'
);

You will notice that I removed the leads,leads_ctsm and replaced it with an INNER JOIN on the id column. By using the id column to join the two tables, you will return only the ids that appear in both tables that match the filter in your WHERE clause.

share|improve this answer
    
Thank you for the assistance. Currently we use SugarCRM to export this data into CSV. I'm trying to find the query it runs so I can write a script to do it directly. It looks like I'm going to have to do some studying so I fully grasp this. –  novafluxx Jun 12 '13 at 15:22
    
I've updated my original question with the stucture of leads. I tried running this and it returned: Unknown column 'lc.id' in 'on clause' –  novafluxx Jun 12 '13 at 15:31
    
@novafluxx You will use the column in the leads_ctsm table that is referencing the leads table. How do you assign the leads_ctsm rows to the matching row in the leads table? –  bluefeet Jun 12 '13 at 15:53
    
Thanks for the help bluefeet, you got me on the right track. –  novafluxx Jun 14 '13 at 19:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.