Forum Home
Press F1
 
Thread ID: 58453 2005-06-01 06:25:00 MYSQL joins help needed Morgenmuffel (187) Press F1
Post ID Timestamp Content User
360280 2005-06-01 06:25:00 Hi all

I have two tables

cart & cart_cookie

what I want to do is delete all values that are in cart (cartID)that dont have a corresponding value in cart_cookie(cartID),

now I know this involves some type of join, but i can't work out how to make it work everything i do seems to fail at the first hurdle

any help would be much appreciated 		Morgenmuffel (187)
360281 2005-06-01 09:16:00 mysql joins in action (www.wellho.net)

something like:

delete from cart using cart left join cart_cookie on ON cart_cookie.cartID = cart.cartID WHERE cart_cookie.cartID is NULL;

or you could use a nested query (not sure if that is valid MySQL syntax - did the job for me with Postgresql)

delete from cart where cartID not in (select cart.cartID from cart, cart_code where cart.cartID=cart_code.cartID) 		gibler (49)
360282 2005-06-01 09:18:00 Go here (www.melonfire.com) for a clear step-by-step guide on joins etc. Note that the link to go to the next page is small and right at the bottom (after the ad) :)

HTH

Mike. 		Mike (15)
360283 2005-06-01 10:07:00 thanks, I solved it using a nested query

I'm stumped by this though I have a field in the database called order_status which is null by default
What i want to do is select all records thatare over 30 days old and have a null order_status field,
now I can get the date bit to work, but I can't seem to select the order_status field

this is what i have tried

SELECT * FROM a_orders
WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) > order_date
AND order_status = ""

or

SELECT * FROM a_orders
WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) > order_date
AND order_status = "NULL"

I am getting to the point where I am going to check it by the length of the field 		Morgenmuffel (187)
360284 2005-06-01 10:13:00 In SQL (so I assume mySQL) you'd use something like:

order_status is null

rather than order_status = "NULL"

HTH

Mike. 		Mike (15)
360285 2005-06-01 10:19:00 Thanks Mike that solved it

I spent over an hour on it to, just assumed it would be = "null"

Cheers 		Morgenmuffel (187)
360286 2005-06-01 10:19:00 In other words:

SELECT * FROM a_orders
WHERE DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) > order_date
AND order_status is null

Mike. 		Mike (15)
360287 2005-06-01 10:20:00 Thanks Mike that solved it

I spent over an hour on it to, just assumed it would be = "null"

CheersBeen there, done that ;)

Mike. 		Mike (15)
360288 2005-06-01 10:32:00 Good to know I'm not the only one


last question of the night

is there a quick easy way to convert a timestamp to a human readable form

everything seems to use perl scripts to do the conversion 		Morgenmuffel (187)
360289 2005-06-01 11:02:00 Try this:

select date_format("<timestamp>", '%h:%i %p %a %d %M %y');

%a = Short Weekday
%b = Short Month Name
%d = Day of the month
%H = Hour
%I = Minute
%J = Day of the year
%m = 2-digit month
%M = Long month name
%p = AM/PM
%r = Time in 12 hour format
%S = Seconds
%T = Time in 24 hour format
%w = Day of the week
%W = Long weekday name
%Y = 4-digit year

You could also try time_format instead of date_format...

Let's hope that works for you :)

Mike. 		Mike (15)
1