2 tables Join 2

[bdichiara]

I am terrible at writing SQL Statements. I never can remember which JOIN to use and what the syntax is...

I'm trying to get all items from table MARKET and only items with matching IDs and have a primary of 1 in MKT_PHOTOS

this is what I have, but I don't think it's right:

SELECT *, DATE_FORMAT(market.added,'%b. %e, %Y') AS disp_date FROM market LEFT JOIN mkt_photos ON market.itemid = mkt_photos.itemid AND mkt_photos.primary = '1' WHERE market.sold <> '1' AND market.visible <> '0' AND DATE_FORMAT(market.added,'%U') >= (DATE_FORMAT(market.added,'%U')-2) ORDER BY market.added DESC

_______________
_brian.

 

[TonyGroves]

That looks OK (though you could format it to make it more readable). Is it giving incorrect output? How does the output differ from what you want?

 

[r937]

this part is problematic --

DATE_FORMAT(market.added,'%U') >=
(DATE_FORMAT(market.added,'%U')-2)

ignoring for a moment that mysql should not allow you to subtract a number from a string, it looks like no matter which week it is, that week is always going to be greater than whatever it is minus two

r937.com | rudy.ca

 

[towerbase]

It would help to tell us why you don't think the SQL code is right. Otherwise we have to guess!

I think that

WEEK(market.added)

returns the week number as an integer and hence would be better than

DATE_FORMAT(market.added,'%U')

Although it is not mentioned in the brief requirements specification, is the intention of the SQL to list only those items that have been added in the last two weeks? In which case the WHERE clause should should be something like:

WHERE market.sold <> '1' 
 AND market.visible <> '0' 
 AND WEEK(market.added) >= ( WEEK(NOW()) - 2 )

Andrew
Hampshire, UK

 

[bdichiara]

man, i feel really stupid. after looking at rudy's comments, i realize how dumb that portion is. Thanks towerbase for the correction. i'm glad you can read minds better than i can explain my problem.

_______________
_brian.

 

[r937]

if you really want the last two weeks, then perhaps you should throw the year in as well, because in january you will be comparing week 1 to week 51

r937.com | rudy.ca

 

[bdichiara]

hmm. man, these dates get me every time. you should see how many condition statements i have in a calendar script i wrote.

So, MySQL isn't smart enough to know that week 1 minus 2 is week 51 of the previous year? I really don't have to have 2 weeks, all i'm trying to accomplish here is some recent items in that have been added. It could be 10 days, 2 weeks, 1 month, etc. What's the best way to do this? Or, how would i factor in the year?

_______________
_brian.

 

[r937]

What's the best way to do this?

begin by putting the date column on one side of the comparison operator, and all the junk on the other


why? because this is the only way to ensure that you don't get a table scan (slow), but instead, use an index on that date column (fast)

example: rows added in the last 15 days

where market.added >= date_sub(current_date, interval 15 day)

example: rows added between 1 week ago and 2 weeks ago

where market.added >= date_sub(current_date, interval 2 week)
and market.added < date_sub(current_date, interval 1 week)


r937.com | rudy.ca