Order by count


I have a query that orders results by the count of another table:

"SELECT table1.*, COUNT(table2.id) AS rev_count FROM table1
LEFT JOIN table2 ON table1.name = table2.name
WHERE table1.active = 1 AND table1.city='$city'
GROUP BY table1.name
ORDER BY rev_count DESC";

How do you add a condition (ie WHERE active=1) to just the "COUNT(table2.id) AS rev_count" part of the query
Posted On: Monday 31st of December 2012 01:05:26 AM Total Views:  337
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




2 tables query - ordering

Im not sure if this is something that can be done in 1 query or not. In leymens terms this is what I have. 2 database tables: users -user_id -user_name and Images -user_id -image_id -image_name -image_datePosted now what I want to do is to grab all users and order them by who posted an image last. is that something I can do
VIEWS ON THIS POST

67

Posted on:

Thursday 25th October 2012
View Replies!

Select with conditional order

Hi all, I need your help. I need order my mysql table with: 1) field `P` DESC; 2) field `Date_A` ASC; 3) field `Date_B` ASC, if field `Date_B` IS NOT NULL. I try this query, but I have error: PHP Code: SELECT`P` \t\t\t,`Date_A` \t\t\t,`Date_B` \t\t\t\tFROMtable ORDERBY \t\t\t\t`P`DESC \t\t\t,`Date_A`ASC \t\t\t\t\tCASEWHEN`Date_B`ISNOTNULL \t\t\t\t\tTHENORDERBY`Date_B`ASC Can you help me , Originally Posted by viki1967 I need see the rows with date_B IS NULL, but before Date_B is NOT NULL... Code: ORDER BY p DESC , Date_A ASC , Date_B ASC
VIEWS ON THIS POST

75

Posted on:

Thursday 25th October 2012
View Replies!

case and order by issue

I am trying to use the results of a case statment as the ordering mechanism in my query. it errors saying no such column as 'stuff'. what is I doing wrong, please Please note that this sequencing requirement is a different one from that being done by sequence_number column. Code MySQL: select fd.file_name_other_language , fd.file_heading , fd.file_text , case when fd.text_or_a_list = 'text' then 1 else 0 end as stuff , fs.sequence_number , fi.image from file_data AS fd inner join file_sequencing AS fs on fs.file_id = fd.file_id left outer join file_images AS fi on fi.id = fd.id where fs.business_id = 504 and fs.file_name_in_home_language = 'Home' and fs.live_from = ( select max(b.live_from) from file_sequencing as b where b.business_id = fs.business_id and b.live_from
VIEWS ON THIS POST

94

Posted on:

Thursday 25th October 2012
View Replies!

Best way to store ordering

When I'm storing a list of items, along with the ordering that's been manually assigned, I normally do it like so Code: SELECT animal, preference FROM favourite_animals ORDER BY preference ASC animal |preference --------------------------- dog | 1 goat | 2 monkey | 3 unicorn | 4 But it does get a bit cumbersome when changing the order, as you obviously have to also change the order of any items being displaced. But I really can't think of another way. It's not the worlds most pressing problem, but any ideas an another way to store this kind of data
VIEWS ON THIS POST

89

Posted on:

Thursday 25th October 2012
View Replies!

UNION and ordering by

I'm trying to order the data but it's not ordering them at all. When you do ASC or DESC the data does not change order. Code MySQL: (SELECT wall_replies.wall_replyid, wall_replies.wall_replyuserid, wall_replies.wall_replydate, wall_replies.type, customers.name, customers.lname FROM wall_replies INNER JOIN customers ON customers.id = wall_replies.wall_replyuserid WHERE wall_replies.wall_replyid IN (101, 93) AND wall_replies.wall_replyuserid !=70 AND wall_replies.wall_replydate > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) ORDER BY wall_replies.wall_replydate DESC) UNION (SELECT NULL, private_messages.fromID, private_messages.currentDate, private_messages.type, customers.name, customers.lname FROM private_messages INNER JOIN customers ON customers.id = private_messages.fromID WHERE toID = 70 AND private_messages.currentDate > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) ORDER BY private_messages.currentDate ASC) Can someone lend a hand please
VIEWS ON THIS POST

128

Posted on:

Thursday 25th October 2012
View Replies!

Fulltext search on multiple tables ordered by relavance

I have a website with a simple search field. A site wide search needs to be performed when the form is submitted. This may prove difficult due to the number of tables. Here is what I have started with, just to search one part of the site: Code: SELECT *, MATCH(title, intro, maincopy) AGAINST('Lifespan') AS score FROM whatwedo_pages WHERE MATCH(title, intro, maincopy) AGAINST('Lifespan') That works fine, but I need to search other tables with different field names at the same time. The tables and the columns which need searching are as follows: - services_pages title, intro, maincopy - workwithus_pages title, intro, maincopy - homes name, description - news title, intro, maintext Also, there is a table called 'regions' eg: North East, South West etc. The region_id is a foreign key in 'homes'. Therefore, if possible, the query also needs to search against region_name and grab the homes in that region if there is a match. Any ideas Any help much appreciated as always EDIT: OK, done a little bit more work and my query has progressed to this: Code: SELECT title, intro, maincopy, 'whatwedo.php', MATCH(title, intro, maincopy) AGAINST('$keyword') AS score FROM whatwedo_pages WHERE MATCH(title, intro, maincopy) AGAINST('$keyword') UNION ALL SELECT title, intro, maincopy, 'services.php', MATCH(title, intro, maincopy) AGAINST('$keyword') AS score FROM service_pages WHERE MATCH(title, intro, maincopy) AGAINST('$keyword') UNION ALL SELECT title, intro, maincopy, 'workwithus.php', MATCH(title, intro, maincopy) AGAINST('$keyword') AS score FROM workwithus_pages WHERE MATCH(title, intro, maincopy) AGAINST('$keyword') ORDER BY score DESC So still need to incoporate the other tables somehow and try and generate valid links to the content that is returned in the result :
VIEWS ON THIS POST

64

Posted on:

Thursday 25th October 2012
View Replies!

Moving a branch in a preordered hierarchy

, I've ran into problems trying to moving a branch in a lef-right preordered hierarchy. I'm trying to do this (modified pic from a SP article): I understand this task consists of two logical actions: 1) Change the left/right values of the record being moved and its children. The values will be changed by the width of the part of the recordset between the subject and the target. 2) Shift the left/right values of the records between the subject and its target by the width of the subject. The problem is that the first action will always affect the second (the action 1 will change the records that their L/R values will overlap with others). How can I bypass this
VIEWS ON THIS POST

59

Posted on:

Thursday 25th October 2012
View Replies!

best index for select with where/order/limit

This is an example of the table I am working on: Code: CREATE TABLE `db`.`messages` ( `unixtime` int( 10 ) NOT NULL default '0', `username` varchar( 20 ) NOT NULL default '', `sender` varchar( 20 ) NOT NULL default '', `content` mediumtext NOT NULL , KEY `username` ( `username` ) , KEY `unixtime` ( `unixtime` ) ) TYPE = MYISAM ; This is the kind of select that will be used a lot : Code: SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY unixtime DESC LIMIT $x,10 username has an index on it, because of the WHERE part. Unixtime has a time(); value in it wich I use to display the date, and because I also use it for ordering by date it also has an index. I dont think this current setup is wrong, but it could be improved, and I want to know if the following things are right : I could add an 'id' int (10) column, and set it to auto-increment and primary key and drop the key on the unixtime column. That way the messages will already be stored by date (new messages will always get a higher id value), and for mysql it will be easier to find the 10 posts that I want. I can not simply change 'unixtime' to a primary key, because on busy moments there is a real chance two users might send a message at the exact same second, resulting in a non unique value for that column. Code: SELECT unixtime,poster,content FROM messages WHERE username='$name' ORDER BY id DESC LIMIT $x,10 It will use the key of username to find all messages for this person, and it will then use the primary key to just select the 10 messages that are wanted. Those messages are already ordered by id (thus by time) when reading them so it will not need an extra sorting process unlike the ordering by unixtime wich probably requires all rows of that person to be read, and then all of them to be sorted to find the 10 that are wanted. So ordering by primary key would be faster and/or use less memory I could go even further and also drop the key on username, and instead have one primary key consisting of ('username','id') , with the id still an auto-incrementing INT. This way there is only one index that has to be loaded, wich can be used for both the WHERE and ORDER BY. The table would be like this then : Code: CREATE TABLE `db`.`messages2` ( `id` int( 10 ) NOT NULL AUTO_INCREMENT , `unixtime` int( 10 ) NOT NULL default '0', `username` varchar( 20 ) NOT NULL default '', `sender` varchar( 20 ) NOT NULL default '', `content` mediumtext NOT NULL , PRIMARY KEY ( `username` , `id` ) , ) TYPE = MYISAM ; I have done some testing , and used 'explain' too. With my first table it uses the username key and a filesort, on the second with the double primary key it reads a bit more rows, but its not using filesort anymore... so that should be an improvement I think it is, but would like some confirmation before I change it on dozen tables in the live database. And are there any other changes in index that would be even better
VIEWS ON THIS POST

81

Posted on:

Thursday 25th October 2012
View Replies!

Order By question (invdividual order not asc or desc)

Hi all, in an old customer db is a table which holds information to products. Each product has one category, which is in its' own column. Each category has its' own number. eg. Hardware = 0 Software = 1 Ink = 3 and so on. (about 20 different categories) Now the customer wants me to generate one large product table with all products from all categories, but ordered in this order: 3,0,2,15,7 ... Normally I'd just do an order by category asc or desc, but in this case I'd have to use an individual sort order. Can someone give me a hint on how to achieve this!
VIEWS ON THIS POST

65

Posted on:

Monday 29th October 2012
View Replies!

return only one instance of each ID # (was "SELECT query question")

So I have a table where one column is populated with item ID numbers. These numbers aren't unique so the same # can appear more than once in the column. What I want is to query the table so it returns only one instance of each ID #. I can't find exactly what I'm looking for, though. It would also be nice if it would give the number of instances the ID appears in other rows, if it does appear more than once. TIA
VIEWS ON THIS POST

66

Posted on:

Monday 29th October 2012
View Replies!

Select distinct rows from list

-- Table: categories | id | category | ------------------ 1 5 1 6 1 9 2 3 2 6 2 9 3 7 3 8 3 9 ____________________________ I have a table like the above. Is there a way to select the ids that has, for example, both the categories 6 and 9. The result I'm looking for would be: + id + ------- 1 2 and not + id + ------- 1 1 2 2
VIEWS ON THIS POST

149

Posted on:

Monday 29th October 2012
View Replies!

STR_TO_DATE() yielding null

As I understand it, STR_TO_DATE() should allow both formatting characters (i.e., '%Y') as well as literal characters ('/'). However, something doesn't seem to be working right for me. Here's an example: Code: SELECT STR_TO_DATE( 'Wednesday 11th March 2009 06:55:43 PM', '%W %D %M %Y %r' ) This works fine, because there are no literal character statements. Unfortunately, this is not how the dates are formatted in the database. They're formatted like this: Code: SELECT STR_TO_DATE( 'Wednesday 11th of March 2009 06:55:43 PM', '%W %D of %M %Y %r' ) As I understand it, this SHOULD work because the 'of' in the formatting string literally matches the 'of' in the date. It doesn't, it just gives me NULL. Am I doing something wrong here, or am I misunderstanding the manual's statement about literal characters
VIEWS ON THIS POST

96

Posted on:

Monday 29th October 2012
View Replies!

UTF-8 causing trouble :(

Hi , I need some help with the following issue : The accentuated characters of the content taken form the UTF-8 database gets garbled once displayed on my website. What I don't understand is that everything seems to be properly set : my.ini Code: [mysql] default-character-set = utf8 default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci init_connect = 'SET collation_connection = utf8_general_ci' init_connect = 'SET NAMES utf8' PHP header Code: header("Content-Type:text/html; charset=utf-8"); Metas Code: Table structure Code: CREATE TABLE `mod_geonames` ( `geoname_id` mediumint(9) NOT NULL, `name` varchar(200) default NULL, `latitude` decimal(10,7) default NULL, `longitude` decimal(10,7) default NULL, `country_code` varchar(2) default NULL, PRIMARY KEY (`geoname_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; So, what else could cause the problem I am at a total loss of ideas after extensive googling
VIEWS ON THIS POST

80

Posted on:

Monday 29th October 2012
View Replies!

SELECT DISTINCT but show all fields

I've googled the mess out of this. Found a few things that looked like they might work, but nothing did. I have a stat tracker program for my website that I wrote. Some of the fields include ip, agent, time, referrer url and destination url. I want to "SELECT DISTINCT `ipaddy` FROM `statstable`" However, I need it to show the rest of the fields associated with `statstable`. I was thinking this was going to be a winner, but still not yielding results... "SELECT * FROM `statstable` WHERE `ipaddy` = (SELECT DISTINCT `ipaddy` FROM `statstable`)"
VIEWS ON THIS POST

62

Posted on:

Monday 29th October 2012
View Replies!

Delete Syntax

What is the correct syntax to delete an item from the database where the field contains a number. My field has the number prefixed to something else, so I need to delete any of the items that contain this number in the database. Example below of 3 entries in the database and the fieldname has the number prefixed: Field Name = filename 1XD23456word.pdf 1XD23456word2.pdf 1XD23456item.doc I am not sure of what the WHERE clause would be
VIEWS ON THIS POST

37

Posted on:

Monday 29th October 2012
View Replies!

How to search for multiple keywords properly?

I know I need to do a full text search to return exact keyword matches but I am unsure how to structure the query for multiple kewords (all keywords are optional). Basically my form has checkboxes for option 1 option 2 option 3 and if the user checks more than one option it should return all results that contain at least one of those options. Can someone give me an example query please This doesn't seem to work very well :-( PHP Code: SELECT*,MATCH(Headline,Blurb)AGAINST('members,chamber,the')ASScoreFROMArticlesWHEREMATCH(Headline,Blurb)AGAINST('members,chamber,the') Edit: actually it does work but I need to be able to search for keywords down to two letters. Maybe I should stick to a LIKE search
VIEWS ON THIS POST

85

Posted on:

Monday 29th October 2012
View Replies!

3 Tables - Inner Join

I need to join 3 tables, Code: orders_order as t1, orders_customer as t2, orders_shipping as t3 all three tables have a common column cust_id and then I would like to seach the defined columns for terms. I have the php form done, however, I am having some trouble with the MySQL Query, this is what I have so far: Code: SELECT * FROM orders_order as t1 INNER JOIN orders_customer as t2 ON t1.cust_id=t2.cust_id AND JOIN orders_shipping as t3 ON t2.cust_id=t3.cust_id WHERE t1.cust_first LIKE '$searchterm' OR t1.cust_last LIKE '$searchterm' OR t1.cust_email LIKE '$searchterm' I would appreciate any help. Thank You. Simon.
VIEWS ON THIS POST

52

Posted on:

Monday 29th October 2012
View Replies!

calculating employee commission

I have the following tables: Table1: Emp %Sales 1 2.3 Table2: Project %Sales CommissionAmt A 2.2 1.50 A 2.4 1.75 Employee1 has 2.3 % sales. I have to figure out from Table 2 which commission amount to apply. Because emp1 has 2.3 I need to apply $1.50. How do I do this in a query
VIEWS ON THIS POST

51

Posted on:

Wednesday 7th November 2012
View Replies!

How to create an array in a paramater in sql stored procedure

I am creating a stored procedure that asks the user for a list of customer segmentations(ie. G, P, S). How can I create a paramater that will allow multiple values(like an array) I want to use the logical operator IN(), but is won't work with a list. Here's part of my stored procedure: @MONTH CHAR(2), @YEAR CHAR(4), @cseg \t\t - How do I handle this\t\t
VIEWS ON THIS POST

71

Posted on:

Saturday 10th November 2012
View Replies!

ADO Connection to db3 database

Would appreciate any help/examples of a code-snippet that will establish an ADO connection to a db3 (Sql-lite) database table and run a simple select query. I'm using VB6 as my dev environment. Many
VIEWS ON THIS POST

80

Posted on:

Sunday 11th November 2012
View Replies!

Sql date help

I want to select only fields with todays date, I tried this but it dosen't work. The data is stored in the DB like this. 2003-05-01 00:00:00.000 Select * from table WHERE DateField = '" & Date() & "'
VIEWS ON THIS POST

87

Posted on:

Sunday 11th November 2012
View Replies!

Clean a transaction log file

Originally posted by : Alex (AlexLut@aol.com),I need to clear a log file in SQL Server 2000. I know that I can shrink and remove any unused space and etc. My problem is that a log file has a lot of old transactions and I would like to not only shrink the file but also clean it. Let's say my file right now is 20mb and I want to make it back to 2mb. Thank you in advance.
VIEWS ON THIS POST

55

Posted on:

Sunday 11th November 2012
View Replies!

SQL Query for Team hierarchy

I have following hierarchy: Product Manager -> Team Mangers (works under PM) -> MDMs (works under TMs) -> Reps (works under MDMs) but not limited to these. we can have more later on.(E.g: Reps can give money to their team as well...) Every Product Manager, Team Manger, MDM will have diff team. PM allocates money to TMs. TMs ocates money to MDMs ...and so on. I have a query that Sum up total amount of money spent by one person (values coming from diff tables), in this case I just pass entered @username. I have to write ASP code or a SQL Query in such a way that when user inputs Product Manager in text box and submit, then my page will calculate all the money spent by his Team Mangers + their MDMs + their Reps... If they enter Team Manger then page will calculate all the money spent by his MDMs + their Reps... I can do this in case if I know that the hierarchy is fixed but I cannot set limitation for my calculations. Hierarchy can increase, E.g: Reps can give money to their team as well... Please Help..
VIEWS ON THIS POST

45

Posted on:

Monday 12th November 2012
View Replies!

String Manipulation in Query Result

I have the following rows in a table, i have to write a query which returns results as shown Table Values Result -------------- ---------------- 100 --> 100 100N --> 100N 100NCA ---> 100N(CA) 200J ---> 2000J 2000CA ---> 2000(CA) Logic: Check last two characters of string, if both are alphabets, then add ( ) before and after and return...for anything else just return original How can i write such a query
VIEWS ON THIS POST

36

Posted on:

Monday 12th November 2012
View Replies!

Multiple values in queries - please help!

A couple of questions... I'm using ASP VBScript with MS Access. I have a company table of food producers. Each producer belongs to various product categories, not just one. I need to create a "search" form where the user can ctrl+click many categories from a listbox and then receive all the companies that belong to those categories, on the response page. I've tried it, but I get this error msg: "[Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression 'CompanysCategories.SubCategoryID = 11, 3'." This is the results of clicking two categories from the listbox. And second - what do I need to do when I want to INSERT a company INTO the database with multiple product categories (= many selections from a listbox) The tables I've got are basically like this: Companies (CompanyID, and the details etc.) CompanysCategories (CCid, CompanyID, SubCategoryID) SubCategories (SubCategoryID, and the details etc.) The SQL Query is here: (note! I've also got other fields in the search form, but this is just for testing the listbox thing) Code: SELECT CompanysCategories.CompanyID, CompanysCategories.SubCategoryID, etc FROM CompanysCategories INNER JOIN Companies ON CompanysCategories.CompanyID = Companies.CompanyID WHERE CompanysCategories.SubCategoryID = fromform the fromform is obviously Request.Form("prodlistbox").
VIEWS ON THIS POST

108

Posted on:

Monday 19th November 2012
View Replies!

DISTINCT truncating a memo field

, I've created an .asp page that returns results from a memo field...I need to use Select DISTINCT, but when I do, the memo field gets truncated. When I remove the DISTINCT, the full memo field is displayed, but of course, I get too many records. Has anyone encountered this before
VIEWS ON THIS POST

46

Posted on:

Tuesday 20th November 2012
View Replies!

Any difference between Win2000 Server & WinXP?

Hi all, I have the following codes working fine on one PC running Win2000 Server, but fails on another PC running WinXP. When I check the system log, it tells me the problem is on line 8, that is :- cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Development\Family\Testing\ElouiseAccess oryProductList.mdb;" Is there any difference between Win2000 Server and WinXP Thank you. Code:
VIEWS ON THIS POST

53

Posted on:

Tuesday 20th November 2012
View Replies!

Running problem!

i have instaleld new copy of oracle 8 i , after the installation i login in oracle with scott & tiger it was working , when i strated again and it say ORACLE NOT AVAILABLE AND NOT LOGGIN IN . please help me how to sort out this prob or any one can tell me dirct link to download oracle from web .
VIEWS ON THIS POST

70

Posted on:

Tuesday 20th November 2012
View Replies!

Login script problems

Hi all, I'm trying to create a login page using the below code that is activated from a form submit: Code: I'm new to this and can't seem to see whats wrong. Instead of giving an error or redirecting it just comes up with a HTTP 500 error.
VIEWS ON THIS POST

62

Posted on:

Tuesday 20th November 2012
View Replies!

DSN, Storing variable permanently

Originally posted by : thunderain (lbrennan1@sympatico.ca)I have Windows 98. My two databases is set up in Control Panel, ODBC Data Sources, User DSNMy program will be using two databases. I am using a drop down menu for the user to pick which database to work with. Once the database is picked, they will be using it on all other pages. After selecting the database, I need to store the variable somewhere to access it on all other pages. I have used global.asa to store variables that are hardcoded in and that works find. Reading up on it, i don't see how to send a variable to global.asa to store it, or for that matter if you can.Can you send a varible to global.asa to be stored and used in other pagesIf yes, howShould I put it in an asp include, I am going in that direction Here is my code.------selectdatabase.asp-------- Database: GateKeeper GKAdmin ----Choose One------ ! ----------database-var.asp----------1/ It seens to be selecting the database ok. There are no errers, but I am not getting anything from Response.write username2/ When the database is selected, how do i store it permanently while the user goes to other pages and call it from other pagesThank you, Larry
VIEWS ON THIS POST

51

Posted on:

Tuesday 20th November 2012
View Replies!

Problems calling stored proc's through an ole-db drivers.

Originally posted by : Gerry...Problems calling stored proc's through an ole-db drivers. Gives me errors..
VIEWS ON THIS POST

71

Posted on:

Tuesday 20th November 2012
View Replies!

Invisible Views

I've just imported lots of data into my database, but cannot see any of my views! However the site works fine and my ASP code can see them. Why is this happening
VIEWS ON THIS POST

45

Posted on:

Monday 26th November 2012
View Replies!

sql syntax error.. dont know why.. help

this is my stored procedure.. when i check the syntax in the stored procedure window it tells me syntax ok... Code: CREATE PROCEDURE sonia @OptionSelected varchar (500) AS DECLARE @SQL varchar(600) DECLARE @Live varchar(40) SET @Live = Convert(varchar,GETDATE(),110) SET @SQL = 'CREATE VIEW (@Live) AS SELECT dbo._students.*, ( ' + @OptionSelected + ')as test, dbo.studentSurveyValues.* FROM dbo._students INNER JOIN dbo.studentSurveyValues ON dbo._students.sid = dbo.studentSurveyValues.studentid' EXEC(@SQL) GO when i run it in query analyzer with the syntax below Code: execute sonia 'modegender' i get this error message in the query analyzer pane.. Code: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@Live'. as you can see i am trying to create a view and i want to add the date to the name can someone tell me why
VIEWS ON THIS POST

139

Posted on:

Monday 26th November 2012
View Replies!

To add date in mysql database

Hi , I have an asp page which is used to add a record to the database.When adding it to the database it shud add the date of the day without entering it. I could achieve this in MSAccess but im not able to do it in mysql.In MSAccess i just add the Now() function. This is My ASP code:- opener.location.reload() close
VIEWS ON THIS POST

57

Posted on:

Monday 26th November 2012
View Replies!

PostgreSQL - what are the best resources on the net?

PostgreSQL -what are the best resources on the net Can anybody submit good links, tutorials, links to references, and recommended books
VIEWS ON THIS POST

114

Posted on:

Sunday 30th December 2012
View Replies!

Update MySQL table from another table

I inherited a database from an old project and would like to make the data more robust. Currently it stores a group of Jobs and each job has a customer listed, however the customer name is stored as a string value and there is a separate "Customer" table that I would prefer to instead of a join of the customer number rather than their name as this causes the data to not be as clean as the user could enter in whatever they like so we could have jobs listed under "ABC Widgets" and "ABC Widgets, Co" and they would appear to be 2 different customers. I've created a new field in the Jobs table to store the customer ID from the customer table, what I am now trying to figure out is how best to build the query to populate this new field for all the existing jobs (I'll be rewriting the other parts of the code to utilize customer numbers for additions/updates). So from my PHP Admin what would the query be to place the customer ID from the Customers table into the Jobs table UPDATE 'Jobs' SET CustNum = Customer.ID WHERE Customer.Name = Jobs.CustomerName is where I am starting, but I think I am missing something. Most likely a JOIN (which I am still having some trouble fully grasping). Or is a JOIN even required since I can just call from the different tables I want this to iterate through all the jobs and if it can't find a match, then just leave the CustNum field blank so I can manually resolve those. Greg
VIEWS ON THIS POST

66

Posted on:

Monday 31st December 2012
View Replies!