Weird Join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Weird Join

Post by AliasBDI »

I thought that a JOIN select would do what I want but I could not pull it off. I have two tables (var_month, var_year) that I need to combine into a list. I need it to get all of the var_year records (2004, 2005, ...) and combine each var_month with every year. So when I echo it, it looks like this:

January 2005
February 2005
March 2005
...
January 2006
Febrauary 2006
March 2006
...*you get the idea.

How would that query look?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT * FROM var_months, var_year

this will result in the product of the 2 tables.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

But how would I output that? And sort them in date order?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

if you are storing the months by value, instead of name: ORDER BY _year, _month

if you're storing by name, tt's a pain with the SQL, but not so much with php.

As for displaying, once you have it sorted, that's easy.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

Well, there is more to the problem... Here is my code for the query:

Code: Select all

<?
$thisYEAR = date("Y");
$thisMONTH = date("m");

mysql_select_db($database_econtrolsgc, $econtrolsgc);
$query_calendarLIST = "SELECT * FROM var_month, var_year WHERE var_year.active = 'Y' AND var_month.active = 'Y' AND var_year.name >= $thisYEAR AND var_month.singleDigit >= $thisMONTH ORDER BY var_year.name ASC, var_month.doubleDigit ASC"; ?>
Right now it will not show any January records in the list (because this month is passed January). The query WHERE reads "var_month.singleDigit >= $thisMONTH" which does that. I need it to run "var_month.singleDigit >= $thisMONTH" once, then all after that. In other words, I need it to get all months proceeding this month.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look into UNION syntax.
Post Reply