Complex Select

[Mr_CHISOL]

I'm trying to do something like this in SQL:

// $list = the tableif ($list->exists('pub_year',$year))	 $list->select('pub_year',$year);else	 $list->select('pub_year',0);// The list now contains either all that has pub_year set to 0 or all that has pub_year set to $year//  Let's asume that they all have $yearif ($list->exists('pub_month',$month))	 $list->select('pub_month',$month);else	 $list->select('pub_month',0);// The list now contains all rows that has $year and either all that has pub_month set to 0 or all that has pub_month set to $month//  Let's asume that they all have $monthif ($list->exists('pub_day',$day))	 $list->select('pub_day',$day);else	 $list->select('pub_day',0);// The list now contains all rows that has $year and $month either all that has pub_day set to 0 or all that has pub_day set to $day

Do you get it?This is the SQL I've come up with, so far, but it doesn't work as I want

SELECT * FROM `jos_kacquotes` AS aWHERE (	(		EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =$year ) AND a.pub_year =$year	)        XOR        (		NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =$year ) AND a.pub_year =0	)) AND (	(		EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_month =$month ) AND a.pub_month =$month	)        XOR        (		NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_month =$month ) AND a.pub_month =0	)) AND (	(		EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_day =$day ) AND a.pub_day =$day	)        XOR        (		NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_day =$day ) AND a.pub_day =0	))

For example if $year is 2007, $month is 1 and $day is 25 and the table looks like this

| pub_year | pub_month | pub_day |+----------------------------------------------+ |	 2007	 |	 0		|	 21	|+----------------------------------------------+ |	 2007	 |	 0		|	 0	  |+----------------------------------------------+

It will return nothing instead of the first row (there are more problems...)Any ideas on how to solve this?

[Mr_CHISOL]

Now I have this SQL

SELECT * FROM (	SELECT * FROM (		SELECT * FROM jos_kacquotes AS a WHERE (				EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )				AND pub_year =2006			) XOR (				NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )				AND pub_year =0			)	) AS b WHERE (		(			EXISTS ( SELECT *  FROM (					SELECT * FROM jos_kacquotes AS aa WHERE (							EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )							AND pub_year =2006						) XOR (							NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )							AND pub_year =0						)				) AS y WHERE pub_month =1 )			AND b.pub_month =1		) XOR (			NOT EXISTS ( SELECT *  FROM (					SELECT * FROM jos_kacquotes AS aaa WHERE (							EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )							AND pub_year =2006						) XOR (							NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )							AND pub_year =0						)				) AS x WHERE pub_month =1 )			AND b.pub_month =0		)	)) 

That works fine for year and month but to have it work with day I need to have the above code twice so I thought that I could create a view, but when I'm trying to do so:

CREATE VIEW YnM AS SELECT * FROM (	SELECT * FROM (		SELECT * FROM jos_kacquotes AS a WHERE (				EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )				AND pub_year =2006			) XOR (				NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )				AND pub_year =0			)	) AS b WHERE (		(			EXISTS ( SELECT *  FROM (					SELECT * FROM jos_kacquotes AS aa WHERE (							EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )							AND pub_year =2006						) XOR (							NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )							AND pub_year =0						)				) AS y WHERE pub_month =1 )			AND b.pub_month =1		) XOR (			NOT EXISTS ( SELECT *  FROM (					SELECT * FROM jos_kacquotes AS aaa WHERE (							EXISTS ( SELECT *  FROM `jos_kacquotes`  WHERE pub_year =2006 )							AND pub_year =2006						) XOR (							NOT EXISTS ( SELECT *  FROM `jos_kacquotes` WHERE pub_year =2006 )							AND pub_year =0						)				) AS x WHERE pub_month =1 )			AND b.pub_month =0		)	))

It doesn't work (I get an error near VIEW), what's wrong?I''m using phpMyAdmin to test my SQL...

[jesh]

Rather than using all the SELECTs, can you simplify your query as something like this:

SELECT * FROM jos_kacquotes aWHERE (a.pub_year = 0 OR a.pub_year = $year) AND (a.pub_month = 0 OR a.pub_month = $month) AND (a.pub_day = 0 OR a.pub_day = $day)

[Mr_CHISOL]

Rather than using all the SELECTs, can you simplify your query as something like this:

SELECT * FROM jos_kacquotes aWHERE (a.pub_year = 0 OR a.pub_year = $year) AND (a.pub_month = 0 OR a.pub_month = $month) AND (a.pub_day = 0 OR a.pub_day = $day)

No I can't! First I wanna select all that matches for example pub_year with $year and if there is none, I just wanna select all that as pub_year set to 0 (not both on the same time) this is where it becomes complex, I'm getting a hang of it now, but it's a BIG query...

[jesh]

Hmm, I see. Have you tried table variables (T-SQL) or temporary tables (MySQL) in a stored procedure?

DECLARE @Temp TABLE(	id int,	year int,	month int,	day int)-- first, try to enter in all the quotes that have a yearINSERT INTO @TempSELECT * FROM jos_kacquotes aWHERE a.sub_year = @Year-- let's see how many we addedDECLARE @Count intSET @Count = (SELECT COUNT(id) FROM @Temp);IF @Count = 0BEGIN	-- didn't add any, so let's add quotes that have year = 0 instead	INSERT INTO @Temp	SELECT * FROM jos_kacquotes a	WHERE a.sub_year = 0END-- let's see, of the quotes we have, how many have month = @MonthSET @Count = (SELECT * FROM @Temp WHERE sub_month = @Month)IF @Count <> 0BEGIN	-- we found some, let's delete any that aren't from that month	DELETE FROM @Temp WHERE sub_month <> @MonthENDELSEBEGIN	-- couldn't find any, let's delete any that don't have month = 0	DELETE FROM @Temp WHERE sub_month <> 0ELSE-- repeat step for month that we just did to get the ones that either have a day-- set or ones that have 0 for day if there aren't any that match our specific day-- once we have narrowed down the quotes in our temporary table, let's return the resultsSELECT * FROM @Temp

In MySQL, rather than a table variable, you'd have to use a temporary table like so:

CREATE TEMPORARY TABLE Temp (	id int,	year int,	month int,	day int)

[Mr_CHISOL]

I can't get it to work..I'm just trynig with this, for now:

CREATE TEMPORARY TABLE Temp(      	 id   	int,	 categoryid	int,	 quoted  	text,	 quote  	text,	 pub_year  	int,	 pub_month  	int,	 pub_day  	int,	 pub_week  	int,	 email  	text,	 showcount  	int)INSERT INTO Temp(SELECT id, categoryid,	 quoted, quote,	 pub_year, pub_month, pub_day,	 pub_week, email,	 showcount FROM jos_kacquotesWHERE pub_year = 2007)

But I get an error near INSERT... What's wrong?This is beginning to get on my nervs..Btw, I'm using MySQL 4.1

[packrat]

I've been through quite an ordeal trying to get registered in order to drop you this chunk of sql, ... sure hope it works. Much thanks to the admins for getting me going. Try plugging your variable names into the sql below. I haven't tested this solution, if its buggy I might be able to help. if you get an error in the 'where' try changing the '=' to 'IN'. let me know how it pans out. --answer to complex select

SELECT [year], [month], [day]FROM [table] WHERE [table].[year] =  (  SELECT TOP 1 [year] FROM [table] WHERE [year]=0 OR [year]=@year  GROUP BY [year] HAVING COUNT([year]) > 0 ORDER BY [year] DESC ) AND [table].[month] = (  SELECT TOP 1 [month] FROM [table] WHERE [month]=0 OR [month]=@month  GROUP BY [month] HAVING COUNT([month]) > 0 ORDER BY [month] DESC ) AND [table].[day] = (  SELECT TOP 1 [day] FROM [table] WHERE [day]=0 OR [day]=@day  GROUP BY [day] HAVING COUNT([day]) > 0 ORDER BY [day] DESC )

//PackRat

[Mr_CHISOL]

Thanks packrat!One syntax-error MySQL don't support TOP so I just used LIMIT instead. But there's on problem with that SQL-query; If one of the parts that gets month and day doesn't find a match it returns nothing (It should return one where the year is 0...). But that's not a big problemThis is the code I use now..

SET @year = 2007;SET @month = 1;SET @day = 26;SET @week = 4;SELECT *FROM jos_kacquotes AS a WHERE a.pub_year =	(		SELECT pub_year		FROM jos_kacquotes AS y WHERE y.pub_year=0 OR y.pub_year=@year		GROUP BY y.pub_year HAVING COUNT(y.pub_year) > 0		ORDER BY y.pub_year DESC LIMIT 1	) AND a.pub_month = (		SELECT pub_month		FROM jos_kacquotes AS m WHERE m.pub_month=0 OR m.pub_month=@month		GROUP BY m.pub_month HAVING COUNT(m.pub_month) > 0		ORDER BY m.pub_month DESC LIMIT 1	) AND a.pub_day = (		SELECT pub_day		FROM jos_kacquotes AS d WHERE d.pub_day=0 OR d.pub_day=@day		GROUP BY d.pub_day HAVING COUNT(d.pub_day) > 0		ORDER BY d.pub_day DESC LIMIT 1	) OR a.pub_week = @week

[packrat]

yea, I suppose that my example would return nothing if it didn't get a hit on one of the specified dateparts, ns what you'd want to return if you don't get the specified datepart and there is no '0' to fall back on... If you need help with that part I'll try to pitch in.Subqueries can really help segment the logic so its easier to understand the steps.Temporary tables can be a god-send as well, especially if you have some complex sub query that you have to use in several places at once (such as running a variety of aggregates in a reporting scenario)cheers;