How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?
TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+
TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+
View Complete Forum Thread with Replies
Related Forum Messages:
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this: SELECT A.id AS Id, A.nome AS Nome FROM main_table A JOIN joined_table JT ON JT.id = A.id_cat ORDER BY ("first whoever have a row in" another_table AT) another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first... I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)
View Replies !
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
View Replies !
How To Delete Rows In A Table Where Url Shows Only 1 Time In Whole Table
I don't know a whole lot about mysql, i need to fix some things in my db and what i described in the title is the first thing id like to do. Now, I have a table which lists referring urls, clicks to them and the time. Everytime i get a hit from lets say url1.com it gets a new row with a time id (like 1218613809). I am not resetting this table since it has to track the incoming hits forever since the moment they start sending traffic. But now the table starts getting the hickups now and then, it has about 850.000 records in it and it cant handle it anymore. I need to do a 'repair table' action and then it's good again, but it happens more and more often lately. So first i want to delete the rows of the urls who have only sent me 1 click. These urls who sent me 1 click only have one row. When an url sent me 20 clicks, that url is in 20 rows in the table. So I want to delete the rows of the table with the url's that only show once in the entire table. How do i do this?
View Replies !
How To Delete All Rows From One Table With Id NOT Found In Second Table?
How can I delete all rows from table `one` with id NOT found in table `two`? Here is my situation: Table `one` contains transaction on items identified by `id` Table `two contains detailed information on items, identified by the same `id`. Periodically, items are be deleted form Table `two`. My question is: How do I find (and delete) all the rows in table `one` that refer to an `id` that is NO LONGER in table `two` ?
View Replies !
Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A.. Example: B.part = "abcdefg" matches A.part ="cde" I cannot put literal in query.. it must be from column data.. It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.
View Replies !
Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag: resource - 1:M - tagged_resource - M:1 - tag So a resource can have many tags and a tag can belong to many resources. The data would look like this: RESOURCE TABLE id: 1 name: resource1 TAG TABLE id: 1 tag: new id: 2 tag: tested TAGGED_RESOURCE resourceID: 1 tagID: 1 resourceID: 1 tagID: 2 How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE? I have tried using 'IN' but it acts like an OR operator rather than AND.
View Replies !
Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem: Simplified tables: ARCADES ======= ID, name GAMES ====== ID, name ARCADES_GAMES ============= ID, arcade_iD Games_ID Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those. I can do soemthing like... SELECT a.name FROM arcades a WHERE EXISTS(SELECT 1 FROM arcades_games WHERE arcade_id=a.id AND game_id IN (11,14)) But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14. It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?
View Replies !
Delete Rows After 24 Hours In A Table?
I'm gonna keep track of all uniqe visitor on a web page and therefore I need to store all visitors IP-number. Each IP-number is only counted as a visit if its on diffrent days. So the IPs will be saved for 24 hours. Is there a way to automaticly delete rows after 24 hours in a table?
View Replies !
Query Delete All Rows From The Table?
I have a table called "repairs" with multiple rows (let's say for the purposes of this post - from repair_id=1 through to repair_id=10) Why does this query delete all rows from the table? DELETE FROM repairs WHERE repair_id=3 OR 4
View Replies !
Selecting Filtered Results From A Table With 1 Million Rows
I am trying to write an interesting query here and I am not sure if it all can be done in one step in the same query. Here is my dilemma: I have a DB with two tables: mm_recipients and postal_codes. The first one has a list of users in my system and the second one a list of 1 million postal codes in Canada. My objective is to select a list of 50 users that live less than 10Km away from a central location and are between 25 and 50 years old. The MM_RECIPIENTS table has the following fields: ....
View Replies !
Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using: *********** SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC *********** I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?
View Replies !
Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName This obviously doesn't work. Any assistance on how to forumulate this query (or, if I'm headed down the wrong path, the correct way to do this operation) greatly appreciated.
View Replies !
Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table? for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0. i don't want merged results. i just need to list all therapist based on the where criteria from a different table. these two tables have the therapist_id in common.
View Replies !
Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows: Customer table contains: 1) customer_email_address 2) customer_newsletter (value 0 or 1) Visitor table contains: 1) email The visitor table contains email addresses from customers that have signed up through another system. I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.
View Replies !
Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables, Table A- has the field "id" which is the primary key Table B- has the field "id" which is the primary key Select * from tableA where Table A.id is not in tableB.id How can i form a statement that will accomplish the above.
View Replies !
Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table This is a SQL that I am trying to use UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000, SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000, SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000 WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1; I got error "ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET SOLAR_SY' at line 1" I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?
View Replies !
Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email. I want to delete the rows completely having the duplicate email. I found a way out is to use some thing like this: Code: insert into newtable (email) select distinct email from oldtable But this way, only the email column is populated in the new table. I want all the other columns as well.
View Replies !
Partially Updating Records Based On Pattern Match
I would like to know if it is possible to a (string) replace on existing records based on a given pattern. Let's say I have a table containing the following records (strings): Windows/98 Windows/98/Registry Windows/2000 Windows/2000/Registry Is there an SQL method for scanning all records in the table for the pattern "Windows/98" and then, when a match is made, replace only the "Windows/98" part of the string with "Windows/2003"? I am having a hard time figuring this out.
View Replies !
Delete From Table Statement Doesn't Delete
I have a weird thing happened in my application where I have run a statemement Delete from tblmysqlReceipt; but later I checked through in my log file there line which have been delete is still exist. If any one can help me I can even post the log files too.
View Replies !
ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211). mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 5359211 Avg_row_length: 801 Data_length: 4294967288 Max_data_length: 4294967295 Index_length: 45783040 Data_free: 0 Auto_increment: 5406252 Create_time: 2007-04-20 18:26:38 Update_time: 2007-08-22 09:55:22 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) Here is the table structure. CREATE TABLE `messages` ( `id` int(11) NOT NULL auto_increment, `subject` varchar(255) NOT NULL default '', `message` text NOT NULL, `attachment_path` varchar(255) default NULL, `new` tinyint(4) NOT NULL default Ɔ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; On searching I found the this link. http://dev.mysql.com/doc/refman/4.1/en/full-table.html According to manual i executed the following query MySQL ALTER TABLE messages MAX_ROWS=20000000000; I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this. After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left. mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 170109 Avg_row_length: 649 Data_length: 110563036 Max_data_length: 281474976710655 Index_length: 1961984 Data_free: 0 Auto_increment: 5409214 Create_time: 2007-08-23 10:07:08 Update_time: 2007-08-23 13:41:57 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) What could be the reason of this rows deletion? I am on 32 bit system. MySQL version: 4.1.18-standard-log Operating System : CentOS 3.x Memory: 4 GB DDR
View Replies !
Count In One Table, Match With Data From Another
I've been going around in circles tryng make a mysql query return a correct result. Basically, I have two main tables that contain almost identical data however with different columns. I need to count rows from one table and match those rows to column data in a second table and display the matched results from both tables. First Table - booklist booklist.author booklist.title booklist.genre Second Table - bookhistory bookhistory.author bookhistory.title bookhistory.checkout_date I want to count the number of times the book has been checked out and list it by author, title, genre and the number of times checked out in the year. Code:
View Replies !
Use MATCH For Search In Joined Table
I'm trying to find matches in comment table with a fulltext search, then get the corresponding post table with JOIN and fetch information like post date, post content etc. from there. This works fine, as long as I'm using LIKE to search the comment content (ââ¬âthe comment table has been altered and the content is indexed, ready for fulltext search): SELECT p.post_date AS unionDate, p.post_title AS unionTitle, p.post_name AS unionSlug, 'comment' AS unionType FROM wp_posts AS p JOIN wp_comments AS c WHERE p.post_status = 'publish' AND ( c.comment_post_ID = p.ID AND c.comment_content LIKE '%earchwor%' ) LIMIT 0 , 30 (In case you're wondering about "unionTitle", "unionType" etc.ââ¬âmy results are being combined with others in a different step, using UNION.)
View Replies !
Inserting Rows From Backup Table To Current Table
I have two tables, with identical structures. One is a mysqlorary table loaded with the information from the current table a few weeks back - it is essentially a backup table. I now need to insert a selection of data from the backup table into the current table. If an item from the backup table exists in the current table, it is to be overwritten. I was wondering on the best way of doing this? Would it be better to loop through a php array of table rows, and delete from the current table then re-insert from the backup table? If so, how would i maintain the primary key (it's auto-incrementing)? Or should i write the rows to be transferred from the backup table to the current table to a file, then use load data in file? Are there any other ways of doing this?
View Replies !
Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with. My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear. What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear. My Table... id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 4..............QD...................4......................2007 (current month and year) 5..............QE...................5......................2007 6..............QF...................6......................2007 I can simply get the question for the current month... SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007; but if I try to get all others using SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007 I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 *********************(remove runMonth4 and runYear 2007) 5..............QE...................5......................2007 6..............QF...................6......................2007 What am I missing?
View Replies !
Multiple Table Search Using Match (fulltext)
I am running a search on multiple tables for a match using: ... MATCH(table1.full_text_index) AGAINST ('string') OR MATCH (table2.full_text_index) AGAINST ('string') OR.... I want to be able to tell which table find a match. If there are multiple tables that find a match to able to identify which table found a match.
View Replies !
UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like: UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN table2.ID; The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.
View Replies !
Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement: UPDATE ApplicationTbl INNER JOIN AcademicTbl ON ApplicationTbl.CampusID = AcademicTbl.CampusID AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period SET ApplicationTbl.App_Status = 'Qualified' WHERE AcademicTbl.Sem_OnCampus >= '1' AND AcademicTbl.GPA >= '2.4' AND AcademicTbl.Judicial_Sanction IS NULL It keeps saying its wrong. Even though I know its not
View Replies !
Selecting From One Table Based On Another?
I have a table for products and another table for the type of product it is like this: I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc. but how can I then select a product based on criteria from the types table say I wanted to select all products that are type = "Band" and type != "Fancy" ?
View Replies !
Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning. current query looks like this: SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id; So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?
View Replies !
Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.
View Replies !
Deleting From A Table Based On Record Id
I need to execute the following query: delete from history where id=(select min(id) from history where user = username); this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause" How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.
View Replies !
How To Itemize Results In MATCH AGAINST
im currently using the MATCH AGAINST feature in mysql to build a query similar to this: "SELECT * FROM db WHERE MATCH(col1, col2, col3, ..., col9) AGAINST ('$query' IN BOOLEAN MODE) where col1 - col9 are large chunks of text each. If that query above returns rows, how can i demonstrate what columns the search query was actually found in instead of just outputting the entire row??
View Replies !
List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme: TABLE Prerequisites PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column) UpgradeID (FK to Upgrades table as well) TABLE BuildingUpgrades BuildingID (FK to Buildings table) UpgradeID (FK to Upgrades table; states what Upgrade Building already has) So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U. What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way. The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me: SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID) FROM BuildingUpgrades BUPG JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID GROUP BY BUPG.BuildingID, BUPG.UpgradeID HAVING COUNT(PRE.PrerequisiteID) = ( SELECT COUNT(PrerequisiteID) FROM Prerequisites WHERE UpgradeID = PRE.UpgradeID GROUP BY UpgradeID ) What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.
View Replies !
Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.). That seems simple enough. But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user? Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him. I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to. I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.
View Replies !
Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model? By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one : SELECT level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name, level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name, level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name, level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name FROM ecom_categories AS level0 LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID WHERE level0.cat_parent_ID IS NULL ORDER BY level0_name, level1_name, level2_name, level3_name
View Replies !
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city. What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'. So is it possible to make those 2 query in one so I do not need to do a php loop: SELECT city, COUNT(city) FROM profile GROUP BY city; then UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city
View Replies !
Multiple Table Search Based On User Input
I'm working on a database containing our research data. I know very little about PHP, MYSQL, or database structure (except keep like data together in different tables and have a key field in each). I'm currently working with 3 tables (tbl_2006 and tbl_2007 structures are identical): ....
View Replies !
Populating Matrix Table Based On Books Taken By User
I've been trying to do this for 2 hours now. My noobness in mysql is not letting me get through =( USERS: id_user | f_name | l_name 1 | mike | smith 2 | david | bush etc... BOOKS: id_book | s_name | l_name 10 | book 1 | walking together 20 | book 2 | straight path 21 | book 2A| crooked path etc.... REGISTRY: id_fk_user | id_fk_book 1 | 10 1 | 21 2 | 10 2 | 20 2 | 21 etc.... Now, I am trying to fill out a table by row using pear's HTML Table that would end up like this: Name | ID | Book1 | Book2 | Book 2A | etc.. mike smith | 1 | x | | x | etc.. david bush | 2 | x | x | x | etc.. I am having a hard time because the table wants each row's data separetly before it send it to HTML output. And my SQL statement looks like this: SELECT id_user, f_name, l_name, id_book FROM USERS, BOOKS, REGISTRY WHERE id_user = id_fk_user AND id_book = id_fk_book which gets me: 1 mike smith 10 1 mike smith 21 2 david bush 10 2 david bush 20 2 david bush 21
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image. The file with the new data is a .txt file, a sample of which looks like this: v_products_modelv_products_imageEOREOR 5361453614.jpgEOREOR 5361553615.jpgEOREOR 5372453724.jpgEOREOR The beginning part of the table looks like this: +----------------------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------+------+-----+---------------------+----------------+ | products_id | int(11) | NO | PRI | NULL auto_increment | | products_type | int(11) | NO | | 1 | | | products_quantity | float | NO | | 0 | | | products_model | varchar(32) | YES | MUL | NULL | | | products_image | varchar(64) | YES | | NULL | | | products_price | decimal(15,4) | NO | | 0.0000 | | | products_virtual | tinyint(1) | NO | | 0 | | From what I've read over the past few days...I think I need to: (a) delete the EOREOR column from the text file; (b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.
View Replies !
|
TRACKER
