Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something...
(I'm using PHP and MySQL)
When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site.
However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?
View Complete Forum Thread with Replies
Related Forum Messages:
Single Foreign Key To Multiple Tables?
I have 3 tables as follows: Table A: id (int) (PK), codeOfTypeA (Varchar), description (Varchar) Table B: id (int) (PK), codeOfTypeB (Varchar), description (Varchar)...(some more columns) Table C: id (int) (PK), form(varchar) I need to create a table to hold relationship between Table A - Table C and Table B - Table C So, the table will be something like: Table D: id (int) (PK), formid(int) (FK referencing TableC.id), codeid(int) Is is possible to have a a constraint on Table D to ensure that codeid column can have values that exist in either TableA.id OR/AND TableB.id? I just can't image what kind of a constraint that would be. Can TableD.codeid be a foreign key on both TableA.id and TableB.id so that it would require that AT LEAST TableA or TableB have a record with that ID?
View Replies !
Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine) Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do) insert joomla2.jos_magazine_articles (joomla2.jos_magazine_articles.name, joomla2.jos_magazine_articles.article_id, joomla2.jos_magazine_articles.category_id, joomla2.jos_magazine_articles.catid) select joomla2.jos_content.title, joomla2.jos_content.id, joomla2.jos_content.catid, maxdev.jos_content.catid
View Replies !
Multiple COUNTs Against A Single Table
I want to get some counts from a single database table and I can't figure out how (or if) I can do it in one statement. I have a table of private messages and would like to get counts on new, read and trashed messages for a user. The table: pms ---- msgID (int) (idx) toUserID (int) fromUserID (int) msgSubject (varchar) msgBody (text) msgRead (enum 'y','n') msgTrashed (enum 'y','n') I would love to do know if it can be done without using multiple separate COUNT queries. Any way you can do this with one query? Right now I'm using 3 SELECT queries which all check against the toUserID... new: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='N' AND msgTrashed='N' read: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgRead='Y' AND msgTrashed='N' trashed: SELECT COUNT(*) FROM pms WHERE toUserID=1 AND msgTrashed='Y'
View Replies !
Joining Multiple Fields To A Single Table?
I have 1 table with 2 columns, 'id' and 'name': tbl_names: idname ------ 1Bob 2Jeff 3Fred 4Joe 5Bill I then have another table which contains several fields which hold id's from the above table: tbl_output: idperson1person2person3 ----------------------- 1231 2543 I need a query that will return the names for the specified id from tbl_output. If I have just one 'person' field in tbl_output I would do it with an inner join like this: SELECT name from tbl_names INNER JOIN tbl_names on tbl_names.id = tbl_output.person WHERE tbl_output.id = ? but I can't figure it out when theres multiple fields to be joined from the same table...e.g I want to specify tbl_output.id = 1, and it give me: person1person2person3 --------------------- JeffFredBob
View Replies !
Multiple Foreign Keys To A Single Table
I searched for this topic but the closest thing I found was a listing for Multiple Foreign Keys to Multiple Tables. I could also have not been searching for the right thing. I have a table that contains a list of people. And I have another table that has two foreign key columns, each setup as a foreign key to the people table. I am trying to do a join on the tables so that I can retrieve the names of both people from the people table in one record. Is this something I can do with a single join or will I need to use nested queries? Will I need to make a mysql table or a view?
View Replies !
Query Single Table, Multiple Times
Before i narrate my issue, i would like you to preview my table structure : Table name = machine_info +------------+----------+------------+ | machine | tag_name | tag_type | +------------+----------+------------+ | machine101 | sge | farm | | machine101 | US | site | | machine101 | CRITICAL | status | | machine102 | CANADA | site | | machine102 | UP | status | | machine102 | sge | farm | | machine103 | CHINA | site | | machine103 | DOWN | status | | machine103 | sge | farm | | machine104 | US | site | | machine104 | CRITICAL | status | +------------+----------+------------+ Requirement : I would like have machines from all 'sites' (almost 15 in the actual db) with 1)status='DOWN' 2)status='CRITICAL' 3)status='UP' Example: (Here the results would be like) SITE|DOWN|CRITICAL|UP US |0 | 2 | 0 CANADA|0|0|1 CHINA|1|0|0 I use php to extract information, here's what i do : <?php $db = mysql_connect("hostname", "user", "password") or die(mysql_error()); $selected = mysql_select_db("dbname") or die(mysql_error()); $site_codes = mysql_query("select distinct(tag_name) from machine_info where tag_type='site';"); echo "SITE|DOWN|CRITICAL|UP ";
View Replies !
How Do I Select Data From Two Tables In A Single Statement?
This is a very basic question. I have two tables, say "users" and "purchases": purchases: order_id, order_user_id, items, date users: user_id, username, email What I want to accomplish is to compose a single query that will include the user data along with the purchase information. Something like: SELECT order_id, order_user_id, items FROM purchases WHERE date = 3/12/09 and then translate the order_user_id into user data, so that I would get a final result of an array of rows where each row would include: order_id, username, email, items Is this accomplished by a JOIN statement and how is the result code written (PHP).
View Replies !
Single Table Or Multimple Tables?
Would it be better to setup a system using multiple tables to handle daily entry, or set up individual tables based on daily, weekly or monthly schemes. On average each days entry will be 50 insertions tops, and thats a busy day. Would it be better performance wise to setup a cascading table, that will grow up to 250 entries weekly?
View Replies !
Selecting Data From Single Table... Subqueries
I have a table with a list of books, their genres (type), and price. I have attached it as an image. I would like to select all books whose price is higher than all books of type HOR. Basically, I think that this means: find the book of type HOR that is the most expensive. Then, select all other books whose price is greater than that. In this example, the most expensive HOR book is 19.60. Therefore, I need to select all books with price > 19.60 and not of type HOR. I tried using a subquery like this: SELECT * FROM BOOK WHERE PRICE > (SELECT MAX(PRICE) FROM BOOK WHERE TYPE='HOR' GROUP BY TYPE) However, I get an error that says: Quote: #1064 - 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 'SELECT MAX( PRICE ) WHERE TYPE = 'HOR' GROUP BY TYPE ) LIMIT I looked up that error in the mysql doc but it didn't have much info in there: Quote: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d
View Replies !
Grant Lock Tables On Single Table Gives ERROR 1144
Tested on MySQL 4.1.20 and 5.0.33 with similar results. I don't understand why I cannot grant LOCK TABLES on a single table, while granting LOCK TABLES on all tables work fine. mysqlSELECT VERSION()G *************************** 1. row *************************** VERSION(): 4.1.20 1 row in set (0.00 sec) mysqlSELECT CURRENT_USER()G *************************** 1. row *************************** CURRENT_USER(): root@localhost 1 row in set (0.02 sec) mysqlCREATE DATABASE grant_test; Query OK, 1 row affected (0.04 sec) mysqlUSE grant_test; Database changed mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL); Query OK, 0 rows affected (0.02 sec) mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.12 sec) mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED BY 'foobar'; ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED BY 'foobar'; Query OK, 0 rows affected (0.03 sec)
View Replies !
Extracting Data From Large SINGLE-table Database To MULT-table Relational Database
I have a very large single-table database of articles that I want to convert to a multi-table, relational database. The existing single-table database contains fields for article author, article source, and article category, where several 'author', 'source', and 'category' IDs repeat dozens of times for hundreds of different articles. I want to create seperate tables for author, source, and category and populate the new tables by extracting data from the original single-table database by unique ID field. I figured out how to use INSERT and SELECT to pull data in new tables, but can't figure out how to pull only a single instance of a unique author, source, and category to create master reference tables for author/source/category.
View Replies !
Select Multiple Rows With Multiple Values In A Single Statement
when selecting multiple rows with different values in a certain row.. is this proper syntax? SELECT * WHERE name IN ('hello', 'cookie', 'smile', 'police', 'fun') It seems to work fine, but It came from another SQL version I believe .. not mySQL. Just wanted to double check, or see if there is a more correct way.
View Replies !
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 !
Multi-tables And Pairing Category
I have one table that has category numbers and names and one with data that refereces the category numbers. I need to SELECT * from one table and match up the category numbers to the names that they equal from the other table.
View Replies !
Get Data From Multiple Tables
I have three tables: PRICES_2006, PRICES_2007 and PRICES_2008 I want to find all the rows between certain dates and then order them by date (TIMESTAMP). My current code looks like this: .......
View Replies !
Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables: - shell_tb; (table of shells) - site_tb; (sites where they can be found) - instrmt_tb; (instruments used for it) As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb. 1st. How do I configure the columns in shell_tb in order to accept more than one value? (I tried: CREATE TABLE shell_tb ( shell_id INT(1) NOT NULL AUTO_INCREMENT, shell_name CHAR(40) NOT NULL, site INT(20) NOT NULL, inst INT(20) NOT NULL, PRIMARY KEY (shell_id) ) TYPE=MyISAM; but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?
View Replies !
Selecting Data From Multiple Tables
I have three related tables. tv_observersation, which contains stationID with the start time and end time for a tv station program viewed (the stationID isn't a key field as the same station can be viewed different times). A stations table, which contains the stationID and the station_Name, and an advertisement table, which contains a list of advertiserID and which stationID they are subscribed too (a one to many table). I am trying to generate a query, that would take out info i.e, the info from only stations that a specific advertiser has subscribed to in the advertisment table, but I am getting duplicate info in the query results from the observations table for a specific table. I think it may be a problem with how I am using the select statement. Do I want to select from two tables observations, stations like I am below, or should I use the other two (stations, and advertisments) with the inner join statement. The objective is to retrieve only the stations that a advertiser is subscribed to from the observations table. Code:
View Replies !
Retrieve Data From Multiple Tables
I have multiple tables I'd like to retrieve data from using the same query statment. All tables are setup as follows: ID Reference Value Timestamp I would like to select all the data from "table1" and "table2" where there timestamp is between Jan 01 2005 9:00:00 am till Jan 02 2005 16:00:00 Is this possible?
View Replies !
Select Data From Multiple Tables
I am trying to use the select statement to select data from multiple tables withing my database. could someone post me an example of how the code would look. if you could use the following info in the string the would be great. I want to selsect the following info....
View Replies !
Search Multiple Tables With Same Data
I have 3 tables where Product col is the same across all 3. Tables id /Product / Price So far I have been using $query = "Select `product`, `price` as p, 'tbl1' as n from `tbl1` where `product` REGEXP '".$trimmed."' UNION ALL Select `product`, `price` as p, 'tbl2' as n from `tbl2` where `product` REGEXP '".$trimmed."' UNION ALL Select `product`, `price` as p, 'tbl3' as n from `tbl3` where `product` REGEXP '".$trimmed."' ORDER by `name`"; Output gives me Product | vendor | Price Wanting to do a search where product is combine from all 3 tables and prices in different colums - >output Product | Price (tbl1) | Price (tbl2) | Price (tbl3) Any idea's ?
View Replies !
Insert Data Into 10 Multiple Tables
Can I insert data into multiple tables at once? Basically just need to insert a number into the 'membersID' column into 10 tables, but is taking ages, so wondering if I can write a query which targets all the tables or is this impossible in SQL?
View Replies !
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Inserting Data Into Multiple Tables
I am just learning both MySQL and PHP, and have a question about a project I am working on. The first (and main) table for this project has the basic information about an refund request, including an autoincrement unique ID. Another table includes the products and uses the ID as a reference. The third table is the history of everything that happens to the request. For example, the information may be as follows. Credit table ID - autoincrement status - requested, needs authorization, processed, etc agent - who entered the request customer - person requesting the refund other customer info order table ID - reference to Credit table Item - item ID for amount requested quantity - number purchased price - price of each item (total information is calculated on above fields) history table ID - reference to credit table seq - id for each history log old status - the previous status new status- the status after the update to the record agent - agent making the change notes - a description of what was done. I have the PHP form that collects the data to be entered. My question is, since the first table is an autoincrement, what is the best way to insert a record into the Credit table, pull the ID that was just entered into the credit table and insert that ID into the order and history tables?
View Replies !
Get Data From Multiple Tables And Then Order By Field
$QueryHIST_QUOTES = " SELECT TIMEDIFF(TIMESTAMP, $starttime) AS diff, t.* FROM $currentTable t WHERE TIMESTAMP BETWEEN $starttime AND $endtime ORDER BY TIMESTAMP"; Originally I had the variable $currentTable = "PRICES_2008" but I now want to get rows from multiple tables, so I tried: $currentTable = "XAUUSDOZ_2003 XAUUSDOZ_2004 XAUUSDOZ_2005 XAUUSDOZ_2006 XAUUSDOZ_2007";
View Replies !
LOAD DATA INFILE - Multiple Tables
Is it possible to insert into multiple tables with a LOAD DATA INFILE statement? I'm going to receive a text file with many fields, and then all of these fields need to be imported into the correct tables. It seems as if LOAD DATA can only insert into one table at a time.
View Replies !
Complex - Extracting Data From Multiple Tables - Plz Help
I have spent more than 24 hours now just trying to build a query to extract data from multiple tables (which are a bit typical) in the format I need but in vain and I am goin MAD now . Any help would be really apprecaited. I have 5 tables: items, table1, table2, table3, and table4 table1, table2, table3, and table4 have got 368 columns each (366 representing each day of a year plus two additional columns - see below in structures) structures are as follows: items: ITEMID, TITLE, DESC ... table1: ITEMID P1JAN01, P1JAN02, ... P1JAN31, P1FEB01, P1FEB02, ...P1FEB29 ... P1DEC01, P1DEC02 ... P1DEC31, YEAR table2: ITEMID P2JAN01, P2JAN02, ... P2JAN31, P2FEB01, P2FEB02, ...P2FEB29 ... P2DEC01, P2DEC02 ... P2DEC31, YEAR table3: ITEMID P3JAN01, P3JAN02, ... P3JAN31, P3FEB01, P3FEB02, ...P3FEB29 ... P3DEC01, P3DEC02 ... P3DEC31, YEAR table4: ITEMID P4JAN01, P4JAN02, ... P4JAN31, P4FEB01, P4FEB02, ...P4FEB29 ... P4DEC01, P4DEC02 ... P4DEC31, YEAR Yes they are funny but that's the way they are. Note: Please note the month names within the column names Now I need to select SUM of a given month (say JAN) for a particular ITEMID from each of the tables for a given year (say 2006) for SUM i am using: PHP $p1 = "P1JAN01 + P1JAN02 + P1JAN03 + ... P1DEC31" $p2 = "P2JAN01 + P2JAN02 + P2JAN03 + ... P2DEC31" $p3 = "P3JAN01 + P3JAN02 + P3JAN03 + ... P3DEC31" $p4 = "P4JAN01 + P4JAN02 + P4JAN03 + ... P4DEC31" and then SELECT $p1, $p2, $p3, $p4 ... A basic query that I first tried was: PHP SELECT $p1 AS P1, $p2 AS P2, $p3 AS P3, $p4 AS P4 FROM items LEFT JOIN table1 on table1.ITEMID = items.ITEMID LEFT JOIN table2 on table2.ITEMID = items.ITEMID LEFT JOIN table3 on table3.ITEMID = items.ITEMID LEFT JOIN table4 on table4.ITEMID = items.ITEMID WHERE table1.YEAR = 2006 && table2.YEAR = 2006 && table3.YEAR = 2006 && table4.YEAR = 2006 This acts very funny...fetches me only the results which have got entries in all the tables. If there's no entry in any of the tables for a given item then that item is not fetched at all. The result set i need is something like this PHP ITEMID P1 P2 P3 P4 1 NULL 56 63 NULL 2 36 0 1253 63 3 NULL NULL NULL NULL and so on... and these all must be for a given year I tried playing around with the query with different joins and work arounds but no use. I really need this done soon. I'll be really thankful if you can suggest me anythig that can get me the result set i need. Please let me know if I am not clear anywhere.
View Replies !
Entering Form Data Into Multiple Tables
I've got a two tables in my database: "customers" and "orders." I want to insert information from a form into both of them - in some cases identical information will need to be inserted into both. The other twist is that I'd like to insert the automatically generated id customer 'id' value into the orders table. I've tried using joins. Something like: PHP Code: INSERT INTO orders,customers (customers.id, customers.first_name, orders.del_address   .....)
View Replies !
Retrieve Data Accross Multiple Similar Tables
I have a number of tables that all have the same table format. I want to query accross all the tables as if they where one large table. I am looking to retrieve data base on one of the columns like: select * from (data_readings.lts4a_line,data_readings.lts12_line,data_readings.lts14_line) where trigger_type='time of day' the error I get is 'column "trigger_type" in where clause is ambiguous' is there a way to do all this in one statment?
View Replies !
Getting The Highest Values Of A Category In A Table
Here's my table mysql> select * from messages; +------+------+---------------------+-------------+ | cid | mid | sent | text | +------+------+---------------------+-------------+ | 100 | 1 | 0000-00-00 00:00:00 | 321 | | 100 | 2 | 2007-09-21 00:00:00 | 07921 | | 100 | 4 | 2007-10-21 00:00:00 | 07=10=21 | | 103 | 9 | 2007-09-22 00:00:00 | 07922 | | 103 | 10 | 2007-11-22 00:00:00 | last of 103 | +------+------+---------------------+-------------+ 5 rows in set (0.00 sec) What I want is a query to extract one row for each 'cid' value - and that row to be the one with the most recent date. i.e. what I want is mysql > **mystic inctantation goes here** +------+---------------------+-------------+ | cid | max(sent) | text | +------+---------------------+-------------+ | 100 | 2007-10-21 00:00:00 | 07=10=21 | | 103 | 2007-11-22 00:00:00 | last of 103 | +------+---------------------+-------------+ 2 rows in set (0.00 sec) I can get the first two rows OK, but I can't pull out the correct text line: FWIW here's my lame atmysqlt... mysql> select cid, max(sent), text from messages group by cid; +------+---------------------+-------+ | cid | max(sent) | text | +------+---------------------+-------+ | 100 | 2007-10-21 00:00:00 | 321 | <-- see we have the wrong 'text' rows here | 103 | 2007-11-22 00:00:00 | 07922 | <-- see we have the wrong 'text' rows here +------+---------------------+-------+ 2 rows in set (0.00 sec)
View Replies !
MYSQL: Retrieve One Row With Data From JOINED Tables With Multiple Matching Rows
I'm not sure this is possible in one SQL query: I have a table called TB_EMAIL and another called TB_OTHER_BLOB. TB_EMAIL contains details like: to, from, subject, body and since I will be reusing headers and footers I aslo store header and footer reference to TB_OTHER_BLOB in TB_EMAIL. What I need is a row (given its id) with all columns (except the references to TB_OTHER_BLOB) from TB_EMAIL and two extra columns one being email_header and the other email_footer which are in two different rows in TB_OTHER_BLOB What I came up with is: Expand|Select|Wrap|Line Numbers
View Replies !
Is It Better To Have One Big Table Or Break The Table Into Multiple Tables
would like to know what would be the more efficient method if were to create a product table that had multiple values that i would seldom reference? When sql calls for a table does it reference the whole table in the query or only specific cells? for instance Product(primary key) | Price(2008) | Price(1998)| Price (1989)| Each of the Prices will have sub prices of each month in the that year. Should i make individual tables for the prices for each year instead of dumping them altogether into one table? is this more efficient when web users get the data? what are the advantages and disadvantages in each method.
View Replies !
Multiple Tables Or One Table
I'm designing my database right now and I've come to a point where I have to make a decision that will be very important to the future use of this database: Using multiple tables or one main table on semi-related products... I have found a few topics on this forum that helped me, but still didn't get me to decide one way or another... The question is: where do you draw the line on similarities in the products? Let me explain... The products I want in the table (or tables) have mostly the same columns (id, suggested price, name, ...) but all of them also have different columns (DVD's have bonus material, CD's have tracks, ...). Understand? If it's not clear I can explain a little better if you ask (but I think I made myself clear).
View Replies !
One Table Or Multiple Tables
I'm working on a project that would use huge volume of MySQL-service. So, I ask You give me an advice on how to implement it out better. The question is, What is the best solution in matter of reliability and speed: to use one table where the users can share the data among themselves or to use multiple tables for each user in which case one user that is permitted to share a data with another (on MySQL-server control level) can simply copy his data to the table of that user. But in this letter case I get thousands of those table! May there is a more fair solution. In any way, please share how would professional would make this app.
View Replies !
Create Multiple Tables From Table
I have a table called 'product' in a sql database called 'product_comparison'. There are currently 3 fields in this table. ProductNumber (which is charvar - 255), Description (which is charvar - 255), and Baillio's (which is decimal 20,2 **it's a dollar figure**). I have just under 10,000 lines (different products) and I'm wanting to create a table for each of the products. The tables should be named whatever the 'Product Number' is. Which is usually a combination of letters and numbers (thus the charvar). I also want to take the other 2 values and create 2 fields in their respective product tables. I'm thinking this should be an easy statement for people as smart as all of you. But I'm having trouble. So far, (with some help) I've come up with a statement something like this: CREATE TABLE PAGEONE SELECT * FROM product WHERE `ProductNumber` = 2; This doesn't work becuase 'ProductNumber' isn't an integer first off, and I'm thinking even if it did work, it would still just create one table called PAGEONE and not a table for each product like I want. Is that right?
View Replies !
Comparing Data In Multiple Table
In my db I have multiple tables, which are versions of the same table from different days. I import the data daily by renaming the old table to table_name_date and then importing the new data into a freshly created empty table. This gives me the following tables: mysql> show tables; +-------------------+ | Tables_in_mydb | +-------------------+ | custinfo | | custinfo_0206 | | custinfo_0207 | +-------------------+ The columns are obviously the same in each table: mysql> describe custinfo; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | | | | x | int(11) | NO | | | | | y | int(11) | NO | | | | +-------------+---------------+------+-----+---------+-------+ what I woud like to do is to query all "x" from each of the tables and compare them. I can obviously query for "x" in custinfo and then for "x" in custinfo_0207 and compare them, but can I do this in one query? Ideally what I would get is something like that +-------------+---------------+--------------+-----------------+ | Field | custinfo_0206 | custinfo_0206 | custinfo_0207 +-------------+---------------+--------------+-----------------+ | id | 1 | 1 | 1 | | x | 5 | 4 | 2 | | y | 150 | 150 | 135 | +-------------+---------------+------+-----+---------+-------+ Is that possible?
View Replies !
Single / Multiple Instance(s)
I'm Using MySQL 5.1 and very new to Databases/Coding. i have a helpdesk system at work which i've been asked to write a statistics page for.. i have created one view which is to show the closed tickets and how long they took to resolve (results), but how dow i create a view to give an average resolve time per user. I tried the following code; SELECT `owner` as User FROM `phpdesk_tickets` WHERE 1 which displays user 1 | 2568 seconds user 1 | 2568 seconds user 1 | 2568 seconds user 2 | 2533 seconds user 2 | 2533 seconds user 2 | 2533 seconds How do i get it to display a single instance for each user instead of one for each ticket closed? I also tried; SELECT `owner`, (SUM(resolve_time)/COUNT(ticket_number)) as Average_Resolve from results where 1 but this only showed user 1 and not user 2 aswell
View Replies !
Multiple Records Single Value
I have been developing a realtor intranet system. I am currently writing a php script that deals with 2 tables "property" and "photos" property table: id | address | etc.. ---------------------------- 1 56 My Road 2 389 Your Street photo table: id | file_id | default | property_id --------------------------------------- 1 3434... 0 1 2 343c... 1 1 3 3udu... 0 1 I have added a field "default" in the "photos" table. The purpose of this is to set the default photo for the property listing. My problem is: I want to perform a single update query as I currently only know how to perform this operation with two. eg: $sql1 = "UPDATE `photos` SET `default` = 0 WHERE `property_id` LIKE {$var}"; $sql2 = "UPDATE `photos` SET `default` = 1 WHERE `file_id` LIKE {$var}"; Any way of doing the above 2 queries in a single query?
View Replies !
Update One Table With Data From Another That Has Multiple Returns
I have a query that works but it takes extraordinary amounts of time to run and I'm sure there's got to be a better way. I haven't touched sql in many years and I just can't remember the proper way to do this. Table 1 has a column that I need to fill from table 2. Table 2 has multiples of the requested data. There are about 303 records in table2 that meet the >0 criteria but only 44 distinct values that I need to retrieve. col1 in both tables is the matching index. What I have so far is: update table1 a set a.col2 = (select b.col2 from table2 b where a.col1=b.col1 and b.col2>0 group by b.col1) I also tried limit 1 instead of group by but there was no difference in execution time (which is about 5 updates in 10 minutes!)
View Replies !
|
TRACKER
