Need help writing a query to join multiple fields

I am trying to write a query for a single table where each record contains five part number fields. I've never done this type of query before and could use some help.

Here is the format of the table:

Code: Table Name: MyTable Fields: widget_1_partnum widget_2_partnum widget_3_partnum widget_4_partnum widget_5_partnum widget_1_description widget_2_description ... I want a query that will merge and return all distinct widget part numbers in a single result set field called partnum, independent of whether the part numbers are related to widget_a, widget_b, etc.

Basically I want to lump all the widget part numbers (from all five fields) into a single group and then have it return distinct part numbers from that group in a single result variable that I can index through in PHP (let's call it "partnum").

Anyone know how to do this Here is the code I'm trying to generate with this query:

Code: $result=mysql_query(""); $num_rows=mysql_num_rows($result); for ($i=0;$i

Posted On: Thursday 25th of October 2012 10:52:33 PM Total Views:  158
View Complete with Replies

Related Messages:

Help needed on MySQL search query with 2 tables   (98 Views)
Hi I have two tables as follows: table 'question' with fields 'patient_id', 'answer_id' and 'terms', and table 'answer' with fields 'answer_id' and 'answer_path'. I need to search question.terms for a keyword match and then join that match to the corresponding foreign key (answer_id) in table answer to return 'answer_path'. Any ideas on this greatly appreciated!!
Closest match to known answers query needed   (110 Views)
HI, I have a slightly odd bit off functionality to code and am struggling with writing a sql query. I have a MySQL database with some info on 100 people in the following field names: Code: ID,name, answer1, answer2,answer3,answer4,answer5 The values inside answer1-5 are a number between 1 and 4, so essentially the table stores the answers each person gave to a short multiple choice questionnaire. Now, I need to sort this table and return the results based on the answers a new user gives to the same questions, so sorting the data by the number of identical answers they gave and therefore finding the person most similar to themselves.
Noob needs help designing first database   (136 Views)
, Im trying to learn PHP & MySQL. I have been out and purchased 2 books on the subject and now I'm going to try and build somethig using the info the book is teaching. I have decided to try and build a lotto / raffle program but this is where I am stuck, and the book does not specifically cover this question. The problem is this: I can create a table with the following columns to store user info UserID | Fnam | Lname | Email | Tel | TotalTickets | Amount | Cost | PPReceipt | Datetime | But when it comes to store the tickets themselves is the issue. How would I store something like 10 randomly generated ticket strings and associate it with the one user I thought maybe build a second table which would have two columns, UserID | TicketStrings | Then for every ticket string purchased, the customers user ID would be in the ajacent column so that it was associated with that customer. Is this the correct or best way
Date format help [was: SQL idiot needing help!!]   (115 Views)
Thanks for reading my post, I need help with a date calculation. I want to delete certain rows in my database where - date1 < getdate - 1 year
Restaraunt rating system - need help, will pay$!   (156 Views)
, I need to build a rating system for Restraunts where a patron can come, answer 5 questions (rate 1-5) each. Also, each restaruant can purchase an advertising package (basic to premier), what i need to be able to do now is on the front end, list restaraunts in a specific order. So a resturant with a 3 star rating with a premier package needs to come before a resturant with a 5 star rating with a basic package (for obvious reasons if someone is paying $40 a month they should be displayed before some getting a free listing). There are other senerios like "Category" and "City". My plan is to store the ratings in a separte table linked to the restaruant table by an id. I'm stumped on how can i average the ratings table first by id's and then tie it to the resturant table so that the sql knows OK "this resturant has a 3 star rating" and this resturant is a premier listing so order before the 5 star guy with a basic. I'm willing to pay for someone with the knowledge to help me work through the problems as i go. I have a 30 hour limit to do the project so it would be cheaper for me to pay someone to help then spend 60 hours trying to figure it all out. feel free to let me know how i can get in contact with you we can figure out how to go about it, and what you would charge to help me. Sorry the system won;t let me post my email since i'm a new user...
Got the foreign key blues... need help   (146 Views)
hey all. Either I have a funadmental misunderstanding of how foreign keys work, or something is wrong with mysql on my server. Here's a simplified version of what I'm trying to do: I have a client table: Code: CREATE TABLE `cp`.`CP_client` ( `id` int(10) unsigned NOT NULL auto_increment, `contact` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; and I have a user table: Code: CREATE TABLE `cp`.`CP_user` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, PRIMARY KEY USING BTREE (`id`,`username`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; My Foreign key is basically the contact refences a user in the user table: Code: ALTER TABLE `cp`.`CP_client` ADD CONSTRAINT `FK_CP_client_1` FOREIGN KEY `FK_CP_client_1` (`contact`) REFERENCES `CP_user` (`username`) ON DELETE RESTRICT ON UPDATE RESTRICT; and the error I keep getting is this: MySQL Error Number 1005 Can't create table '/.cp/#sql-d1a_1d.frm (errno: 150) I've also tried creating the foreign key as a reference to the user id with the same outcome. Any idea what's going on
Page 2 - Query with subselect optimaization help needed!   (177 Views)
the subquery is correlated, which means it looks only at those rows where the is the same as the outer query's accounts.user so should have an index -- and you said that it does -- so whether the MAX() is on the id or on a datetime column doesn't matter, because it's the user that has to be filtered the only other thing i can think of is that instead of a subquery for the max(), you replace this with a join what's up with the STRAIGHT JOIN, what's that for
Comparing Tables...I need help.   (82 Views)
Let me set the scene for you. I have two tables on the same database: hr_harassment (everybody who has already entered into a training class) fields: lname, fname, class hr_employee_list (everybody who needs to enter into a training class) fields: Lname, Fname I need to compare the two tables to figure out who has not yet signed up for the class. What would be the easiest way to do this I am using PHP to program the page. I looked online and came up with this: SELECT * FROM hr_employee_list AND NOT EXISTS (SELECT * FROM hr_harrassment AND hr_employee_list.Lname = hr_harrassment.lname AND hr_employee_list.Fname = hr_harrassment.fname) But my browser tells me this: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in monitor.php on line 270
find lowest unique number in a column (was "SQL query help needed")   (121 Views)
Hi folk, Been working on a query whole day without progress so I hope someone of you experts can help me. I need to find the lowest unique number in a column: (Sorry dont know how to tag the code) (got a column name price and time) price time 5 2006-05-04:12:00:00 4 2006-05-04:12:00:04 5 2006-05-04:12:01:16 12 2006-05-04:12:02:54 4 2006-05-04:12:05:02 as you all can see is "12 2006-05-04:12:02:54" the lowest unique number. but how do I put this in a query
Some advice on performance needed   (206 Views)
I have two tables. One for products and one for reviews. I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. good so far. I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that Or is this not a problem Any input you can offer would be very much appreciated. Thank You.
WHERE statement- ' ' needed?   (195 Views)
I have the following statement: Code: SELECT DISTINCT u.user_title, u.user_first_name, u.user_last_name, e.course_name, e.course_date FROM user_name AS u, course_enroll AS e, course_complete as c WHERE e.course_ID = c.course_ID AND e.course_date = c.course_date AND e.course_session = c.course_session AND e.user_ID = u.user_ID AND e.user_ID != c.user_ID ORDER BY e.course_date DESC, e.course_name ASC My problem is the "e.course_session = c.course_session" in the WHERE clause. Both of these fields are varchar types. With the current syntax, the appropriate matched records are not being found. I tried: 'e.course_session' = 'c.course_session' and no records were returned. The data that will populate these fields will be things like "Session 1". Any advice on how to clean this up to get the statement to pull the right recordset is much appreciated...
Database Tean needed for important project - Please Read!   (163 Views)
AnimalHelp Foundation is looking for some fabulous database developers for a very important pro-bono type job. If you have time to give for a really great cause, please contact Jodi Witte at to join the team! The Concept: Recent devestating hurricanes along the gulf coast have truly brought to light the inadequacies of a central system of animal record keeping and ultimately helping these displaced animals reunite with their families. There is NO central database that data can be easily entered by volunteers, and searched through by rescuers or pet owners. has built a makeshift system that is helping, but is not great. If you visit and lets say you are looking for a solid black, 10 year old male german shepherd, you will find it is almost impossible to find your pet. Now, consider that this is not the only list of animals displaced due to Hurricane Katrina. These animals are spread out over 50 different databases such as and (The list of all known databases is available if you are interested). If that is not proof enough, I can send you a photo of a grey/black standard poodle which was picked up by a coast guard helicopter in New Orleans, then I can also include the list of all the databases. If this was your dog, would you find him I don't think so. I have tried. This dog I mention was in my care at the New Orleans airport as I received him off the helicopter. He sat for 2 days with me on the tarmac watching for his family to show up. While we were indoors, he would sit facing the door, just waiting. Nothing and no one could get his attention, he was so sure his owner would walk through the door. He was then moved to a shelter in Baton Rouge where I visited him again. He had given up and no longer was watching for them. But then he was moved again, and now despite my best efforts, I can't find him. I don't know where he was sent, which of the multiple shelters all over the US he has been taken. If I can't find him, then I can only conclude that his owners never will either. This is very sad!!! What happens after a disaster is many groups converge on an area and begin rescue. The animals are sheltered at a few main locations, with some smaller private home sheltering one or two animals, vet clinic and also boarding kennels also doing sheltering on a small scale. Each group uses their own intake forms, and then it often takes 3 to 4 weeks before data entry is started. The Solution: AnimalHelp Foundation will provide one central online database, digital cameras and laptop computers at every location, along with Directway satellite internet access and generators to power the computer equipment. AnimalHelp Foundation will also provide dedicated data entry volunteers at each location. Right from the beginning these animals are entered in the database and their locations tracked at every step. In the database all information will be entered by the volunteers, including sex, coloring, tags, collar color, location found, breed, etc... This database will be searchable by breed and color, or color alone, or any combination of description or information. It will be a high powered database, making it easy - because everything is there - for an owner to find their pet. Every pet will have a photo with their entry. Owners will also be able to add information on lost pets and the last known location. This database must be able to cross reference on each and every detail. Shelters and rescue groups must be able to pre-register before a disaster. Individuals who find an animal can register at that time. And, it will include a credit page with bios of the entire database team who gave their time and expertise to build this invaluable tool. I will need a team - the cream of the crop in database development. I know disaster response, I know what we need. But I need a database team, headed by someone who can lead and direct the team to make this the most powerful animal disaster tool ever. If you are willing to join this team, please contact me immediately. We need to get started because the disasters just keep happening...and the sooner we are ready, the better!!! I look forward to hearing from you! Jodi Witte President AnimalHelp Foundation Jodi Beck Witte currently works full time for AnimalHelp and is also a member of the Veterinary Medical Assistance Team (VMAT), part of the National Disaster Medical System, US Department of Homeland Security. In addition to her disaster response experience, she has worked as a veterinary technician in both small animal practice and at a zoo, and has done wildlife rehabilitation for many years. She is certified in Chemical Immobilization/Remote Capture, Large Animal Rescue, and is certified as a Weapons of Mass Destruction Specialist.
3 tables delete query. mysql geek need :)   (180 Views)
I have 3 tables! Need a MYSQL delete query to delete rows in 3 tables at same time... table1: log_visit 1) Need to DELETE from log_visit WHERE counter > 5 id | visit | visitor | counter 1 - 10 - qwerty | 15 2 - 11 - qwerty | 10 3 - 12 - azerty | 8 4 - 13 - azerty | 1 it will delete row 1 and 2 table2: log_link_visit_action 2) need to get visit and visitor rows deleted from table1 and delete in table2, in this case [row 1-table1] DELETE from log_link_visit_action WHERE visit = 10 AND visitor= querty [row 2-table1] DELETE from log_link_visit_action WHERE visit = 11 AND visitor=querty [row 3-table1] DELETE from log_link_visit_action WHERE visit = 12 AND visitor=azerty id | visit | visitor | url 15 - 8 - dodid - 16 - 10 - qwerty - 17 - 11 - qwerty - 18 - 15 - kaz - it will delete row 16 and 17 table3: log_action 3) Need to delete all url deleted in table2, in this case [row 17-table2] DELETE from log_action WHERE idaction = '' [row 18-table2] DELETE from log_action WHERE idaction = '' id | idaction 1 - 2 - 3 - it will delete row 1 and 3 Any possible help joining delete queries in one query would be extremely helpful Many
Join sintax help - not even know if I really need a join - please advice. :)   (182 Views)
all, This is a common scenario but I'm struggling here. I have 3 tables. The middle one relates table 1 and 3 by having a Fk from both tables. I would like to list some data from table 1 and same data from the table 3. But that data should be organized by ordering a given column in table 2. Can I have an example of the above, so that I can study and try to apply it to my code If you prefer, I can well provide the create tables here, I was just thinking that, like this, I can properly learn. K.
subquery max group needs and additional where clause   (215 Views)
I have a discussion forum that I would like to filter approved posts. I would like to know how to select the max(datetime) but only if the max has an approved value of 1. The only way I can think to do this is adding a where clause of approved = 1 but this will not work with a group by. Any suggestions Code MySQL: select * from questions where datetime in (select max(datetime) from questions group by id )
need help with simplifyling mysql queries.   (263 Views)
ok im really not sure what this is called so im making a new thread. in my current website i need to select photos using 3 different mysql calls, is there a way to simplify them into one mysql call there are 3 types of photos i need selected and they are related to the current photo and "report" thing, so far i have been doing it this way: Code MySQL: "SELECT id FROM photos WHERE seq > :seq and reportid=:reportid and seq NOT LIKE '0' ORDER BY seq ASC LIMIT 1"; "SELECT id FROM photos WHERE id < :fotoid and reportid=:reportid ORDER BY id ASC LIMIT 1"; "SELECT id FROM photos WHERE seq=( SELECT MAX(seq) FROM photos WHERE reportid=:reportid) and reportid=:reportid ORDER BY ID ASC LIMIT 1"; but now i started wondering if its a bit excessive and could be possibly simplified into 1 mysql query
CTE /w Multiple JOIN needs   (114 Views)
I'm using CTEs along with a JOIN to pull personnel information. This works fine. Now, I want to try to pull more information from another table, "Phones". Phones contains multiple phone numbers, of multiple types, for personnel. Each person's phone are also ranked in order of importance and they can have multiple phones of the same type (cell, home, work, etc). Phones PhoneID PersonnelID PhoneType PhoneNumber SequenceNo Now, what I'm trying to do is continue pulling all Personnel, but also the highest phone number of a particular type (optimally in the same SP). What I have so far: Code SQL: CREATE PROCEDURE dbo.Personnel_GetContactHelpSummariesByStartingNodeID ( @StartingNodeID INT, @PhoneType CHAR(3) ) AS WITH Nodes (NodeID, ParentNodeID, SequencePath) AS ( SELECT NodeID, ParentNodeID, SequencePath FROM Personnel_CommandHierarchy WHERE NodeID = @StartingNodeID UNION ALL SELECT CH.NodeID, CH.ParentNodeID, CH.SequencePath FROM Personnel_CommandHierarchy CH JOIN Nodes R ON R.NodeID = CH.ParentNodeID ) SELECT Personnel_Personnel.PersonnelID, Personnel_Personnel.FirstName, Personnel_Personnel.MiddleInitial, Personnel_Personnel.LastName, Personnel_Personnel.SuffixCode, Personnel_Personnel.CommandHierarchyNodeID, Personnel_Personnel.ServiceMember, Personnel_Phones.PhoneNumber, Personnel_CivilianPersonnel.CivilianClassificationCode, Personnel_CivilianPersonnel.CivilianRankCode, Personnel_ServicePersonnel.ServiceRankCode FROM Nodes INNER JOIN Personnel_Personnel ON Nodes.NodeID = Personnel_Personnel.CommandHierarchyNodeID LEFT OUTER JOIN Personnel_Phones ON Personnel_Personnel.PersonnelID = Personnel_Phones.PersonnelID INNER JOIN Personnel_CivilianPersonnel ON Personnel_CivilianPersonnel.PersonnelID = Personnel_Personnel.PersonnelID INNER JOIN Personnel_ServicePersonnel ON Personnel_ServicePersonnel.PersonnelID = Personnel_Personnel.PersonnelID WHERE Personnel_Personnel.CommandHierarchyNodeID = Nodes.NodeID AND Personnel_Personnel.Archived = 0 ORDER BY Nodes.SequencePath, Personnel_Personnel.LastName, Personnel_Personnel.FirstName RETURN Since I'm already using the WHERE clause to join my Personnel table to the table created by the CTE, I'm not sure where I can indicate that I only want the top-ranked phone number of a particular type.
simple database. just need some pointers.   (145 Views)
I there. I am currently working on a clients site that i think would benefit from a simple database. The site is a semi truck dealer all i would need is some way to display a list of trucks with pictures and a brief description. From there be able to click on a specific truck and view a details page with an expanded view of the truck, a bigger picture and more details. The most important thing is that what ever the information comes from the client needs to be able to update it him self. I have worked with mySQL and some php but really have almost no experience in those or any kind of CMS. can anyone point me in the direction of a tutorial or something that could help me learn how to make or adapt something to my needs.
need help with a query   (152 Views)
The relevant cols in my table are these. |Session_ID | referer | domain_viewed | (please don't worry about the upper and lower case. this is a test/practice db and I have learned about that issue. ;o I am trying to find out the total number of unique viewers (sessions), who were refered by referer AND who visited domain_viewed My query is like this but it gives seems to give me the total number of visitors referred irrespective of session. Code mysql: SELECT count(*) from tbl_Statistics where referer = '' and domain_viewed = '' group by Session_ID How can I make sure that it returns say a composite result of 4 sessions :: 650 page views (total across those sessions) bazz
Help needed for query.   (177 Views)
Everybody, i want to know about how to retrieve value order by numeric and alphabetic words in one query e.g:- I have value in db like 1180A,1180B,1180C so how to get 1180A first then 1180B then 1180C