Adding Columns To Multiple Tables?
I have tables like "threadid1" "threadid2" and so on. Is it possible I can add a column to say every table that starts with "threadid"?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Adding Multiple Columns
Schema on a table has changed between versions of an application I use. I'm planning on migrating the existing table (member table as is, data and schema) and then wish to add the new columns after an existing column (eg password) and am wondering do I have to add each column at a time with multiple sql statements or can I add them all in one sql? Additional columns: `invisible` set('1','0') default '0', `pmfolders` text NOT NULL, `saveogpm` char(3) NOT NULL default '', `emailonpm` char(3) NOT NULL default '', `useoldpm` char(3) NOT NULL default '', `webcam` varchar(75) default NULL,
View Replies !
View Related
Grouped Columns From Multiple Tables
I am trying to write a query to get the total records per user id from 2 different tables. The tables are posts and topics. Each table has a numeric user_id and a numeric forum_id that these records relate to. I'm trying to write a forum usage report with output like this: Code:
View Replies !
View Related
SELECT From Multiple Tables With Different Columns
I am trying to combine 2 SELECT queries so that I can order them to display in proper date order. The queries I am using are below. My question is, how can I join together these queries so that they will mix the news and events together to display them in order instead of displaying the news items first in date order then displaying the events beneath them in their order. $query = "SELECT * FROM news_articles WHERE (date_full >= '$days_back' AND date_full <= '$days_ahead') AND (news_type = '$news_query') ORDER BY 'date_full' DESC"; $query = "SELECT calendar.* FROM calendar LEFT JOIN events_type ON events_type.event_id = calendar.id WHERE calendar.date_full >= '$eventdate_time' AND date_full <= '$days_time_ahead' AND STATUS = 'active' AND (events_type.type_title = '$events_query') ORDER BY 'date_full' ASC"; Anyone got any ideas?
View Replies !
View Related
Pattern Matching Across Multiple Tables In Different Columns
Im having trouble getting my head around how to construct a query to address this problem im having. The aim is to generate a search facility for specific columns across a number of tables... I have the following tables: atms atm_actions atm_checks atm_comments atm_phases atm_tests All have the ID from atms in common, in the other tables it is known as subsystem_id. Within each table there is a specific column I want to be able to search, they are given below for each table (Note: the other column doesnt exist in the table, e.g. atms does not have a text column, and atm_actions does not have a name column): atms - name atm_actions - text atm_checks - text atm_comments - text atm_phases - name atm_tests - name The search available to the user should involve 3 criteria, the first is their search criteria (i.e. text input), the second is the subsystem/s (Id numbers), the 3rd is atm section (corresponds to each table, e.g. atm_checks is the checks section). So for example, they may want to search for "hello world", in the atms, comments and phases sections, where the id/subsystem_ids are 12, 60 & 47. Code:
View Replies !
View Related
Adding Columns And Data
I have two tables. I wish to add a new column to the beginning of each table and to fill them with incremental values e.g. if one of the tables looks like this: 23 56 45 87 21 67 34 09 I'd like it to be changed into: 1 23 56 2 45 87 3 21 67 4 34 09 .
View Replies !
View Related
Automatically Adding Columns
I have a table that looks like this (commas indicate columns: player_id|player_name ------------------------- 1|Mary 2|Dan 3|Joe I would like to assign each player to a team, but each player can belong to more than one team, so for example: player_id|player_name|team_1|team_2|team_3 ---------------------------------------------------------- 1|Mary|LA |Denver|Boston 2|Dan |LA |null|null 3|Joe |Miami|New York|null How can I automatically add columns to my table depending on how many teams each player belongs to?
View Replies !
View Related
Adding Aggregate Functions With Columns
Does anyone know how to add the values returned from aggregate functions to the values in the columns. I have to compute the average price of a product given from a list of products which have their own prices, and display the products information with its price, the average price including that product, and the average price not including that product. so far it got this :----: select (select avg(pprice) from vendor) , (select max(pprice) from vendor), (select min(pprice) from vendor), avg(pprice) , vname from vendor a group by vname; which calculates the average but then i got lost.....
View Replies !
View Related
Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.
View Replies !
View Related
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
View Replies !
View Related
Adding Multiple Years
I have set up tables that increase input dates by either a month, quarterly, half yearly or annual using M,Q,H or Y in the default space, and ('M','Q','H','A') in the length/values space.However, I want to be able to add 5 years to one of the fields in the table.
View Replies !
View Related
Adding Multiple Rows Into A Table At Once
I have to the stage of being able to easily(?) add new rows to my tables using the command insert into zone_vs_weight (low_mass, high_mass, zoneA, zoneB) (1, 250, 4.50, 6.35); It seems to work well for adding single rows but can we use it to add multiple rows i.e. insert into zone_vs_weight (low_mass, high_mass, zoneA, zoneB) (1, 250, 4.50, 6.35) (low_mass, high_mass, zoneA, zoneB) (251, 500, 6.50, 8.50) .........; Is there another command apart from the input file insertion or using PHP? I have been using K. Yank's fine book 'PHP & MySQL' as a good primer but it doesn't seem to mention whether it is feasible.
View Replies !
View Related
Adding Two Tables
I am actually developing an application where Data from one table is added to the other.The thing is, both Table A and Table B are having the same attributes (ie both are same tables with different values) for eg ....
View Replies !
View Related
Adding Tables To Database
Probably a very simple solution to this question, but this newb is having difficulties adding tables to the first database created "client001". I just set up MySQL and when I try to add tables to the database named client001, the tables are being added to the mysql database ... Why is this happening? How can I add table "instructors" to "client001"?
View Replies !
View Related
STD Of Multiple Columns
I want to calculate the standard deviation of data that are in multiple columns. I know how to calculate the STD of 1 column (e.g. X1 of table_X) using: SELECT STD(X1) FROM table_X; but I want to calculate now the STD of the union of data of columns (e.g. X1, X2, ..., X100 of table_X). Does anyone has any suggestion on how to do that? I hoped something as SELECT STD(X1,X2,...,X100) FROM table_X existed, but apparently it does not.
View Replies !
View Related
Where In Multiple Columns
I am attempting to search multiple columns for the same foriegn key. I have the following columns in my catalog_images table: nID, strFileName, nCategoryA, nCategoryB, nCategoryC, nCategoryD, nCategoryE and I want to select strFileName from catalog_images where ANY of the following matches occur: nCategoryA="1", nCategoryB="1", nCategoryC="1", nCategoryD="1", nCategoryE="1" I can't figure out my query string. Any suggestions?
View Replies !
View Related
Use 'not In' For Multiple Columns
I have the following query: delete from table_a where concat(column_a, '/', column_b) not in (select concat(column_c, '/', column_d) from table_b) ; as you can see, I am using concat as a work-around. is there a better way to do this, something that looks like this:?: where (column_a, column_b not in (select column_c, column_d from table_b))
View Replies !
View Related
WHERE With Multiple Columns
I have three tables: person with per_id, event with eve_id and than third table per_eve with the two columns per_id and eve_id. I now need to check, if a row exists in the per_eve-table for a certain event and person. If there is an occurence, than I don't want that row in my overallresult. Which person and event to take I get from other conditions. To check for non-existence I thought something like "WHERE (person.per_id, event.eve_id) != (per_eve.per_id, per_eve.eve_id)" could give me all rows not to drop, as I need the combination of both values. But it does not work, I get an errormessage after the first comma.
View Replies !
View Related
Adding Data Into Joined Tables
I recently started playing around with joining tables together to be able to retrieve data from them using one query. However, as embarrassing as it may be, I can't figure out how to add data to multiple tables. For the sake of practice I creates three tables: table1 Code: ID Name table2 Code: ID Age table3 Code: ID Gender PHP Code:  $query = mysql_query("                     SELECT name, age, gender FROM join1, join2, join3 WHERE join1.id = join2.id AND join1.id = join3.id         ") or die(mysql_error());Â
View Replies !
View Related
DISTINCT With Multiple Columns
I need to select unique combinations of 4 columns from one table and insert them into a new table but I can't think of any way of finding unique combinations of more than 1 column since SELECT DISTINCT only works on single columns.
View Replies !
View Related
Include Multiple Columns
I have a table with these fields: id - account - ip - data what I want to do is select only unique ip, but also include the 'data' field in the result so that I can print the unique ip along with the associated data .. is this possible?i tried 'select distinct ip,data from table' but that returns duplicate ips.
View Replies !
View Related
UNIQUE As Multiple Columns
its possible to define multiple columns as being UNIQUE - not the value of either column, but the two together. For example, you're storing people's names and addresses in a table. Two or more people could have the same lastname, and more than one person will surely have the same firstname. However, no two people can share the same lastname AND firstname. Is there a way to set this constraint?
View Replies !
View Related
Update Multiple Columns
I get an error on this command: UPDATE patchMapTable SET row=0,column=0,patchNumber=23 "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'column=0,patchNumber=23' at line 1" row, column, and patchNumber are columns in patchMapTable all 3 are int row and column are primary keys
View Replies !
View Related
Group By Multiple Columns
I have a "users" table as follows: first_namelast_name JohnStuart JohnDell JohnStuart BillWhite If I want to know how many people of each first name there are, I do the following: select first_name, count(*) AS Number from users GROUP BY first_name; I am getting: John|3 Bill|1 But how can I get the same for both first and last name? I just don't want to count John Stuart twise. I tried: select first_name,last_name count(*) AS Number from users GROUP BY first_name,last_name; This gives me the same result as before, just adding column of first name (the same old last name grouping). I want to get: John | Stuart |2 John | Dell |1 Bill | White |1 Do I need to add multiple column index for multiple column Group By?
View Replies !
View Related
Selecting Min,max,avg Of Multiple Columns
I have to select "min, max, avg, sum( if( somecolumn = 123, 1, 0 ) )" from 5 to 15 columns so far, each query is for different table. Is there any other way of doing that except writing all the columns to query ? Of course that job does the script, but still, query looks miles long. Example of table: CREATE TABLE `example` ( `ID` int(255) NOT NULL auto_increment, `some_int` int(255) NOT NULL default '0', `some_int2` int(4) NOT NULL default '0', `create_time` date NOT NULL default '0000-00-00', `some_int3` decimal(65,2) default NULL, `some_int4` int(255) NOT NULL, `some_int5` int(255) NOT NULL, `a_somecolumn` decimal(65,2) default NULL, `a_somecolumn2` decimal(65,2) default NULL, `a_somecolumn3` decimal(65,2) default NULL, `a_somecolumn4` decimal(65,2) default NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; So, I have to select these "min,max,avg,sum" from all the a_somecolumn.
View Replies !
View Related
Max() With Multiple Columns W/o Groupby
I have a table with two columns ts(timestamp) and tempc(int). From this I need to pull the both of these values but where tempc is max value. Individually I can this: select max(tempc) from templog; and it works fine. However when I try this it fails: select ts, tempc from templog where tempc = (select max(tempc) from templog); I'm sure this is something inanely obvious, but I cannot seem to figure it out... Any ideas?
View Replies !
View Related
Select Sum(x) >0 With Multiple Columns
I am writing a script to show the average value of a question in a submitted survey form. I have 14 questions, which can have the value 1-10, if the user submitts "I don't know" it returns the value 0. When i calculate the average it includes the value 0 but still divides with all the submitted answers (see code). How can i make a select statement which only includes values > 0? Current select statement: $select = "SELECT SUM(S1), SUM(S2), SUM(S3), SUM(S4), SUM(S5), SUM(S6) , SUM(S7), SUM(S8), SUM(S9), SUM(S10), SUM(S11) , SUM(S12), SUM(S13), SUM(S14) FROM Evaluering"; I want it to do something like this : $select = "SELECT SUM(S1), SUM(S2), SUM(S3), SUM(S4), SUM(S5), SUM(S6) , SUM(S7), SUM(S8), SUM(S9), SUM(S10), SUM(S11) , SUM(S12), SUM(S13), SUM(S14) WHERE S1,S2,S3,S4,S5,S6,S7,S8,S9,S10,S11,S12,S13,S14 > 0 FROM Evaluering"; Any suggestions?
View Replies !
View Related
Multiple Records As Columns
I have a table of Sessions and a table of Speakers. Each Session could have several Speakers and each Speaker could speak on several Sessions. I have a link table containing just SessionID's and SpeakerID's. All this is working fine on a successful website. I am now trying to write a query that will return records for each Session with each of the Speakers' details for that Session. Each record could have a different number of fields according to how many Speakers were assigned to that Session - this is the bit that makes it seem difficult.
View Replies !
View Related
Search Multiple Columns
I would like to search for a keyword '%schultze%', and I would like the search to include more than one column. I was experimenting with this: SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`title`) AGAINST ('%schultze%'); But I would get funny errors complaining about not being able to find a FULLTEXT index. I imagine this is something that has to be built before the search happens. As the database is changing all the time, I would want this to be built once a day. Perhaps I could stick a batch file in the xinetd that would take care of that. Ideally I would like to search over 10 columns, (with the constraint that the owner equals 128) that are varchar and text, all utf8_general_ci. I suppose this would look something like: SELECT * FROM `listItem` WHERE `owner` = 128 AND MATCH (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) AGAINST ('%schultze%'); Or perhaps I'm barking up the wrong tree here and I could do a search that was like this: SELECT * FROM `listItem` WHERE `owner` = 128 AND (`referenceNumber`, `title`, `menus`, `added`, `format`, `ratio`, `source`, `description`, `genre`, `rating`) LIKE ('%schultze%'); But where mysql would know each column was OR, where '%schultze%' matches for `referenceNumber`OR `title` and so on,
View Replies !
View Related
Searching Across Multiple Columns
Is it possible to search for a word across multiple columns in a table? For example: Table contains columns "Year" "Make" "Model" "Color" (etc etc.....) Then, using a user entered keyword, generate a query that checks the word against all ( or a list ) of columns in that table? Something like.... SELECT * from Table where * LIKE '%keyword%'
View Replies !
View Related
Multiple Non-Unique Columns
I'm trying to store the call detail reports from a phone system to a database. I've successfully been able to read the CSV files it gives me into an InnoDB table using PHP. I just realized that the system does not give a single unique key in the raw data, but that two columns together will identify a unique call entry. The columns if interest are the timestamp (two people could conceivably make a call at the same time) and a call identifier (which seems to be unique over a month or so, then the system starts recycling the call identifier). With these two columns you can identify a unique call over ANY given time. I'm not sure how to use that fact to assure I'm not inserting the same data if I were to read the same CSV file twice.
View Replies !
View Related
ORDER BY Multiple Columns
I am trying to order by multiple columns, here is my example and what I want to output Code PHP ORDER BY lastName ASC, State ASC Output Quote: James Anna, Alabama Ben Bloggs, Alabama Chris Cats, Alabama Mark Aims, Delaware Hopefully you can see what I am trying to achieve, but I don't get there all it does is sort the result by lastName and ignores the states...
View Replies !
View Related
Index Multiple Columns
I have a table that looks like this: PlayerItems -------------------------------- | PlayerID | ItemID | ItemCount | -------------------------------- | 1 | 1 | 5 | | 1 | 3 | 1 | | 2 | 1 | 2 | -------------------------------- What index types should I use for PlayerID and ItemID so that it will throw a duplicate key error if a row is inserted that matches the playerID and the itemID. Basically im looking to run a query like this: Insert into playeritems values (1, 1, 1) on duplicate key update itemcount=itemcount+1 How should I set the indexes so it will throw a key error only whe nthe player id and item id matches.
View Replies !
View Related
The DISTINCT Command With Multiple Columns
What i want to do is use a query that will return one of each type regardless of the other in there, for example. i have a list of unit unit 3131,3132,3133 etc and they have a status attached to them of red, green or yellow. when i run a select statment on them as below i get the results unit 3131 - red unit 3132 - green unit 3132 - red i knopw that this is becasue the DB is seeing the second two as distinct, what i want is just one of all the unit regardless of second or thrid entries. so the statment i am using is ....
View Replies !
View Related
Left Join Multiple Columns
I am having problems LEFT joining Muti - columns to ONE table This is what I am using: SELECT * FROM nanny_tbl left join position_tbl on nanny_tbl.position_pos=position_tbl.recid_pos and nanny_tbl.position2_nan=position_tbl.recid_pos WHERE recid_nan = colname The first left join woks 100% the 2nd does not nanny_tbl stucture is: recid_pos --- this is primary key position_pos ---- this is VarChar Who can help me solve this?
View Replies !
View Related
Update Syntax For Multiple Columns
I'm looking for a feature I'm accustomed to with other SQL products, whereby multiple columns can be updated with a single SET clause, e.g.: UPDATE tab1 SET (col1, col2, col3) = (SELECT col4, col5, col6 FROM tab2 WHERE tab2.col1 = tab1.col1); I don't see this feature documented for MySQL. Is there a way to accomplish it?
View Replies !
View Related
|
TRACKER
