Right Join


Code: $sql="SELECT MAX(mr.messageId) AS maxMessageId, mr.threadId FROM messages_recipients AS mr RIGHT JOIN thread_recipients AS tr ON tr.threadId=mr.threadId WHERE mr.recipientUserId='2' GROUP BY mr.threadId";

If the above SELECT should find a row in message_recipients with a threadId of 1
and thread_recipients has multiple rows with threadId=1..
then because I have a RIGHT JOIN,
I would expect it to return as many rows as the number of rows in thread_recipients where threadId=1

It is however only returning one row regardless...
Can you tell me why it is doing this...
Kind regards J
Posted On: Monday 29th of October 2012 06:16:04 AM Total Views:  63
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is this query in the right format?

I pieced this query together using some stuff I found on the web. I'm afraid to run it because once an update is done it can't be undone. Does this look good to you
VIEWS ON THIS POST

68

Posted on:

Sunday 21st October 2012
View Replies!

is this statement right ?

Code: $q = $dbh->query("SELECT dish,price FROM meals WHERE meal LIKE '" . $_POST['meal'] ."'"); i have looked for lots of articles,finding in mysql,behind LIKE there are two institutions,some use '',and some use " ",i don't know which is right two: in the above it uses " behind LIKE,i think it's wrong,and the right is LIKE ' $_POST['meal'] '"); am i right
VIEWS ON THIS POST

75

Posted on:

Sunday 21st October 2012
View Replies!

MySQL slow log CAN'T be right

I have this entry: # Time: 080604 17:04:49 # User@Host: [] @ localhost [] # Query_time: 61 Lock_time: 0 Rows_sent: 118 Rows_examined: 62562 select o.* from s01_Orders o, s01_sw_downloaded d where o.id = d.orderid and d.siteid = 'a8fbd8c6' and (d.batchid o.batch_id or d.verifycode 0); According to the entry, that query took 61 seconds to process (and it did). My question is, what on earth could cause this to be such a slow query For some odd reason, the query says that it is examining 62,000 rows. Here's the explain for that query: Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE d ALL orderid,siteid,verifycode NULL NULL NULL 23508 Using where 1 SIMPLE o ref s01_Orders_1 s01_Orders_1 5 .d.orderid 1 Using where I have tried adding lots of different indexes to this table, but I can't seem to get it to use an index either. If I run this exact same query, it only takes me 1 second to run it... I don't have control over the process that is using this SQL, but if there is a way for me to optimize the query, I would be thrilled. If there is a fundamental change that needs to be added to the query, I might be able to get them to make that change for me. Can anyone help out Formatted query: Code: SELECT o.* FROM s01_Orders o , s01_sw_downloaded d WHERE o.id = d.orderid AND d.siteid = 'a8fbd8c6' AND (d.batchid o.batch_id OR d.verifycode 0)
VIEWS ON THIS POST

85

Posted on:

Sunday 21st October 2012
View Replies!

Mixing order level & product level totals. Am I doing this right?

I have an orders table that contains the delivery charge for an order, and am ordered_products table that contains the sale price of the products. If I want to get totals, by date, of the delivery charge + product cost, one could do this: Code MySQL: SELECT date, SUM(o.delivery) + SUM(p.salePrice) as total FROM orders o JOIN ordered_products p ON o.orderID=p.orderID GROUP BY o.date But, an order with more than 1 product would have its delivery charge added for each product, am I right So is this a better approach Code MySQL: SELECT date, SUM(delivery) + SUM(salePrice) as total FROM ( SELECT date, o.orderID, o.delivery, SUM(p.salePrice) as salePrice FROM orders o JOIN ordered_products p ON o.orderID=p.orderID GROUP BY o.orderID ) as orders GROUP BY date
VIEWS ON THIS POST

98

Posted on:

Sunday 21st October 2012
View Replies!

DATE_SUB() is not working right for me.

My `date` column is in date/time format 2007-12-14 19:55:00 When I try to select only the records added in the last 5 hours, I get records from more than 24 hours ago. I know I am EST zone and the server is PST zone, but something else is wrong. This is my WHERE clause: WHERE `date` > DATE_SUB(CURRENT_DATE, INTERVAL 5 HOUR) Sam ;
VIEWS ON THIS POST

256

Posted on:

Sunday 21st October 2012
View Replies!

Timestamp not being updated - is my UPDATE command right?

Can anyone tell me if there's something wrong with my UPDATE command below. PHP Code: mysql_query("UPDATEprv_msgSETdate_read=now()ANDconfirm='1'WHEREid='$userid'")ordie(mysql_error()); I'm not getting an error message, but a time stamp is not being inserted into the date_read column
VIEWS ON THIS POST

95

Posted on:

Monday 22nd October 2012
View Replies!

Client Style data - join not quite right

Hi all, Happy New Year and thanks for taking the time to read my post. Here's my entity relationship diagram for starters: I'm creating an output of style category, then the style elements that appear underneath that category. e.g. styleCategoryName | styleElementName | styleElementValue boardstyle | color | #ffffff boardstyle | name | test boardstyle | e-mail | test@test.com I've got this to display as i want, with a left outer join on styleElement to get the related elements under each category. My issue is, i want to expand this so that the style value can be changed per client, as an extra column, so i can check for this extra column and use this value instead of the original, per client. At present, the query i'm using gets custom data in the extra column, but it's not limiting down to just that clientID, it's bringing back custom for all clientIDs. Any help or guidance on where my join has gone wrong would be much appreciated. Code: SELECT styleCategoryName, styleElement.styleName, styleElement.styleValue, customStyleElement.styleValue FROM styleCategory left outer join styleElement on styleCategory.styleCategoryID = styleElement.styleCategoryID left outer join customStyleElement on styleElement.styleElementID = customStyleElement.styleElementID left outer join client on client.clientID = customStyleElement.clientID AND client.clientID = 3 ORDER BY styleCategoryName, styleElement.styleName Many thanks. Martin.
VIEWS ON THIS POST

85

Posted on:

Wednesday 24th October 2012
View Replies!

JOIN - works it right?

, I solve a problem with the statement from two tables. The data in tables looking something like that: T1 id: 2 name: Aron ... T2 id: 1 item: 2 size: L ... id: 2 item: 2 size: XXXL ... id: 3 item: 2 size: XXL ... id: 4 item: 2 size: XL ... And my SQL query: Code: SELECT ... FROM t1 LEFT JOIN t2 ON t1.id=t2.item I thougt that the above SQL query returns only 1 row (with the row with ID=1 from table T2), but he returns all rows from T2. Can you help my, please, what is wrong I thought if I use RIGHT JOIN, that the result will be return 4 rows, but if I use LEFT JOIN, returns also 4 rows (but it should only one, or not). Well what I can to get only the one row from table T2 I'm a little bit confused... Thank you very match, Manny
VIEWS ON THIS POST

65

Posted on:

Wednesday 24th October 2012
View Replies!

Am I doing this right?

Hi-- I'm working on shopping cart where shoppers can view order histories and place an older order again. What I need to do is check the items in the order history against the current list of available products to make sure no unavailable products are ordered again. My query: Code: SELECT order_items.reorder_num, order_items.product, order_items.size, order_items.weight, order_items.quantity, products.reorder_num, products.product, products.size, products.weight, products.price FROM order_items LEFT OUTER JOIN products ON order_items.prod_key=products.pkey WHERE order_items.prod_key=2 The results are only the rows of currently available products. That's perfect, but I have so little test data I need to make sure my logic (and query) is accurate. Also, I'm wondering if I'm using the WHERE clause correctly here. If you couldn't guess, my two tables are order_items (order history) and products (available products).
VIEWS ON THIS POST

97

Posted on:

Wednesday 24th October 2012
View Replies!

SQL Join, is this the right way?

, I'm making a forum and ran up to the following problem: There are 3 mysql tables involved -> members(mem_id, username) topics(topic_id, title, created_at, created_by, type) threads(thread_id, title) The field 'created_by' links to an id from the members table. I got the following query: Code: $sql = 'SELECT t.topic_id, t.title, t.created_at, m.username FROM topics t, members m WHERE t.in_thread='.$thread_id.' AND m.mem_id = t.created_by ORDER BY t.type DESC, t.created_at DESC'; It should select all topics that are in the specified thread ($thread_id) and select the name of the member who created the topics. This works correctly except for the fact that when created_by links to a member id that doesnt exist (anymore), it wont return the entire topic record. I still want to return the topic data even when the member-id doesnt exist. How can i do this
VIEWS ON THIS POST

86

Posted on:

Wednesday 24th October 2012
View Replies!

Query not returning right dates

this is the select query... Code: SELECT * FROM ml_games WHERE gamedatetime >= 2007-05-01 AND gamedatetime < 2007-06-01 ORDER BY gamedatetime DESC I'm trying to bassicaly return all rows that happened in the month 5/2007 (may 2007). if i take out the Code: gamedatetime < 2007-06-01 it returns columns after that date, but if theres entries on months coming after may it won't be returning the rows i want. There is no mysql error, just it seems to be some sort of problem with the logic with "AND" or something any ideas
VIEWS ON THIS POST

166

Posted on:

Wednesday 24th October 2012
View Replies!

Need a point in the right direction

Hi all i am trying to create a sort of module include system where it finds modules in the database and then, checks if they are in the modules table and include them, but it always says that the module isn't in the array... Basically thise code checks the module to use first, and then secondly it searches through a made array of modules to make so it is there, then check if it exists, then include it. As you can see here - http://www.milztech.net/area51/ it includes it as it says "Start module" which is the defualt module yet it says it's not in the array (it says it 3 times as i put 3 modules in my modules table). PHP Code: classmain { \t\tfunctionmodule_check() \t\t{ \t\t\t\t//Makesurewecanuse \t\t\t\tglobal$database; \t\t\t\t//Checkifthereisamodulesettouse \t\t\t\tif(isset($_GET['mod'])) \t\t\t\t{ \t\t\t\t\t\t$module=$_GET['mod']; \t\t\t\t} \t\t\t\t//Ifnotthenusethedefualtmodule \t\t\t\telse \t\t\t\t{ \t\t\t\t\t\t$get_defualt_module=$database->sql_query("SELECT`name`FROM`main_modules`WHERE`is_defualt`='1'"); \t\t\t\t\t\t$module=$database->sql_fetch($get_defualt_module); \t\t\t\t} \t\t\t\treturn$module['name']; \t\t} \t\tfunctionmodule_includes() \t\t{ \t\t\t\t//Makesurewecanuse \t\t\t\tglobal$database; \t\t\t\t//Getthenon-adminmodulesfromthedatabase \t\t\t\t$get_modules=$database->sql_query("SELECT`name`FROM`main_modules`"); \t\t\t\t//Makesuretheywillbeanarray \t\t\t\t$modules=array(); \t\t\t\t \t\t\t\twhile($modules=$database->sql_fetch($get_modules)) \t\t\t\t{ \t\t\t\t\t\tif(in_array($this->module_check(),$modules)) \t\t\t\t\t\t{ \t\t\t\t\t\t\t\tif(file_exists("./modules/".$this->module_check().".mod.php")) \t\t\t\t\t\t\t\t{ \t\t\t\t\t\t\t\t\t\tinclude("./modules/".$this->module_check().".mod.php"); \t\t\t\t\t\t\t\t} \t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\telse \t\t\t\t\t\t\t\t{ \t\t\t\t\t\t\t\t\t\techo"Moduledoesnotexist(thisisaplaceholder)"; \t\t\t\t\t\t\t\t} \t\t\t\t\t\t} \t\t\t\t \t\t\t\t\t\telse \t\t\t\t\t\t{ \t\t\t\t\t\t\t\techo"Modulenotinarray(thisisaplaceholder)"; \t\t\t\t\t\t} \t\t\t\t} \t\t} \t\tfunctioninitialize_forum() \t\t{ \t\t\t\t$this->module_includes(); \t\t} }
VIEWS ON THIS POST

61

Posted on:

Wednesday 24th October 2012
View Replies!

Query matching join to the right table

I'm trying to match comments to resources in different tables. Each comment has a resource_type and resource_id The resource type is the name of table that the resource is part of, and the id is the id from that table. So it might look like: Comments resource_type | resource_id | content ------------------------------------------- photos | 21 | blah blah blah documents |63 | blah blah blah photos id | title documents id | title Is it possible to write a query that would select the title from the right resource table for each comment
VIEWS ON THIS POST

144

Posted on:

Thursday 25th October 2012
View Replies!

Problem with MAX and selecting the right rows...

Hi all, I am having a really weird issue with the MAX function. Any insights would be greatly appreciated. I have a table with 3 columns: id, group_id, and name id and name are unique, but group_id isn't. So, each group_id can contain multiple id/name combos My goal is to return a list of highest value ids in each group, so the query I have is: SELECT MAX(id), name FROM table GROUP BY group_id ORDER BY id DESC For some reason... all the id's I got are right, but the returned names have absolutely no correlation with id's what-so-ever.... Is there anyway I can return the right rows\t It feels like right now, a random name in that group is being randomly pulled... I know I can do a self-join, but I am assuming that's much less efficient Any tip would be greatly appreciated,
VIEWS ON THIS POST

204

Posted on:

Thursday 25th October 2012
View Replies!

All my rights have been stripped!!!

This morning i tried to access my wordpress blog. Only to find this: CLICK The config file is fine and has never been changed since install. I went into SQL lite manager 2005 on the server and the database was still intact but when i went to manage users: the username wordpress used had all its rights stripped (select, insert, delete, update etc. etc.)... It was working perfectly only 10 hours ago... What could have happened! hackers i doubt it, but its so weird because nobody else uses this server. I ran windows update on it a couple of weeks ago...could this have affected anything
VIEWS ON THIS POST

57

Posted on:

Thursday 25th October 2012
View Replies!

right design for database

hi i attached my database in jpg picture if any body please can look at it and till me if it is normalized properly this is my first database i ever done, the data base is going to be used for a web site that will allow you to list your home for sale ,how ever the listing will be for free, donation will be accepted that's why there is product table your comment will be appreciated please not that the attached database will be implemented in mysql , i would like to say
VIEWS ON THIS POST

70

Posted on:

Thursday 25th October 2012
View Replies!

Condition on the right table in LEFT OUTER JOIN eliminate results on let table

I just need to confirm this. In the query below AND packages.status = 'active' removes any room that does not have an active package. This means it doesn't really matter if this is LEFT OUTER JOIN or INNER JOIN. Code: SELECT hotelrooms.room_id AS RID, hotelrooms.roomname, hotelrooms.roomdescription, packages.id AS PID, packages.description AS PD, packages.room_id AS room_id FROM hotelrooms LEFT OUTER JOIN packages ON packages.room_id = hotelrooms.room_id WHERE hotelrooms.hotel_id = 4 AND hotelrooms.status = 'active' AND packages.status = 'active' ORDER BY hotelrooms.room_id What would be the correct way to show all rooms and active packages (in case exists) without eliminating rooms with no active package
VIEWS ON THIS POST

88

Posted on:

Monday 29th October 2012
View Replies!

Going INSANE. How to right this efficient hit counter

I am literally going insane, I have been asking coders I know how to do this and all the suggestios seem poor and rely on CRON jobs. For each link on my site, I want to record for each day, how many clicks I get. Since I do not need any other information such as IP, browser etc, it seems much better from a performance point of view to have the view count on that days date incremented each time. So, regardless of the number of hits, there is only 1 record per day, not one new record per hit. Date | Hits 20-12-12 | 102 20-12-13 | 23 20-12-14 | 35 20-12-15 | 47 If today is the 15th and I visit the site, it will increment "47" to "48". So, it seems easy enough. However, the issue is, what happens on the 16th December using the example above. There is no record for that. I dont want to be running a Cron job each day to add in the next days date so how can I do it. Is this no function to just increment todays date and create the line if it doesnt already exist. I would REALLY appreciate some help on this, ive spent all day on it. James
VIEWS ON THIS POST

68

Posted on:

Monday 29th October 2012
View Replies!

JOIN to return one lefttable row, and multiple right table rows.

I'm sure this is something silly, but for the life of me, I can't figure out how to leverage a join to return only one row of the left table, but display as many rows from the right table that match it's key Ex: Code: table = fruit fruitID | fruitName 1 Orange 2 Grape 3 Apple table = fruitvariations fruitColorID | fruitID | fruitColor 1 1 Orange 2 2 Purple 3 2 Green 4 3 Red 5 3 Green 6 3 Golden So if I ran a SELECT * FROM fruit [LEFT JOIN] fruitvariations ON (fruit.fruitID=fruitvariations.fruitID) WHERE fruidID = 3; Code: I would like it to return fruitID | fruitName | fruitColorID | fruitID | fruitColor 3 Apple 4 3 Red 5 3 Green 6 3 Golden A left join just returns as many left rows as it matches in the right table (as designed), and group by fruitID just returns one row
VIEWS ON THIS POST

225

Posted on:

Monday 29th October 2012
View Replies!

Getting connected (simillar) data with lef/right fields

Im having two tables: - PRODUCTS (id, Name) - SEEALSO (id, prodLeft, prodRight) SEEALSO defines which PRODUCTS are related together and are represented as binded fileds "prodLeft"-"prodRight". For Example: PRODUCTS: 1 Desk 2 Table 3 Chair 4 Doors 5 Tree 6 Flower SEEALSO 1 1 2 Desk-Table 2 2 3 Table-Chair 3 3 4 Chair - Doors 4 5 6 Tree - Flower From that we can see binding of Desk-Table-Chair-Doors and Tree-Flower. I would now want to write SQL statement where i could specified PRODUCT name (e.g. Chair) and i would get result of binded fields that are connected with it (e.g. Chair: Desk-Table-Chair-Doors). From this point on i would like to know if this is even possible for my data presentation concept in SEEALSO and if it is if you could help me solve my problem. tnx!
VIEWS ON THIS POST

64

Posted on:

Monday 29th October 2012
View Replies!