Join in the same table?

Question

I have to perform a SELECT operation in the DB. Since I want to narrow down the search results, I was wondering if I should use nested SELECTs or use a JOIN operation?

I want to perform a dual search on the table. I unsuccessfully tried this:

$receive_listings = mysql_query('SELECT * 
                                   from show_detail 
                                  WHERE ch_id="0140.zee.in" IN ('SELECT * 
                                                                   FROM show_detail 
                                                                  WHERE `date_column` BETWEEN `2010-08-17` AND `2010-08-18`')'
                                ) or die("ERROR: $receive_by_show_id.".mysql_error());

throws an error:

Parse error: syntax error, unexpected T_VARIABLE in /Applications/XAMPP/xamppfiles/htdocs/play/browse-channels-shows.php on line 17

I was wondering if a JOIN operation would do the trick more efficiently?

Answer 1

$receive_listings = mysql_query('SELECT * from show_detail WHERE ch_id="0140.zee.in" IN (SELECT * FROM show_detail WHERE `date_column` BETWEEN `2010-08-17` AND `2010-08-18`)') or die("ERROR: $receive_by_show_id.".mysql_error()); 

I think it might need to be like this.

Answer 2

Since others have already solved your parse error, you're going to end up with a second error because your query isn't correct.

I'm assuming you're trying to get items between your two dates and match your ch_id:

SELECT * from show_detail WHERE ch_id="0140.zee.in" AND (`date_column` BETWEEN `2010-08-17` AND `2010-08-18`')

Answer 3

All the answers in here failed to bring up that Double quotes cannot be used to surround the value. You must use single quotes.

$receive_listings = mysql_query("SELECT * 
        from show_detail 
        WHERE ch_id='0140.zee.in' IN (SELECT * 
              FROM show_detail 
              WHERE `date_column` BETWEEN '2010-08-17' AND '2010-08-18')") or die("ERROR: $receive_by_show_id. ".mysql_error());

Should be a correct version for MySQL and PHP.

Answer 4

Here's your query rewritten to be a bit more readable.

$query <<<EOL
SELECT *
FROM show_detail
WHERE ch_id = "0140.zee.in" IN (
    SELECT *
    FROM show_detail
    WHERE `date_column` BETWEEN '2010-08-17' AND '2010-0818'
);
EOL;

You've obviously got multiple columns in the show_detail table, but you're selecting them all in the subquery. This is illegal syntax. A subquery must return at most 1 value (if it's being used in an equality test (... WHERE field = (SELECT someval FROM...))), or one column, if it's used in an IN clause. Your query, if everything else was correct, would still return ERROR 1241 (21000): Operand should contain 1 column(s).

Unless you've got something totally crazy in mind, you don't need a subquery to do this, you just need an AND clause, as CFreak says in his answer.