JOIN problem


It's probably something stupid - but I've done more complicated joins than this in the past and I've never seen this problem.

The error is:
1054: Unknown column 'mp.product_id' in 'on clause'
Code: SELECT mp.product_id, mp.title, mp.description, mp.price, mp.image, mp.trade_id, m.merchant_id, m.name, p.title AS platform, ag.merchant_product_id FROM merchant_products AS mp, platforms AS p, merchant AS m LEFT JOIN assigned_games AS ag ON (mp.product_id = ag.merchant_product_id) WHERE mp.merchant_id = m.merchant_id AND mp.platform_id = p.id AND mp.platform_id = 9 AND mp.title LIKE '%Orange Box%' ORDER BY m.merchant_id ASC, mp.title ASC
Posted On: Monday 29th of October 2012 09:12:52 PM Total Views:  411
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Problem using sum() function and join

hi, i am trying to fetch data using sum() function and join as data is to fetch from two tables but where i use sum() function it gives me wrong values. my query is select comearn.mdid, sum(comearn.ecom), sum(pcom.pcom) from comearn join pcom on comearn.mdid = pcom.stid group by comearn.mdid
VIEWS ON THIS POST

150

Posted on:

Monday 29th October 2012
View Replies!

How to rewrite subquery as join

My goal is to have output like this: First, Last, Bachelor of Arts (BA) abc, , BA Sub 1 ghi First, Last, Bachelor of Arts (BA) abc, , BA Sub 2 jkl First, Last, Master of Arts (MA) def, , I have this query: Code: /* QUERY */ SELECT pe1.firstname, pe1.lastname, p1.programname pn1, sp1.graduationdate gd1, (SELECT p2.programname pn2 FROM student_program sp2 LEFT OUTER JOIN program p2 ON sp2.programid = p2.programid WHERE p2.subprogramof = p1.programid AND sp2.personid = pe1.personid ) sb1 FROM person pe1 LEFT OUTER JOIN student_program sp1 ON pe1.personid = sp1.personid LEFT OUTER JOIN program p1 ON sp1.programid = p1.programid WHERE pe1.personid = 1 AND p1.subprogramof is null This query give me this error: Subquery returns more than 1 row. I know I need to rewrite it according to this: http:// dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html I've tried many times, and all I come up with is failure. See here for the db structure if needed: http:// pastie.org/626754
VIEWS ON THIS POST

372

Posted on:

Monday 29th October 2012
View Replies!

Trying to get mysql join query to work

hello, What I am trying to do is connect to a class roster table and grab a specific classes roster and then connect to a staffing table and pull who is not listed in the class roster table which would ultimatly give me a list of who needs to be scheduled for a specific training. My issue is my join query does not work here is my code PHP Code: $croster=mysql_query("SELECTClass_roster.FirstName,Class_roster.LastName,Employees.FirstName,Employees.LastName,Employees.EmployeeStatusIDfromClass_roster,EmployeeswhereClass_roster.ClassID=6ANDClass_roster.FirstName=!Employees.FirstNameANDClass_roster.LastName!=Employees.LastNameANDEmployees.EmployeeStatusID=1"); while($classroster=mysql_fetch_array($croster)){ extract($classroster); echo"$FirstName$LastName"; when I try to run this in mysql I get this error PHP Code: #1064-YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'=!Employees.FirstNameANDClass_roster.LastName!=Employees.LastNameANDEmploy'atline1 however I dont see anything wrong with that section of my query\t as I am telling it to get the employees first and last name where the first and last name does not match whats in the class_roster table.
VIEWS ON THIS POST

159

Posted on:

Monday 29th October 2012
View Replies!

Newbie help with a join

Bit of a newbie when it comes to databases, just wanting a little help with (what i think will be a join of a complex nature) rightyo I have five tables tCompetencies - the table that stores the compentcy name and details about the competency tCompetncyReqs - the table that acts as a bridge between the modules and competency tables, bascicly defining which modules you require to complete the competency and considered "qualified". tModules - the table that holds information about the specific modules. tModuleResults - the table that holds the results of the modules as a user completes them tAccounts - which is another table where users are stored (basiclly where the accountId comes in, each user account has an account id) TABLES tCompetencies ----------------- competencyId name etc etc tCompetencyReqs ----------------- reqId competencyId moduleId etc etc tModules ----------------- moduleId name etc etc tModuleResults ----------------- accountId moduleId result etc etc tAccounts ----------------- accountId fName lName etc etc What i need to do is a query which gets all the required modules for a specific competency (i know how to do this bit) but then exclude the records (modules) which the person has already completed (ie present in the moduleResults table) complex joins are not my strong point , any help would be appreciated
VIEWS ON THIS POST

151

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Need help with some joins

Thanks for the info r937. Cafelatte, you were right when you said that the dataset was not representative, I should have included a product in the "products" table which did not have the "products_model" field set as 'CUSTOM'. I have tried to add a condition to the code you provided so that only product_id's with an associated "products_model" field set to 'CUSTOM' are returned, but I can't seem to put the condition anywhere without screwing up your code.
VIEWS ON THIS POST

136

Posted on:

Monday 29th October 2012
View Replies!

Find nr of records in right tabel on a join?

, im not too good at sql, so im getting bit into trouble now that i need a join. this is what i have : SELECT * FROM `out` INNER JOIN `galleries` ON `out`.`gal` = `galleries`.`id` WHERE `galleries`.`owner`={$sUserId} GROUP BY `galleries`.`id` and well it works it gives as output 1 line for each record from the galleries table if is at least 1 record in the out table matches the id. But now i need to know exactely how many records he found in the out table for each record of the galleries table (and would love it if i can order the recordset generated by this select that way). off course i can do so by making a new select for each output line and count the records from that select but that would result in way to many db accesses to have my page loaded on time. so is there no way to do that in this join thx
VIEWS ON THIS POST

128

Posted on:

Monday 29th October 2012
View Replies!

Limit on a join

I'm stuck on a join. I have two tables, games and scores. Each row in games can relate to many rows in scores. Code: Games | id | date | user | | 01 | 02/02/2009 | 00 | | 02 | 03/02/2009 | 00 | | 03 | 03/02/2009 | 01 | Scores | id | gameid | score | | 01 | 01 | 23 | | 02 | 01 | 25 | | 03 | 01 | 17 | | 04 | 01 | 21 | | 05 | 02 | 29 | | 06 | 02 | 23 | | 07 | 03 | 12 | I want to return all of scores.score for the 10 most recent games for a certain user. The problem I have is I don't know how to add a limit to the join. So far I have SQL Code: Original - SQL Code SELECT `score` FROM `scores` INNER JOIN games ON scores.gameid = games.id AND games.user = 0
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!

Help with join query

hi ! This my table design so people can understand better what Im trying to do. And I have this query that should return to me all the people that havent paid something. Code: SELECT pagado,paciente.idPaciente,Nombres,Apellidos,conceptos.Nombre,Faltante,Cobertura FROM paciente LEFT OUTER JOIN conceptos_contratados ON conceptos_contratados.idPaciente = paciente.idPaciente LEFT OUTER JOIN conceptos ON conceptos.idConcepto = conceptos_contratados.conceptos LEFT OUTER JOIN conceptos_facturar ON conceptos_contratados.Conceptos = conceptos_facturar.idConcepto WHERE conceptos.Tipo='Dias' AND conceptos_facturar.pagado=false ORDER BY paciente.Apellidos But the query is returning me people that have pagado=true, wich is really weird.
VIEWS ON THIS POST

135

Posted on:

Monday 29th October 2012
View Replies!

Update using a inner join?

I dont know if thats even possible I was thinking to update several tables using joins to select wich data to update something along the lines of: Code: UPDATE conceptos_contratados set Contratadas=30 LEFT OUTER JOIN conceptos_facturar ON conceptos_contratados.idConceptocontratado=conceptos_facturar.idConceptocontratado LEFT OUTER JOIN conceptos ON Conceptos.idConcepto=conceptos_contratados.Conceptos WHERE conceptos_facturar.Pagado=true AND conceptos.tipo='Dias' AND conceptos_contratados.Estado='En tratamiento' Can it be done I know I could do it the old way like "Where A.id=B.id,etc... " but Im just courious
VIEWS ON THIS POST

157

Posted on:

Monday 29th October 2012
View Replies!

Left outer join, 3 tables, SUM field from 2nd/3rd tables

I'm having a problem with my LEFT OUTER JOIN on 3 tables, with a SUM clause. This is my code: SELECT r.ret_desc, vmp.vlt_id, SUM((vmp.cash_in + vmp.per_cash_in_sec) + (CASE WHEN vmp.acc_id = t.db_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END) - (CASE WHEN vmp.acc_id = t.cr_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END)) AS in_adj, FROM ret AS r LEFT OUTER JOIN vmp ON r.acc_id = vmp.acc_id LEFT OUTER JOIN txn AS t ON (r.acc_id = t.credit_acc_id OR r.acc_id = t.debit_acc_id) WHERE t.txn_type_id IN (1,2,4,5) GROUP BY r.ret_desc, vmp.vlt_id ; The problem is that there are multiple VMP records and the SUM field gets multiplied by the COUNT of the VMP records. Ex: If the SUM field should equal $100 and there are 50 VMP records, then the SUM becomes $5000, when it should just be $100. Does anyone know what is wrong with this statement (posted here in case a SQL-guru knew the answer) Thx!
VIEWS ON THIS POST

116

Posted on:

Monday 29th October 2012
View Replies!

Mysql joins

Ok, here's the scenario. Lets say I have 2 tables: Table: stations. Fields: station_id (int), call_sing (varchar), dma_id (int), msa_id (int) table:cities fields: city_id (int), city (varchar) This is for a radio station site. Basically, stations.msa_id and stations.dma_id both are foreign keys for the cities.city_id. So they both refer to cities -- and usually they are different cities (one is physical location, the other is the market city) So if I wanted to select all call signs, dma (city name) and msa (city name), how would I construct that query The problem i'm running into is this: SELECT call_sign, city as dma, city as msa FROM stations, cities WHERE stations.dma_id = city_id AND stations.msa_id = city_id This doesn't work cause im asking the city_id to be equal to two different keys for each record. How do I do this then
VIEWS ON THIS POST

158

Posted on:

Monday 29th October 2012
View Replies!

2 COUNTs and 2 left joins

I have three tables: User(id, name) Phonenumber(id, user_id, number) Email(id, user_id, email) I want to retrieve all users with the count of their phonenumbers and emails like so: id, name, count(user's phonenumbers), count(user's emails) How can I achieve this Is it possible to do this without subqueries and with just Joins
VIEWS ON THIS POST

155

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Adding yet another inner join

Quote: Originally Posted by zippers24 all that was missing in the end was product_image.product image in the original select statment. i said that
VIEWS ON THIS POST

149

Posted on:

Monday 29th October 2012
View Replies!

Query left join problem

Code: SELECT comic.*,comicuser.Username,comicuser.Email, comicrate.userrating FROM comic Left Join comicrate on comicrate.comicid = comic.id Left Join comicuser ON comicuser.id = comicrate.userid AND ( comicuser.id = "1" OR comicuser.id IS NULL ) group by comic.id ORDER by comic.userratingtotal DESC What i'm doing here is joining the comic.* with the comicuser.userid and the comicrating.* I am trying to gather non-duplicate entries of comic.* where the comic is either rated or not rated, if its rated than make sure its the entry that the user has rated and none of the other ones .. what is happening is that its getting all the non duplicates and duplicates that have userrated (showing only the first one it finds that is rated) which may or may not be the userid
VIEWS ON THIS POST

158

Posted on:

Monday 29th October 2012
View Replies!

Detecting what tables data is from in union join.

Hi , I'm using a union to connect to tables together, it's working just how I want but I have suddenly realized that I need to be able to tell what table the information is coming from. PHP Code: $query="selectid,title,timestampfromportfoliounionallselectid,title,timestampfromgalleryorderbytimestampdesclimit4"; above is my sql select. I'm pulling in the id, title and timestamp for the most recently updated items in my portfolio and gallery.. My problem is that I want to create a link from each item in this updated list to the actually item either in my portfolio or in my gallery, so is there an easy way for me to be able to tell if the information is coming from the gallery table or the portfolio table below is what I would like to output $is_this_portfolio_or_gallery; being the bit I don't know how to work out. PHP Code:
VIEWS ON THIS POST

149

Posted on:

Monday 29th October 2012
View Replies!

Ordering by price value asc in joined table

Hi There, I getting a little confused with this one and would appreciate any pointers anyone can give me. Code: SELECT distinct p.pid, p.cid, p.name FROM p LEFT OUTER JOIN pcx ON p.pid=pcx.pid LEFT OUTER JOIN pxo ON p.pid=pxo.pid WHERE (p.cid='$cid' AND p.publish='Y') OR (pcx.cid='$cid' AND p.publish='Y')ORDER BY pxo.price ASC LIMIT $position, $nresults trouble is this bit: Code: ORDER BY pxo.price ASC Some of the rows have 2 values for the price and I need to order by the lowest of the two values.....By default (with my query) it orders by the higher of the two values which means that my results are out of sync. I hope i explained right!
VIEWS ON THIS POST

171

Posted on:

Monday 29th October 2012
View Replies!

4 table join for tshirt orders; I've almost got it!

OK, I've got 4 tables: paypaldata contains the customer name, etc orders contains the item_id, cust_id and shirt size (optional) products contains item_id (id) stock contains prod_id (same as item_id), size and weight Code: select item_id, cust.id as custid, p.name, ord.qty, ord.size, (weight * qty) as weight, stock.size from paypaldata as cust left join orders as ord on cust.id = ord.cust_id left join stock on ord.size = stock.size and ord.item_id = stock.prod_id left join products as p on ord.item_id = p.id where ship_date IS NULL AND (txn_type = 'cart' or source = 'shana') order by order_date desc, cust.id desc This query returns this data: Code: "item_id","custid","name","qty","size","weight","size" "239","1695","Corporate welfare redux","1",NULL,NULL,NULL "214","1693","Octophant pillow (gray)","1","",NULL,NULL "224","1693","Resist","1","",NULL,NULL "205","1689","Monkey vs. Mao wallet","1","",NULL,NULL "241","1688","Skulls and kittens pink","1","m","6","m" "238","1687","Hail wallet red","1",NULL,NULL,NULL "187","1685","King rhino (girls)","1","l","6","l" "205","1684","Monkey vs. Mao wallet","1","",NULL,NULL The result set is the correct number of rows, but there are no being returned weights for items with NULL sizes since I'm using AND. I need weights for all items. So I'm thinking I need to left join paypaldata and orders together, then left join stock to products, then take the results of those two and left join A+B to C+D. Or... can I just do something else
VIEWS ON THIS POST

164

Posted on:

Monday 29th October 2012
View Replies!

Please explain outer join and how i could implement it?

I have two tables called table1 (sid, name) and table2 (sid,desc,value). Now i do something like: select name,sid,desc from table1 as s left join table2 as p ON s.sid = p.sid order by name and it returns: anthony, 1, player anthony, 2, boxer Britany, 3, dancer Casper,4,shower etc This is just an example but i do get the first row as repeating throughout However how could I implement it using PHP and MySQL so that I can keep track of the change in first column for example: I would have to keep on checking after i do the query to see if that row is same as previous row if not then echo it out Code: $prev = ""; while(row = mysql_fetch_array($result)){ if ($row[0] != $prev){ echo $row[0] } echo $row[1]; echo $row[2]; $prev = $row[0]; } SO i am wondering as i have numerous times heard of outer join (and even after googling i havent gotten a good explanation) i was wondering how would i do it so that the first col is only displayed once or how would i incorporate it in PHP , lnxgeek, i know what he wants paul, yes, you have to do that with a php loop over the result set an outer join is not what you want here
VIEWS ON THIS POST

136

Posted on:

Monday 29th October 2012
View Replies!

Multiple joins and query efficiency..

Okay , here is my database structure: table: category table: category_has_content table: content table: category_has_category= I need to retrieve all of the data associated with a category including child categories and all content. My query looks something like this: //to get the content SELECT * FROM category_has_content JOIN content ON (content.content_id = category_has_content.content_id) WHERE category_has_content.category_id = $this->id ORDER BY content.content_views_month LIMIT 0, $perPage" //to get the child categories of the category SELECT gen1_category.category_id, gen1_category.category_name FROM category gen0_category JOIN (category_has_category gen0_category_has_category JOIN category gen1_category ON (gen1_category.category_id = gen0_category_has_category.child_category_id)) ON (gen0_category_has_category.parent_category_id = gen0_category.category_id) WHERE gen0_category.category_id = ".$this->id."" I'm kinda new to join, so if I join the two queries into one query like so: SELECT * FROM category gen0_category LEFT JOIN (category_has_content gen0_category_has_content JOIN content gen0_content ON (gen0_content.content_id = gen0_category_has_content)) ON (gen0_category_has_content.content_id = gen0_category.category_id) LEFT JOIN (category_has_category gen0_category_has_category JOIN category gen1_category ON (gen1_category.category_id = gen0_category_has_category.child_category_id)) ON (gen0_category_has_category.parent_category_id = gen0_category.category_id) WHERE gen0_category.category_id = ".$this->id."" The gen0/gen1 are for the instance that I might retrieve grandchild categories and contents in the future etc. Then I would get duplicate entries for the content for each child category. For example, if I have 4 contents and 2 child categories my result set has 8 rows. This seems inefficient but I'm assuming that's just how joins work. If the overall efficiency of one query in this format outweights parsing through the probably much larger result set, with native PHP, I would be happy to write the functions. It seems unlikely as the result set would get huge, with lets say 2000 contents and 50 categories (50*2000 = oMg). Am I missing something Also another question, lets say I want to enable the user to browse through all of the content by letter and the browsing interface is based in flash. Whenever a user rolls over a letter all the content for that letter is displayed. Do you think I should then retrieve ALL of the content and seperate it into letters (despite the fact that a user might not be interested in every letter) from the getgo, or do you think it would be smarter to run 26 query for each letter whenever a user hovers each one. Now this wouldnt be a problem if I werent writing a RIA, as the content for each letter would only need to be accessed whenever a user clicks. Anyway, this is mainly a theoretical question as in, what's more efficient (I suppose I'm beating a dead horse, as it really depends on the ammount of content) a huge query from the getgo or a large number of smaller queries at probably a very short interval from each other (seems like a lot of server load). Anyway, let me know. I might be overobsessing about efficiency.
VIEWS ON THIS POST

137

Posted on:

Monday 29th October 2012
View Replies!

Table join

Hi. I have 2 tables. Products : -CategoryID -ProductID -Productname -PPrice -Postage -Description Colours : -ColourID -ProductID -Colour My problem is that I know that my pc case comes in black, white and blue for example. However, when you click on a menu item eg: "cases" it passes a category ID through url. I get that on the next page and it shows all records for that category. I am trying to join the products and colour table so that it shows available colours for each product in a dropdown. My sql is as follows but doesn't work how I want it to. [MYSQL]SELECT * FROM products INNER JOIN colourchoice ON products.ProductID = colourchoice.ProductID WHERE CategoryID = colname[/MYSQL]
VIEWS ON THIS POST

163

Posted on:

Monday 29th October 2012
View Replies!