Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.
I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Ranking Scores
I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table. the link below shows an example image of what i need to happen.
A Ranking Leaderboard
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved. Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on? For example if I had a basic table with fields of name and points. You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name. How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated... Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.
Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league. for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20. I want to show on each individual team page their stat, with the rtanking for the league.
Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column. I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.
Ranking Users
i'm trying to rank the users in our site. table contains: - userid - userrates and with thousands of records. member with the highest rate goes #1, next is #2 and so on. i want the query to be like: Code: <something goes here> WHERE userid = '$userid' order by userrates desc from the code above it will show the rank of this user. then the next user logsin and it shows again his/her ranking. hope someone understands this any input is appreaciated
Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts? And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.
Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved. Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on? For example if I had a basic table with fields of name and points. You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name. How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated... Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.
Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly. So the result should be something like this: ID Amount1 Ranking1 Amount2 Ranking2 ---- -------- --------- ------- -------- 135 123.34 4 23.00 3 184 160.23 1 60.89 2 845 140.22 3 100.20 1 987 155.00 2 1.20 4 The ID isnt important here. The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one. Is there a way to get the ranking filled in the same pass as the amounts are calculated?
Sort And Give Ranking
let say i've have these following table. tblA name | marks ----------------- robert | 39 johnny | 78 bruce | 23 elena | 56 halim | 23 formula, if same marks (see bruce and halim), sort them by name how to query? expected output showing as below: no | name | marks ---------------------------- 1 | johnny | 78 2 | elena | 56 3 | robert | 39 4 | bruce | 23 5 | halim | 23
Sorting And Put Ranking In Field
I have 3 field in my table Ranking, Name and Score. Is it possible to sort all record to order by Score and put Ranking no. to each record by using query not much?
Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table. For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank. An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method. Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.
Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking. For instance: Username | Rank steve | 6000 bob | 5000 jane | 4000 chris | 3000 brian | 2000 tim | 1000 molly | 0 Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following: bob | 5000 jane | 4000 chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this. brian | 2000 tim | 1000 What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from. For instance, lets say Chris is row 583 of my database... how do I retrieve that number?
Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14 or 3.23 SQL to get student grade ranking where tied grade have the same rank. I used to set it through MS Access 2002 and use this kind of query: SELECT nilai.studentNIS, nilai.studenttestmark, (SELECT COUNT(*) FROM tblStudentGrades WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC; I've been looking around mySQL documentation and read that subquery can be redefined as INNER JOIN or using two SQL statement via variable? I have no idea on the basics of how to set it out though. Could one of you please help give a me a sample on how this kind of query should be done on mySQL? Is it possible to do it in single line? And without having to use PHP/Perl scripts? Or maybe I should have approach it differently?
Full-Text Search: Truncation Operator And Relevance Ranking?
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1. A user-provided "workaround" on the MySQL site said to use the same keywords but NOT in boolean mode to get a usable relevance ranking. However, a standard fulltext search for "televis" or "televis*" will return no rows.
Using 'if' In A 'join Query' ?
I have two tables items and food_names in mysql db items ->structure **************************************** id food_items 1 veg 2 non-veg 3 veg & non-veg **************************************** food-names ->structure **************************************** id items_id foods 1 1 vbvcb 2 2 cvbvbv 3 3 gfdgdfgd 4 3 bbvcbvcb ******************************************* if i choose veg if(id=1) I want to write a db query for both veg and veg & non-veg else if i choose non- veg I want to write a db query for both non-veg and veg & non-veg else if i choose 'veg & non-veg' then i have to dispaly all.
Query: Inner Join Bug
What is wrong with this query? I cant get it to match the "company" field and it is throwing an error... this query works fine if i do "MATCH(date_year, market1, market2, market3, market4, market5, market6)" , but if I put "MATCH(company)" it breaks.... what am I missing here? PHP $query = "SELECT u.id , u.username , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE username = '$username' AND MATCH(company) AGAINST ('$P_search' IN BOOLEAN MODE) ORDER BY date_year DESC, date_month DESC, company ASC"; Here is my database schema: ================= user_reports ================= user_id report_id ================= user ================= id username ================= emt_report ================= id company description market1 market2 market3 market4 market5 market6 location date_year date_month source video audio pp execsum report_url exec_url
Using Both JOIN And AVG In One Query
I'm looking to use both JOIN and AVG in the same query but not sure how I'm going to it. I'm VERY new to using MySQL. The ultimate goal is to take one table which counts votes for various criteria in a poll. I want to average those results together. Then I want to join that averaged table with another table and GROUP By URL (which is the column common to both tables). I've successfully used AVG with the first table and used JOIN (with a totally different) table, I'm not sure how to approach using both together.
Help With A Query Join?
I have a query whereby I look in two tables a teams table and results table to output some data for some football scores. SELECT th.team_name AS home_team, ta.team_name AS away_team FROM results r INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id Now I want to add a third join there on another table named reports to see if the match_date in the results table matches a match_date in the reports table SELECT th.team_name AS home_team, ta.team_name AS away_team, re.match_date FROM results r , reports re INNER JOIN teams th ON r.team_one_id = th.team_id INNER JOIN teams ta ON r.team_two_id = ta.team_id INNER JOIN reports WHERE r.match_date = re.match_date Now this works great, but I want to output everything in the results table, and not just where results.match_date = reports.match_date as there may not be a report for every result and I want to still output every result nomatter if there is a report or not?
Using Join In A SQL Query
I have a problem which cant sort out. I have 3 tables where the primary keys in two tables also serve as keys in the third table. Table1:one two three four Table2:five six seven eight Table3:nine ten eleven one holds the same information as nine five does the same for ten Now I need to get information from the first and third tables but also need to use the second table to get the information from the third table. I can get the info from the first table grand , but its joining the 3 tables together is where I get stuck. I know its very abstact but this is the way I got the Tables.
Join Query
My problem is with my query below(sname means student name lecname means lecturer name) the table it produces means that the same lecturer can be supervising 2 projects at the same time(eg. Mon 9.00am in the query below) which is impossible. So is there a way I could change it so that lecname can only be associated with one projtitle for each time? INSERT into time select distinct p.sname,p.projtitle,s.lecname,s.time from projectstatus as p,properstafftimetable as s left outer join time as t on t.sname2=p.sname where t.sname2 is null and s.lecname = p.supervisor and s.time = "Mon 9.00am";
Join Query
I'm having trouble with a (simple?) concept in MySQL join. This query gives me the correct data from all 3 tables, but I need to sort by the date from 2 of the tables: SELECT det.CalendarDetailsID, dai.StartDate, wee.DisplayStart, det.Title, det.DetailsFROM phpCalendar_Details detLEFT JOIN phpCalendar_Daily dai ON det.CalendarDetailsID = dai.CalendarDetailsIDLEFT JOIN phpCalendar_Weekly wee ON det.CalendarDetailsID = wee.CalendarDetailsID Is there a way I combine dai.StartDate and wee.DisplayStart dates fields into a new (alias?) field that I can sort with? Or is there a way that I can simply sort the two fields within each other without combining them?
JOIN Query
This query won't work... tried to execute it a million times, it just loads forever. Any clue why? SELECT oh.client_id, oh.id AS load_number, oh.deldtime, oh.pickupdtime, oh.dtime, oh.status, oh.order_uid, oh.driver_id, cm.name AS client_name, oo.order_id, oo.city AS origin_city, oo.state AS origin_state, od.city AS destination_city, od.state AS destination_state, od.order_id, os.id, os.status AS order_status, dm.id, dm.name FROM order_header oh INNER JOIN client_master cm ON cm.id=oh.client_id INNER JOIN order_origin oo ON oo.order_id=oh.id INNER JOIN order_destination od ON od.order_id=oh.id INNER JOIN order_status os ON os.id=oh.status INNER JOIN driver_master dm ON dm.id=oh.driver_id ORDER BY oh.id DESC LIMIT 10
Join Query
I have two tables dispatch and pending orders now i want to get fields from both the tables where either dispatch.mail_id = 'p@yahoo.com' or pending.mail_id = 'p@yahoo.com' order by either dispatch.order_no or pending.order_no. How would I be able to do this with a join query.
Wish To Join A Query
I have this query: PHP Code: SELECT * FROM `news` ORDER BY `id` DESC LIMIT 6 I wish to also select the users id and username from the members table where the users id equals the "owner" field from the query above. As using another query apparently would be stupid?
Join-query
I cannot figure out how to query the db. I got 3 tables: players (player_id, name, number) stat_start (opponent_id, name_id) name_id = players.player_id stat_avb (opponent_id, name_id name_id = players.player_id Now I want to list all my players, and count how many times they have occured in stat_start and stat_avb. I guess I need to do an Inner join as i want all my players listed...
Join Query
this is embedded in a php file, and the request should bring all open assignment_events. it works fine except it spits them out 3-5 times, its the query and not the php, could someone please help with a join? Code: SELECT c.customer_id , a.assignment_id , ae.desc , ae.assignment_event_id , ae.closed FROM customer c , assignment a , assignment_event ae , event_type et WHERE a.customer_id = c.customer_id AND a.customer_id = 1 AND a.assignment_id = ae.assignment_id AND ae.closed = 0 ";
Join Query
i've got two table in the format: referrers --id, name, url, image country_refs --id, name, domain, ref1, ref2, ref3, ref4 and i'm using the query Code: SELECT referrers.name, referrers.url, referrers.image, referrers.description FROM country_refs JOIN referrers ON ref1 = referrers.id OR ref2 = referrers.id OR ref3 = referrers.id OR ref4 = referrers.id WHERE domain = 'gb'
Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk). On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query. I'm also not sure I'm thinking of this right. The fields on clientFamily start off like: clientFamilyID spouseFirstName spouseMiddleName spouseLastName spouseOccupation motherFirstName motherLastName motherOccupation fatherFirstName fatherLastName fatherOccupation I think that part's ok, but if I add: childFirstName childLastName childBirthday childSex and sibFirstName sibLastName sibOccupation how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.
Join Query Problem... Help!
I have two tables: STORES ------- id name ORDERS ------- id total cost store_id date_ordered I want to select ALL stores and their total sales for a date range. Here's my query: SELECT stores.id, stores.name, SUM(orders.total), SUM(orders.cost), COUNT(orders.id) FROM stores LEFT JOIN orders ON stores.id = orders.store_id WHERE orders.date_ordered > 'xxxx-xx-xx xx:xx:xx' GROUP BY stores.id This works, except that it only selects stores that have orders. If I leave out the WHERE clause, it then selects ALL stores, even without orders, and populates the sums with "null" That is what I want - if the stores don't have any orders, the results should simply return null or 0. However it seems that stores without orders are simply not being returned in the results at all.
Join Query Problem
student ------- id name 1 name1 2 name2 3 name3 Class ----- cls_id stu_id marks 1 1 100 1 2 300 5 1 400 5 3 500 I want to show only those student that are not class Ƈ' So I wrote select s.id,s.name,c.marks from student s,class c where s.id=c.stu_id and c.cls_id<>Ƈ' but it gives 1 name1 400 3 name3 500 but name1 is already in class Ƈ' . I desire the output only 5 3 500
Join Query Chaos
I cannot get this join to give me what I need. Here is my setup. I have 3 tables: courses, modules, sessions. courses has this structure:
JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of student numbers for each school from two tables School,Student. select sc.name, Sum(if(student.SNO is not null, 1, 0)) nmbr, from school sc left outer join student on sc.school_id = student.school_id group by sc.school_id this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13. i could not find a solution.But i suspect from NULL threatment.
SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem: SELECT * FROM footballers WHERE sname LIKE '%$criteria%' however, if I want to join two tables and still use the LIKE clause how would the syntax look?? My guess below was incorrect. any help would be appreciated. thank you WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
Help With Join Query Mysql 4.0
As I understand subqueries do not work in version 4.0 Here is what i am trying to do; select a, b from table1 where table1.b != (select distinct c from table2); Other words: table1.b and table2.c have values I want to match on I run select a, b from table1; subtract all out using select distinct c from table2; and leave me with a and b from table 1 that have no corresponding value of c in table 2
Generating A JOIN Query
I need some help with a MySQL query, probably a JOIN. Here is the situation: I have two tables. The first one contains customer info. Table CUSTOMERS id | name | address id ... customer ID (MEDIUMINT, autoincrement) name ... customer name, for example John Doe address ... customer shipping address Then I have the second table which contains sale info from all customers....
Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow: EventTable: ID EventTimeStep EventID -- ------------- --------- 1 5 E1 2 22 E2 3 56 E3 StateTable ID StateTimeStep StateID -- ------------- ------- 1 1 S1 2 39 S2 I want to perform a query that reports the StateID of the application at the time that each event was logged to the EventTable. The desired output is: ID TimeStep EventID StateID -- -------- ------- ------- 1 5 E1 S1 2 22 E2 S1 3 56 E3 S2 I have tried to create a query with an INNER JOIN where the value for the StateID output field comes from the last row in the StateTable WHERE StateTable.TimeStep <= EventTable.TimeStep and where I use a GROUP_BY EventTable.ID to merge the following rows from the join: 3 56 E3 S1 3 56 E3 S2 However, the closest I can get is a query that gives me the wrong state when applying the GROUP BY clause 3 56 E3 S1 I also think that the queries I have written is slow and inefficient. Is there a better way to perform this query or is my database design fatally flawed?
Table Join Query
I'm trying to join multiple database tables and having a little trouble with it, hope someone can help: There are 3 tables with columns: id, user, date, time, action I'm trying to get user, date, time and action from all 3 tables where the user equals "anyuser" and sort the result by date DESC, time DESC.
Join Query By Date
I've these tables: - PERSON (id,name,age,....) - EXAM (id,date,note,exam_type,id_user) One person has 0 or more exams. I have to do this report: name, age, date, note, exam_type This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)
MySQL JOIN Query
I am a recreational MySQL user, and I tend to make some non-optimal queries. In my latest project I made some LEFT JOIN queries that were extremely slow. I made two changes that made the queries almost instant. 1) indexed the columns that the joins were working on 2) optimized the tables being joined (using phpMyAdmin > Operations) All I can say is WOW!!! I had no idea that the resulting difference in speed would be so great. After I did step (1) it added a tremendous amount of speed, and after I did step (2) the queries responed almost immediately. So if you are running slow with JOINS, give indexing and optimization a try.
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
Left Join Query
I have this query SELECT user.user_id, email, password, full_name, company_name, first_login_date, last_login_date, first_draft_date, last_draft_date, first_submit_date, last_submit_date, rowc.* FROM user LEFT JOIN rowc ON rowc.user_id = user.user_id WHERE user.user_type = 'USER' ORDER BY user.email Which returns all records in user table plus rowc records if there are matches in the user id. but user.user_id is null when there are no matching recordings in the rowc. Isn't left join meant to pull all the records in the user table? What am I doing that is wrong here?
Query Trouble With LEFT JOIN
My query as it echoes out in PHP... SELECT sales_reps.sr_id, sales_reps.order_id, sales_reps.name, sales_reps.job_number, UNIX_TIMESTAMP(shop_orders.date) as date, sales_reps.stage, sales_reps.status, ....
Having Troubles With A LEFT JOIN Query
I'm currently building a private members area for a website where designs can be submitted and rated before they are released to the public under one unified "pack". What I am trying to do is to create a query that lists the latest submissions along with their ratings (if they have been rated) and if the user has rated that submission or not. Here is my query: SELECT s.id as id, s.title as title, s.users as creators, ROUND(AVG(r.rating), 1) as rating, r2.id as userhasrated FROM submissions AS s LEFT JOIN sub_ratings AS r ON r.subid = s.id LEFT JOIN sub_ratings AS r2 ON r2.userid = ".$user->getUserId()." AND r2.subid = s.id LEFT JOIN packs AS p ON p.id = s.pack AND p.released = 0 WHERE s.state > -1 GROUP BY r.subid ORDER BY s.id DESC LIMIT 10 The problem is that it only returns two rows: one with some submission that hasn't been rated and one with some submission that has been rated (but only rated by the user indicated by $user->getUserId()). How can I fix my code so that it returns as many rows as I desire?
JOIN Query Related Question...
I have two tables as shown below: Table 1: Actor.............................Table 2: Movie Field:...ID.....Last......First..............Field:...ID.....Last......Movie ...........1.....Willis......Bruce.......................1.....Willis......Die Hard ...........2 ....Cruze....Tom..........................2.....Cruze.....MI-III ...........3.....Pitt.......Brad ...........4 ....Depp.....Johnny Then I JOIN the tables with Query which works fine, following is the result: Field:...Last......First........Movie ..........Willis......Bruce......Die Hard ..........Cruze....Tom.........Cruze MI-III ..........Pitt.......Brad ..........Depp.....Johnny MY QUESTION: What JOIN option should I use so it would display data that are in both tables (such as Bruce Willis and Tom Cruze).
How To Join Three Table Into A Little Complicated Query
I am using version 4.1.22. I simplifying this for example's sake. I have three tables: order: order_id tip_amount item: item_id order_id price vendor_id vendor: vendor_id vendor Name Each order can have many items. Each item has one vendor. I need a query that displays all orders grouped by order_id, EXCEPT where there are multiple vendors where I need it to show grouped by order_id in the first column then grouped by vendor_id in the second column. Plus I need the tip_amount to show ONLY on the first line of the order. It would like this (notice order# 299 has two entries and the tip amount was added to the first record vendor record that order): PHP order_id vendor_name total tip298 ABC $30 $2299 DEF $25 $4299 XYZ $12 300 LKW $17 $7...
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
Problem In Left Join Query?
I have a pretty complex query and it's practically crashing my system due to generating more data than I am looking for. I basically have 2 tables. They should contain very similar data as they contain information about the same types of things. I was asked to join the 2 tables, getting any information from table 1 that does not exist in table 2, but keep everything of table 2. Anyway I tried this query and it just hangs forever. One problem could be because I have no ID field to match up between the tables. I'm trying to join them based on an address, city, and zip. Select * INTO OUTFILE 'output.tab' FIELDS TERMINATED BY ' ' LINES TERMINATED BY ' ' FROM table2 LEFT JOIN table1 ON table2.address = table1.address and table2.city = table1.city and LEFT(table2.zip,5) = table1.zip; My resulting file should be 13000 records in length, with 3000 records that have additional information based on table1 data.
|
TRACKER
