Efficient SELECT From Multiple Tables With Same Formatting?
Let's say I have 2 tables with the exact same formatting (field/column names, etc) -- the only difference is the name of the tables.. one is called "table1" the other is called "table2"
If I wanted to query both of the together is this legal and good coding practice?
"SELECT id, headline, permalink, body FROM table2007_11, table2007_12 WHERE id=23"
understandly I get this error: Column 'id' in field list is ambiguous"
is there a better way to do this than printing out each table name for each field individually? (ie. id. table2007_11, id. table2007_12, etc)?
View Complete Forum Thread with Replies
Related Forum Messages:
Select Multiple Keywords From Multiple Tables
am trying to do. I have a database called "members" that contains a table called "Reviews". This table has fields S1, S2, S3, S4, etc. that I want to search for the words ad, advertisement, and promotion. Here is the syntax I came up with that doesn't work: SELECT * FROM `REVIEWS` WHERE S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, XTRA='ad, advertisement, promotion' It would be perfectly fine, if easier, to search all fields in the table.
View Replies !
The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people The table is quite large (300,000 rows) I need to write code that update many of the rows in the table Here is what I need to do Two of the fields that I store about each person are: location and country The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements I cannot use LOAD DATA as this is product database Is there a way to update multiple rows (each with a different value and condition in a single query?
View Replies !
Most Efficient Way Of Storing Data From Multiple Accounts
As part of a system I am putting together I need to allow users to create thier own accounts on my servers. Each user can create their own account, and then have their users register for it. Each account needs it data seperate from the others, a member registered for one account should not be able to view another account and a username registered with one account should still be available to the other accounts. The ways I have been looking at are: 1. Create a new database for every account created so that all users are kept in seperate databases. 2. Have one table for users, one for topics, one for posts etc and then associate each row within this table with the relevent account. So for example a user could register with the forum with the ID 4, so their user entry would be Userid: 234 Username: xxxx Password: xxxx Forumid: 4 Then when a new member registers with any account I simply check that there is not another user with the same account ID and username. Indexes on relevent fields in this system could help speed up huge tables. I expect to quickly have 20,000 plus accounts (and in theory it could go up to hundreds of thousands). I guess my question is which of these methods is better from a speed point of view once we get a large number of accounts and users. Also, are there restrictions on the number of fields in a table that could cause problems? Using MySQL by the way, on an Apache server.
View Replies !
Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation. On a typical "route" there could be hundreds of points. My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route". Sample data is along the lines of : - <trkpt lat="54.016942977905273" lon="-1.4903640747070313"> <ele>82.330078125</ele> <time>2006-09-03T07:35:41Z</time> </trkpt> - <trkpt lat="54.016938870772719" lon="-1.490332055836916"> <ele>0</ele> <time>2006-09-03T07:35:42Z</time> </trkpt>
View Replies !
Is This INSERT With SELECT As Efficient As Possible?
I'm writing a pretty complex web app and will be repeating many times over a query very similar to below and need to know if there is a more efficient way to do it. If anyone has input, I'd be happy to hear: INSERT INTO table (somecolumn) VALUES ((SELECT id FROM other_table WHERE foo = 'bar'))
View Replies !
Select From Multiple Tables
I have multiple tables named like this wpctchie, wpctchee, wpcbasfr, wpcbasth etc. The idea is that admin will be able to search all tables beginning "wpc" (all tables), superusers will be a able to browse tables beginning "wpctch" ("wpctchie" and "wpctchee") and users will be able to search only one table e.g "wpctchie" Is it possible to do a search such as SELECT * FROM wpc% (for admin) SELECT * FROM wpctch% (superusers) SELECT * FROM wpctchie (well this just works anyway!) Does that make sense what i am trying to do?
View Replies !
Select With Multiple Tables
I have a select coded like: "SELECT * FROM Table1, Table2, Table3 WHERE Key1='Joesmith'" the query works correctly except that both Table1 and Table2 has a column named "Points" What is the proper way to retrieve the correct column named Points? I tried "Table1.Points and that failed. I changed the order of table names and then I retrieved the one I wanted (ie. Table2). It appears that if two tables have the same colun name that the one referenced is the last one listed.
View Replies !
Select Statement From Multiple Tables
how can I do this type of join: table1: id, value1, value2 table2: id, value1, value2 table3: id, value1, value2 the "id" is a product code the values are new & used pricing I need a select that can join all this data, and fill 0's if an id is not listed in a gven table, then output to a file like so: id, table1.value1, table1,value2, table2.value1, table2.value2, table3.value1, table3.value2 obviously, if I join on all the ids, it only will output the values that have the same id in every table. how do I get that plus all the values that are unique to each table?
View Replies !
Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields between them, not even a KEY or ID or anything like that, except for the "body" of a blob field. and that text is not identical, just a portion of that text is identical. each table has 5 fields, all different except the blob, which is called "message", so normally I use something like: select * from table1 where message like '%apple%'; to query this table, and the same goes for table 2, except the blob is different, table 2 normally is like this: select * from table2 where message like '%customerid=453%'; It's impossible to change the data in these fields (which would be the best option), but there is one common element between them in the message blob. What I want to do is something like this: select * from table1, table2 where message like '%order=100%'; however only one table will have that order, either table1 or table2, but never both, and theres no way to tell which of the tables will actually have the text. In other words, I want to search a bunch of tables for common text without having to actually submit the query five times, because the list of elements to search is about 25,000 items... I'd rather submit 25,000 queries than 125,000 queries.
View Replies !
Select And Count From Multiple Tables
i have two tables (providers and clients). Whenever I have a new user create an account, I want to check the count of each, and if it is 0, then the account can be created, otherwise they will get prompted for a new one. What is the syntax for this? I was trying to do a select count(*) from table1, table2 where username=x, and for some reason it gives me a 5, even though there is only 1 entry.
View Replies !
Select Count From Multiple Tables
I want to select persons from a person table, and count from an "events", "trainings", and "leads" table, but I'm not able to figure out how to select distinct so that I get one person each and a count for how many times that person appears in each other table. My statement looks like this right now: SELECT person.name, count(leads.lead_id), count(events.event_id), count(trainings.training_id) FROM person LEFT JOIN leads ON leads.person_id=person.person_id LEFT JOIN eligible ON eligible.person_id=person.person_id LEFT JOIN events ON events.event_id=eligible.event_id LEFT JOIN trainings ON trainings.event_id=events.event_id GROUP BY leads.lead_id, events.event_id, trainings.training_id Is it even possible to do what I want?
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 !
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 !
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 !
Accessing Multiple Tables With A Select Statement
I am writing a select statement, that says something to this effect. Select * from texas, california, oklahoma; Everything in each table is going to be the exact same. as far as city, name, and zip...they are the titles of my fields. But when I go and look at this file, it just shows everyone from oklahoma. If take out out oklahoma, then it shows everyone from california but not texas. What is the proper way to do this?
View Replies !
Select All Rows Greater Than... (multiple Tables)
Edit: Using MySQL 4.0.27. I have a standard forum database, and am trying to extract data from it. I want to extract "Last 7 topics you have posted in", and need some SQL help! The data returned should list the last 7 topics you have posted in. The last topic you have posted in will be at the top. A topic row can have many associated post rows (many posts in one topic). This SQL query works for this purpose: (Assume userid = 2)....
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 !
Select Statement With Multiple Counts/multiple Joins Trouble
SQL SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count FROM duo_groups as g LEFT JOIN duo_group_members as m on m.group_id=g.group_id LEFT JOIN duo_users as u on u.user_id=m.user_id LEFT JOIN duo_videos as v ON g.group_id=v.group_id LEFT JOIN duo_video_comments as c on v.video_id=c.video_id GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username Will return My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.
View Replies !
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
View Replies !
Multiple Batabases Vs. Multiple Tables
I am about migrate from an old program database to MySql (Running under RH LINUX)and I'm wondering which is the best option to do : I currently have one file for each of my modules, (I'm using filepro plus) , so like CUSTOMER, WARRANTY, ZIPCODES, INV, etc... like 2,000 files each with their own fields. 1) Should I create multiple databases on MySQL 2) Should I create a single database on MySql and with multiple tables ? Which one of these options is the best and more safer ?
View Replies !
Multiple Databases Or Multiple Tables
I have an application that is supposed to make multiple connections [around 10 connections per second] to a mysql database. The connections are done by mutlitple users at potentially the same time. I have to decide if I should use : 1)one database with one table for all users. 2)one database for each user. 3)one database with one table for each user. I would really appreciate if you can tell me what choice is best and why.
View Replies !
Selecting From Multiple Tables (20 Tables)
I am running MySQL 4.1.20, but use a PHP front end mostly, so I'm not too familiar with the mysql command line options. I have a database with 20 tables, which are data stamped. I'd like to do a select or delete statement that uses multiple tables. I know I can do select * from t1, t2, t3 where field= 'value' But since the table names change, is there a way to do some kind of wildcard expression for table names? Something like.. select * from t* where field= 'value'
View Replies !
What's The Most Efficient Way?
I've done a database scheme and wonder what the most efficient way of storing certain entries would be. The site will have news, reviews and tutorials and these will be under the same categories. So I'm wondering what the best way to do design the database scheme would be. Have one table like I've done now in the "content" table with the posibilty to differentiate the entries with the "post_type" field or have three different tables? Below is the table "content". Field Type Null Default post_id int(10) No post_date datetime No 0000-00-00 00:00:00 post_text text No post_title text No post_cat_id int(4) No 0 post_description text No post_name varchar(200) No post_type int(4) No 0
View Replies !
How Efficient Is In() , And How Much Is The Most That You Should Put In An In()?
I am just wondering as to the efficiency of the in() function in MySQL. select field1,field2,...,fieldn from table where id in(1,2,3,4,5,6, ... , n); assuming: id is an indexed field. table is a VERY big table (100 000+ - 1 million+ records) what do you think is the largest number of values you could pass to the in() function without completly flattening your server?
View Replies !
Most Efficient YES/NO...
I) Table_A has fields Data_1, Data_2, Data_3, and Data_4; I need to determine whether value 'X' is present (at least once) in any of the Data_N fields. All I need is a YES/NO answer: YES, 'X' is present (at least once) in one of the Data_N fields of Table_A [Record number(s) not required!], or NO, 'X' is not present in any of the Data_N fields in any of the records in Table_A. II) Similar scenario, but this time Table_A has fields RecID, Data_1, Data_2, Data_3, and Data_4. I need to determine the RecID (none, one, or more) of every record that has value 'X' in any of the Data_N fields. I'm looking for the query that will be fastest and most elegant to implement.
View Replies !
Quickest And Most Efficient
i run an online game. I want to give each user a ranking, based on how high thier score is.If for example, i had 20,000 players, i dont want to have to update them all one by one, it may strain the server and take a long time. Is there another way i can assign a rank number (rank 1 has the top score and so on)
View Replies !
Efficient UPDATE
I have a table with the following structure; CREATE TABLE my_table ( id_1 int(11) NOT NULL , id_2 int(10) NOT NULL , stauts tinyint(1) NOT NULL DEFAULT 0 , PRIMARY KEY (id_1) ) Engine =InnoDB'; The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query; UPDATE IGNORE my_table SET id_1 = 74240, id_2 = 5 I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.
View Replies !
Efficient Storage Of IP Address
I am establishing a database for the purpose of logging access to my secure webserver and am wanting to make the database as efficient as I can because it will be doing a lot of work when the site goes live. What is the most efficient way in a MySQL table to store remote IP addresses? What data type should I use? Should I just go with a basic VARCHAR(15) to allow for 4 sets of 3 digits with 3 decimal separators, or is there a better way?
View Replies !
Efficient Placement Of Fields
is there any noticable efficiency is ordering the position of the field types?. Like say i place the join keys in a table at the end, and varchars and text at the top of the table does it really matter? Also when i do a query like select * from table where id IN (1), where 1 is usually a primary key int is that quicker than doing where id=1 or is there no difference and is it handling the int as a string or as an int?
View Replies !
Efficient Database Structure
I have been developing a new website and i need help in deciding the best database structure for it. The site is basically a dating website with various modules like blogs, videos, comments, friends, photos etc. I have created a member table that stores all the basic profile info and created seperate tables for friends, photos, messages, blogs etc and MemberID as foreign key. Now on profile page, i want to display all info related to member like his profile info, his photos, friend list, messages etc and i have to execute 7-10 short queries on profile page for this. Also, i think Joins will not be much helpful as there is one to many relationship e.g there can be more than 1 photo for a member and i am saving each photo in a seperate record. Similar is the case for other tables?
View Replies !
Creating An Efficient Database
i'm currently writing a web based catalogue system in php using a mysql database. the catalogue has a number of products in it from different brands. i would like to know if it is more efficient to have each brand in a separate table then a "master" table just listing the brand name and corresponding table or all the products in one large table and a "master" table listing each brand in the large table. the large product table would of course have a field to state which brand the product was from. the efficiency would be based on users being able to access the database via html browsers using php and also search the database.
View Replies !
How To Make Index Most Efficient
I have a large table (> 3,000,000 records). Each records contain a primary key like 'id' and a lots of attributes like 'age', 'department'. I want to build some indices to accelerate my query. I read the document which says that too many indices may slow down the INSERT and UPDATE operations. So is there any rule on how to set indices in such table? If I create an index for every field, would that be a very bad idea? If I create an index on each of two fields but not on their combination, will the indices contribute to queries on the combination?
View Replies !
Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer. I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Replies !
Efficient Way Of Mass Indexing?
I have been working on a program that will populate and index a database. The populating doesn't take too long, but the indexing does. My question is: Is there a better approach to indexing this table than the one I'm using right now? I'm doing this through QtSql. headers is a QString array of 48 column names I want to index. for (int i = 0; i < headers.size(); ++i) { q.exec("ALTER TABLE mysqltable ADD INDEX(" + headers[i] + ");"); } example run for 20000 rows: viper,david $ time ./main -r 20000 Database Connection Established 0.520u 0.060s 0:21.27 2.7% 0+0k 0+0io 0pf+0w
View Replies !
Is This An Efficient MySQL Setup?
I would like to offload the MySQL server from my dedicated box in order to speed up page loads. I don't have an additional dedicated server so my only option is to get a VPS. But is offloading the MySQL server to a VPS, albeit a modest one, even worthwhile? I understand this is a very broad question because I am not providing any details but that being the case I am expecting a broad answer
View Replies !
Efficient Way To Left Join?
What is the most efficient way to do a table join, but even if there is no matching foreign key, still return the table on the left. SELECT col, col2, COUNT(col3) FROM tbl1 t1 LEFT JOIN tbl2 t2 ON t1.id = t2.id GROUP BY t1.id But this only works if there is a matching foreign key on t2, I would like all t1 rows to return regardless of whether they exist on t2 or not.
View Replies !
Multiple Tables Better?
i have a database with 10 tables, each table has around 9,000 record, is it faster and better to put all records in ONE HUGE TABLE and define each table with a column ?? i mean i have table for games, softwares, movies, etc ... so i put ALL the records in one table and make a column named "type" and i insert in each column the corresponding value.
View Replies !
Multiple Tables DB
Does anyone know of a practical tutorial (with code) that uses multiple related tables. I want to learn how to update all related data when doing an insert. e.g.: if I add a user to users table and each user gets a car by defualt, how do I insert a car to cars table and keep a reference to the user (owner of this car).
View Replies !
|
TRACKER
