How To Improve The Speed On Joining Tables
I have 2 tables, one of them has 0.8M of rows
the other has 40k of rows
total size is ~400MB
How can I improve the speed of joining these 2 tables faster?
do a sort first or other ways before joining them?
View Complete Forum Thread with Replies
Related Forum Messages:
Improve Speed
I need to improve the speed of my ff sql statement but I do not know HOW! I am using mysql in running this. If the records are below a thousand, I have no problem getting the info quickly. But I tried it on 700,000 records, the feedback took 22 secs.In this sql statement, I am currenly accessing the same table but I have to get the rows of certain conditions multiplied to different numbers. SELECT COUNT(IF(code='play',1,NULL))*5+count(IF(code like 'ok%',1,NULL))*2.50 + COUNT(IF(code='prev' OR code='prev_here',1,NULL))*15 + COUNT(IF(code='hello' OR code='hi',1,NULL))*15 + COUNT(IF(code='new',1,NULL))* 2.50 as sum from table;
View Replies !
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
View Replies !
Mysqldump Speed On A DB With Thousands Of Tables
I am running Wordpress MultiUser (WPMU) to host a quite large french blog hosting network (close to 100000 users). WPMU creates 10 tables per user (basically duplicates wordpress'tables for each user). This makes quite a lot of tables as you can imagine. Now, I'm migrating my DB to a custom multi-DB setup, basically seperating users into multiple DBs instead of keeping them just in one. Thing is, when i run mysqldump for just a few tables on the initial DB (trying to migrate one user's data for instance), the mysqldump takes *really long* to perform. It takes me basically 1 hour to migrate 50 users, and considering the total number of users that's gonna take forever to move everyone around. So, I've made some investigation, and using show processlist, I've seen that most of the time spent by mysqldump seemed to be running queries like show table status like tablename or show table like tablename These are probably taking very long due to the large number of tables in the DB. Is there a way I can make these faster ? Or just have mysqldump use something else at all ?
View Replies !
Joining Three Tables
i am making a community site where i am making the member search area, the information from the table is coming from 2 tables already rage_members and rage_members_profile, and i am using the inner join for that and it is working fine ,the basic motive was to search the members (screen name,1st name,last name,country, city,interests,age,sex),now what we do that if we type in the search box "Alamos" it should search all the members who's name is Alamos who's city id Alamos and all the the other fields where exist the string Alamos,till this end it has been doing fine and perfectly alright,now the requirement is to find in the personal profile also where he has answered the questions like 1.level of play 2.career interest 3.about yourself and this info is in another table ,i havent used joins with 3 tables, my current code is below. Expand|Select|Wrap|Line Numbers
View Replies !
Joining Tables
table1 has 5 records - rec1 - rec2 - rec3 - rec4 - rec5 table2 has 2 record (2 fields: rec and stat) - rec1 - stat1 - rec2 - stat0 i want to show all records from table1 that is NOT included in the 2nd table with a 'stat1' status. records should show : - rec2 - rec3 - rec4 - rec5 (because 'rec1' is present in the second table... it will not show; although 'rec2' is also in the 2nd table its status is 'stat0' so its ok to output it).
View Replies !
Joining Tables To Themselves
im in the design stage of a very large project at the moment (as you may know from some of my other questions). im pretty pleased with what i have at this stage but there is one subject that id like to seek expert opinion on before going any further. in an instance where you had a table of data with millions of records with data such as id date grade 1 2006-01-01 2 2 2006-01-01 1 1 2006-05-01 3 and you had an extremely common query being (in english) show the grades of id's who graded 2 on their most recent grading date what is better practice creating a second table that has an extra column id date grade prevgrade 1 2006-01-01 2 null 2 2006-01-01 1 null 1 2006-05-01 3 2 so you can simply say select id from gradingresults where prevgrade=2 or joining table to itself i am planning the extra column version but am being advised by a colleague that this is against table normalization procedure. i imagine that running a query the first way would be considerably faster than the second. any opinions?
View Replies !
Joining Two Tables
I am trying to figure out the SQL to take care of this table merge: I have two tables of identical structure (TABLE A & TABLE B) that I would like to merge into a new table (TABLE C). I want identical rows from each table to not show up as two separate rows in the final table (I do not want duplicates). EX: TABLE A WORKER ID----SKILL ID----SKILL RATING 1000------------2001------------3 1001------------2459------------1 TABLE B WORKER ID----SKILL ID----SKILL RATING 1000------------2001------------3 1003------------2923------------2 TABLE C WORKER ID----SKILL ID----SKILL RATING 1000------------2001------------3 1003------------2923------------2 1001------------2459------------1 NOTE: the row containing a WORKER ID of 1000 was not duplicated in TABLE C.
View Replies !
Joining Tables....
I'm having some trouble joining tables. My two tables are: aws_event (with hundreds of thousands of events) and awu_user (with lots of users). I need to retrieve all data from aws_event where the id field in aws_event is equal to all user IDs with a certain teacher ID in the awu_user table. So: in the example below, in need to fetch all data from aws_event for users who are students of teacher 1. aws_event ------------ user | event | more data ---------------- 1 | blah | more 1 | blah | more 2 | blah | more 3 | blah | mpre aws_user ---------- userid | teacher 1 | 1 2 | 1 3 | 2 Normally, I'd write this in PHP, but I have no PHP access to the server... only SQL query access.
View Replies !
Joining 2 Different Tables
Let me start by saying I am clueless, I have 2 different tables(databases) that I wish to join into one. The one I wish to use is built into a script, and the other has 40000 plus entries and in it own file. Could there be a simple way to drag and drop or combine without having a great deal of knowledge. Both are mysql!
View Replies !
Joining Two Tables
I there I have this statement SELECT DISTINCT reps.*, invites.Used, invites.CompleteOn FROM reps, invites Where reps.Id = invites.RepId AND invites.Used ='1' AND invites.CompleteOn IS NULL group by reps.id it returns only the reps who meet the condition I would like it to return all of the reps
View Replies !
Joining 2 Tables
I am having a problem with a join statement. I have 2 tables. table one - id - name - body table two - id - body - pdate The relationship is one to many (table one -> table two) I want to select all the records from table one, and only get the latest row for each record from table two which is related to table one. (pdate is a date field) Just now if I have 2 rows in table one and 3 rows in table two, 2 of which point towards a single recond in table one, i get 3 rows returned whereas I just want two.
View Replies !
JOINing Three Tables.
Suppose I have these tables: news (content & author_id) comment (comment content & author_id) authors (id and name) I want to create a query that gives me, in one go: one news item it's author (by name, by ref'ing to the authors table) all comments attached to it and the comments' authors (by name, ref'ing to the authors table) Is this possible? I got as far as listing everything but the commenter's name (I got his ID ofcourse).
View Replies !
Joining Tables In Sql
I have built an sql script that connects to one tables. however I want to connect now to 2, this is what I had: $query = "SELECT * FROM artists WHERE ID = ".stripslashes($ID); how do I connect to 2 tables, I know about foreign fields, its just the sql Im not sure about, if someone could post some example ive been googling it for ages, with no success.
View Replies !
Joining 3 Tables
I am using MySQL 4.0.25. I have the following tables: Gallery gallery_id gallery_name gallery_desc feature_image archived Album album_id album_name gallery_id feature_image archived Image image_id album_id sml_url image_title image_orientation ... I am using the sql on a page that displays all of the albums that belong to a selected gallery. I need to select the count of number of images in that album, the album_id, album_name, the url of the feature image, the title of the feature image, and the orientation of that image (wide or high). I am able to achieve this no probs with the following sql: SELECT count(i.image_id) as 'image_count', a.album_id, a.album_name, i.small_url, i.image_title, i.image_orientation FROM album a LEFT OUTER JOIN image i ON a.album_id = i.album_id WHERE a.gallery_id = 1 AND a.feature_image = i.image_id AND a.archived = Ɔ' GROUP by a.album_id, a.album_name ORDER by a.album_name ASC I also need to get the name of the gallery that these belong to. I have tried the following sql: SELECT g.gallery_name, count(i.image_id) as 'image_count', a.album_id, a.album_name, i.small_url, i.image_title, i.image_orientation FROM album a LEFT OUTER JOIN image i ON a.album_id = i.album_id AND a.gallery_id = 1 AND a.feature_image = i.image_id AND a.archived = Ɔ' LEFT OUTER JOIN gallery g ON a.gallery_id = g.gallery_id GROUP by a.album_id, a.album_name ORDER by a.album_name ASC which works fine for gallery_id = 1, but when i display any other gallery, it displays the albums that belong to gallery 1.. I think I am not far off here! - just a minor tweak with my 2nd Left join?
View Replies !
Joining 5 Tables
firstly I am using MySQL 4.0.25. I have a database storing availability of rooms for an accommodation business. The following tables are relevant to my question: room: - room_id - room_name - accom_type - thumb_id - room_comments accom_type: - type_id - accom_type (the name) - desc_short - features - max_slept - num_queen - num_single - num_bunks - main_thumb_url accom_type_pics: - image_id - small_url - large_url - caption calendar_date: - date_id - calendar_date - season_id room_availability: - date_id - room_id - availability_status - booking_id There are 4 accom_type - each with more than 1 room - all in all there are 9 rooms. Each accom_type has a main_image which has its url stored in the accom_type_pics tbl. The room_availability is used to store unique pairs of room_id and date_id - and the status of the room for that date (and booking_id if it is booked). I am attempting to select all of the accom_types that have availability for a certain range and number of guests. I am just not sure which table I "start" with - I have a fair idea of how to write the query, I am just not sure which order the joins need to be in. SELECT at.accom_type, atp.small_url FROM accom_type at LEFT OUTER JOIN accom_type_pics atp ON atp.image_id = at.main_thumb_url LEFT OUTER JOIN
View Replies !
Joining Tables!
i'm making a simple online shop with products that can have any number of attributes..e.g a shirt mite be able to be color: red, blue, yellow, size: medium, large...then a pair of shoes mite be color:white, cream, size, 9, 10, 11 etc. So far I have the following tables... products: id: name : price: description product_attributes: id: productid: attributeid product_attribute_names: id: name product_attribute_values: id: attributenameid: value so, the product_attributes tables stores what attributes each product has (each product can have any number of attributes), in this table, attributeid points to the id in the product_attribute_names table. product_attribute_names simply names and identifies each attribute (eg. size of shoe A, colour of t-shirt B). then product_attribute_values lists all the possible values for each attribute. Now, i think this is a pretty good, normalised way to store products and attributes, but I'm having difficulty joining the tables in a sensible manner. The product table is good because when listing products you can just list product names, price and description. But when you click on the product I have a more detailed product page where you can select what attributes you want before you buy. But I'm slightly baffled as to the best way to use JOIN to extract all the attributes. Ideally for each product I'd want to return a table like this: for product #3762: attributename : attribute value color : red color : blue size : large size : small so i cud access the returned array as $product['color'][0] or $product['color'][1] or $product['size][0] (using php). How could I acheive this using JOINS?
View Replies !
Speed Of InnoDB DELETEs On Large Tables
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): [color=blue] > SELECT count(*)[/color] -> FROM UserSnap -> WHERE LogDate<now() - INTERVAL 750 DAY; +----------+ | count(*) | +----------+ | 308969 | +----------+ [color=blue] > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;[/color] That delete query takes hours to run. The structure of the table is: [color=blue] > desc UserSnap;[/color] +----------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------------------+-------+ | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | | | Period | tinyint(4) | | PRI | 0 | | | UserName | varchar(50) | | PRI | | | | RateType | varchar(50) | | PRI | default | | | Rate | float | YES | | NULL | | +----------+-------------+------+-----+---------------------+-------+ Any suggestions on why this is slow, and what to do about it?
View Replies !
Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different : mysql> describe MONITORINGUNIT1_DATA; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | dt | datetime | | | 0000-00-00 00:00:00 | | | wg | float(20,3) | YES | | NULL | | | dflag_wg | tinyint(4) | YES | | NULL | | +-------------+-------------+------+-----+---------------------+-------+ mysql> select count(dt) from MONITORINGUNIT1_DATA; +-----------+ | count(dt) | +-----------+ | 24144 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dt) from MONITORINGUNIT2_DATA; +-----------+ | count(dt) | +-----------+ | 1464 | +-----------+ 1 row in set (0.00 sec) Very briefly, [dt] contains an hourly date/time stamp representing when the reading [wg] was taken. [dflag_wg] contains a integer that describes the data (over threshold, under threshold, etc). The DB is populated automatically by a Python script that executes once per hour. If I want to get the overlapping data (with the same date/time stamp) I use this query: Code:
View Replies !
Trick Of Joining Tables
I have two tables with the following fields: table 1 ("f"): forum_id (and other fields) table 2 ("ft"): forum_id, user_id and some other fields I want to join the tables on (f.forum_id = ft.forum_id and ft.user_id = 2) but the trick is that ft has an entry for forum_id = 0 that f does not have and I want the join to somehow include a result for that as well. Is there a solution other than to insert another row in table f that has forum_id of 0?
View Replies !
Joining Larger Tables
In a multi-table join, would it be wiser to join the table that has more records first or the table that has the least records first? Is there any performance gains with a certain order of doing this?
View Replies !
Joining Multiplte Tables
I have created this query that joins multiple tables together: Quote: SELECT * FROM rbsr_times, rbsr_entrylist, rbsr_countries, rbsr_cars, rbsr_penalties WHERE rbsr_times.car_num = rbsr_entrylist.car_num AND rbsr_entrylist.car = rbsr_cars.id AND rbsr_entrylist.country = rbsr_countries.id AND rbsr_penalties.car_num = rbsr_times.car_num AND rbsr_times.week='1' ORDER BY rbsr_times.total ASC The problem is not every car_num has an entry in the rbsr_penalties table. But im only able to display their details if they do. How could I rewrite the query to show car_num's that don't have an entry in the penalties table as well as the ones that do?
View Replies !
Joining/Merging Two Tables Into One?
INSERT INTO test select testing.products_id,testing.products_percase,testing2.products_name, testing.products_weight, testing.products_model, testing.products_price, testing.products_quantity, testing2.products_description, where testing.products_id = testing2.products_id; This is what I have so far. Basically, I am trying to create one table with the values listed here from the two tables "testing" and "testing2". It says it is stopping near the where clause; does anyone know how to fix this issue so I can merge the two tables?
View Replies !
Joining Two Derived Tables
I have two very similar queries SELECT items.id, COUNT(votes.id) total_wins FROM items LEFT OUTER JOIN votes ON items.id = votes.winner_id GROUP BY items.id and SELECT items.id, COUNT(votes.id) total_losses FROM items LEFT OUTER JOIN votes ON items.id = votes.loser_id GROUP BY items.id giving two derived tables +----+------------+ | id | total_wins | +----+------------+ | 1 | 13 | | 2 | 15 | | 3 | 10 | | 4 | 12 | | 5 | 10 | | 6 | 8 | | 7 | 14 | | 8 | 19 | +----+------------+ and +----+--------------+ | id | total_losses | +----+--------------+ | 1 | 14 | | 2 | 11 | | 3 | 12 | | 4 | 13 | | 5 | 13 | | 6 | 12 | | 7 | 18 | | 8 | 8 | +----+--------------+ +----+------------+--------------+ | id | total_wins |total_losses | +----+------------+--------------+ | 1 | 13 | 14 | | 2 | 15 | 11 | | 3 | 10 | 12 | | 4 | 12 | 13 | | 5 | 10 | 13 | | 6 | 8 | 12 | | 7 | 14 | 18 | | 8 | 19 | 8 | +----+------------+--------------+
View Replies !
Joining 3 Tables MySQL 4.0
I am trying to join 3 tables together... Here is a diagram I drew to give you a better idea of what I'm trying to do... http://i41.tinypic.com/vzzhpu.jpg I seem to have no problem joining 2 of the table separatly... I just need to combine these two queries somehow... SELECT t2.firstName, t2.lastName, t1.idGrade FROM contactGrade AS t1, login_user AS t2 WHERE t1.userID = t2.userID SELECT t1.idGrade, t2.nameGrade, t1.userID FROM contactGrade AS t1, grade AS t2 WHERE t1.idGrade = t2.idGrade
View Replies !
Joining Two Tables Possibility
if this is possible: SHOW COLUMNS FROM table1 ie. table1_a table1_b table1_c table1_d and then join another table to this result so it would end up looking like this table1_a | table2_rowa table1_b | table2_rowb table1_c | table2_rowc table1_d | table2_rowd .
View Replies !
Joining Two Tables Or Difference Between
I have 2 tables called 'usersTBL' and 'propertiesTBL' user info stored in 'usersTBL' and their properties stored in 'propertiesTBL' I want to select the users from 'userTBL' which does not have any property in 'propertiesTBL' table, Both table has a same column called 'username' the Query I wrote was SELECT * FROM usersTBL, propertiesTBL WHERE usersTBL.username NOT IN ( DISTINCT propertiesTBL.username )
View Replies !
Joining Tables That Are In Different Databases
How do I join two table that are in two different databases? In MSSQL or Oracle, I would have done something like "SELECT * FROM db1..table1 t1, db2..table2 t2 WHERE t1.id = t2.id". Unless my memory failed me anyway. Is there an equivilant syntax for MySQL?
View Replies !
Joining Tables With Different Columns
I have 2 tables: A:[name,password,email,phone,height,weight] B:[name,password,email,age] I wish to do a search in both the tables, and if a match is found in A, it will return values from A(name,password,email,phone,height,weight) and vice versa. I tried UNION but it says the columns don't match. I tried LEFT JOIN and it just merge everything together.
View Replies !
Joining NON-Related Tables
Is it possible to join non-related tables? If I have one table called "community", how would I join it with the table "membership"? They don't have any related fields. I would like to display the data from the "membership" table in the "community" table: community ============== id members group **************** membership ============== memberspergroup feepergroup
View Replies !
Create Joining 2 Tables
I have two tables with that data...truck_id and id in TRUCKS_NAME are reffering to the same thing... how do I create a select query to join both? TRUCKS_ENTRY id = 89 truck_id =3 create_by =61713 description = Test name = John TRUCKS_NAME id = 3 truck_name = Mercury truck_vin = LIIVLKSJOIKES truck_plate = 2H1037
View Replies !
Joining Two Tables To Get Value Of Unique ID
trying to select a value (PK) from one table and join to another table, getting the value of the item with that ID. I seem only to be gettinbg the unique ID value and not the other value in the record. ("SELECT Business_Type_ID From tbl_BusinessDetails table inner join tbl_businessType on Business_Type_ID = Type_ID"); here's the table structure tbl_BusinessType | Type_ID | Business_Type | tbl_BusinessDetails | Business_ID | Business_Name| Business_Type_ID | I am trying to get the value of Business_Type (from tbl_BusinessType) using the PK (Business_Type_ID) in the tbl_BusinessDetails. I also do not want any matches.
View Replies !
Joining MySql Tables
I want to create 2 pages, one is called monitor.php and other on is monitor_more.php (it's for a computer store) all data will come from mysql monitor.php: displays many types of monitor, each monitor will contain a picture, price, descriptions (2 lists) and a link called more (go to the monitor_more.php) monitor_more.php display picture of monitor, price, description (10 lists). Here is my problem I created 2 database, table1 contains: <picture> <price> <description1><description2> table2 contains: <description3> <description4> <description5> ......... <description9><description10> how could I link table1 and table2 together?
View Replies !
MySQL Joining Tables
I have 2 tables, one that has $query="CREATE TABLE products (id int(6) NOT NULL auto_increment,fname varchar(15) NOT NULL,lname varchar(15) NOT NULL,stage2 varchar(20) NOT NULL,itemnumber varchar(20) NOT NULL,quanity varchar(20) NOT NULL,email varchar(30) NOT NULL,location varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))"; In the 2nd table I want to have 3 items, itemnumber, quanity, and descrition. I want itemnumber from the 1st table to refer to the 2nd table and update the quanity. For example, If I order 3 of item #12345 then i want it to update the #12345 to 3. And then say in a week someone else orders 4 of #12345 then i want it to change to 7. How would I go about linking these tables together,
View Replies !
Joining On Unionized Tables
I'm working on a CMS with several database tables featuring animal names and information. My pages begin with two multi-table queries using the UNION command, similar to this: PHP SELECT * FROM (SELECT GZA.Name FROM gz_animals GZA WHERE Name LIKE 'Canis_lupus' UNION ALL SELECT GZM.Name FROM gz_mammals GZM WHERE Name LIKE 'Canis_lupus' UNION ALL SELECT GZB.Name FROM gz_birds GZB WHERE Name LIKE 'Canis_lupus' UNION ALL ) as Animalia SELECT * FROM ( SELECT GZA.Name, GZA.MyCla FROM gz_animals GZA UNION ALL SELECT GZM.Name, GZM.MyCla FROM gz_mammals GZM UNION ALL SELECT GZB.Name, GZB.MyCla FROM gz_birds GZB ) as TClass WHERE Name LIKE 'Canis_lupus' I actually use variables instead of "Canis lupus" so it can display whatever database value matches my URL. Anyway, the above tables focus on scientific names. I'm now ready to add common names, but I'm not sure how to do it. First, not every animal species has a common name, so I created a separate series of tables for common names. Actually, I've only created the birds' common names table so far. I'm trying to figure out how to join it into my query, but nothing works. I assume that if I add a join to one table, I have to add the same type of join to every table in the series, right? But if the only nams table is bird_names, then shouldn't I be able to join every other table (gz_mammals, gz_birds, etc.) to bird_names? If that should work, then I'm probably messing up on this section: PHP WHERE Name LIKE 'Canis_lupus' I've tried variations like these... PHP WHERE Name LIKE 'Canis_lupus' OR GZBN.NameCommon LIKE 'mandarin_duck' WHERE Name LIKE 'Canis_lupus' OR WHERE GZBN.NameCommon LIKE 'mandarin_duck' So is there a way to do this join if I have just one common names table? Also, note that the first series of tables form a super table named "Animalia," while the second series form a table named "TClass." Instead of joining bird_names to every individual table, is it possible to simply join it to Animalia, then to TClass? How could I do that? *Question #2* Is it possible to create a series of tables joined by UNION to form the super table Animalia, then create a second series of tables joined by UNION to form the super table CommonNames, then join CommonNames to Animalia? I envision a scheme where I could join the tables even if the only common names table was birds_names. When I finished mammals_names, I'd simply add it to super table CommonNames.
View Replies !
Trouble With Joining Tables
I'm having some trouble running the following query: SELECT DISTINCT(im.id) FROM item_master im LEFT JOIN xref xr ON im.id = xr.item_master_id WHERE im.item_number LIKE '%100%' OR xr.oem LIKE '%100%' For some reason, the query takes 2 minutes to execute. If I remove the bit that says "im.item_number LIKE '%100%'", or change it to compare data from the joined table (eg. "xr.item_number LIKE '%100%'"), the query is executed in 31ms.
View Replies !
Problem JOINing Two Tables
I have two tables, for simplicity lets call them Table AAA and Table BBB, I have a query as follows SELECT count(DISTINCT AAA.ID ) AS post_count, count( DISTINCT BBB.ID ) AS vote_count FROM AAA JOIN BBB ON BBB.user_id = 'John' WHERE AAA.user_id = 'john' Now this query works fine if both the tables contain records with user_id as 'John'. The problem arises when there's no 'John' in any of these two tables. There could be situation in my program where john has a entry in only table AAA or only BBB. In case he has no entries in the any table that corresponding DISTINCT COUNT should be returned as zero.
View Replies !
Joining 3 Tables! Mysql Help...
Here is my current setup: featuredvideos: videoid videos: videoid, userid, videotitle, videodescription members: userid, username, email So I have a page where I want to list all the featured videos, and I want to grab all the video details as well as details about the user. So I need to somehow join all 3 of these tables. Currently I joined 2 tables: PHP $sql = "SELECT * FROM featuredvideos JOIN videos ON featuredvideos.videoid=videos.videoid"; but I also need information about the user, how would I go about doing this? Also, would it be advantageous to also store the userid in the featuredvideos table as well?
View Replies !
Joining Tables With A Limit
I have two tables, they are ARTICLES and ARTICLE_IMAGES Each article can have up to 5 images, the images are displayed on the "VIEW ARTICLE" page. This works fine. The problem is the "ARTICLE LISTINGS" page. The page displays a summary of each article, the issue i am having is that i also needs to display the first image (image with the lowest id) alongside each summarised article. Is this possible? At the moment it is displaying ALL images, i only want it to display jsut the one, i.e. the one with the lowest id.
View Replies !
LEFT JOINing On 3 Tables
I have the following tables: FEEDS, which includes feed_id and feed_name, among other fields. LOCATIONS, which includes location_id and location_name, among other fields. LOCATIONS_DATA, which consists of 2 foreign keys; feed_id and location_id, plus one other field called data. Any given feed_id or location_id may appear in LOCATIONS_DATA multiple times, or not at all. What I am trying to achieve, is the selection of the IDs and the locations_data.data specific to that combination of IDs, if one exists. This is what I have tried: Code: SELECT feeds.feed_id, locations.location_id, data FROM feeds, locations LEFT JOIN locations_data ON locations_data.location_id = locations.location_id AND locations_data.feed_id = feeds.feed_id WHERE locations.location_name = [user input] AND feeds.feed_name = [user input]; This fails with an "Unknown column 'feeds.feed_id' in 'on clause'".
View Replies !
Joining Tables On Unknown Table Name
I have a framework that uses dynamically created tables, named using an incremental "attribute set ID", as follows: attrdata_1 attrdata_2 attrdata_3 etc, etc... I also have another table that stores data for each object within the framework, called "object". The fields in this table are fixed so are always known, one of which is "attrset_id" which relates to one of the "attrdata_***" tables. The fields in the "attrdata_***" tables are all unknown except for a fixed "object_id" field that links objects with an entry in those tables. The idea is that the "attrdata_***" tables can be used to extend the data for each object so, for example: We need to extend Object 100 to store new properties "name", "address" and "tel", so a new "attrdata_4" table is created containing fields "object_id", "name", "address" and "tel" and "object_id" is set to "100" (the object's ID) and the "attrset_id" field in the "object" table is set to "4" (the newly created attribute set's ID). Now, when querying the DB I want to link the "object" table with the relevant "attrdata_***" table to get the complete extended data set, so ideally this would be.... SELECT O.*, A.* FROM object AS O LEFT JOIN CONCAT("attrdata_", O.attrset_id) AS A ON O.id=A.object_id WHERE O.id=100 ... but that doesn't work. What I need to know is if anything along those lines exists? I've looked, but not found so I'm now making sure :) Although it could be done using a couple of statements, I'd prefer, if possible, a single statement solution.
View Replies !
Joining Two Tables For A Search Engine
let's say I have 3 tables. one has titles, one has words, and an index where each title is broken into words (title_id and word_id) I want to be able to search terms in any order, so "potter harry" is the same as "harry potter". select t.name from titles t, words w, index i where (w.name in ('harry", "potter")) and (w.word_id = i.id) and (i.title_id = t.id); This produces a list of titles matching my search times. however, with over 3 million products, it can be quite slow. 3 seconds is too slow. sometimes it takes a minute. is there a better way to do a join when there are more search terms like "harry potter and the chamber of secrets"? the fastest way I found was to get the word count for each term, join on the least used word, and then make sure the remaining terms are in the titles. but sometimes it returns 10,000+ titles.
View Replies !
Joining Multiple Tables With One To Many Relationships
I have one main table that holds employee IDs and their supervisor names. I then have a dozen other tables that hold many different employee statistics and scores that they accumulate through the year. These tables all have employee IDs in them, but the IDs would reoccur everyday (so they are not unique) and there is data for every day of the year (i eventually need to factor in the date fields to allow them to query by dates). If i join just one table linked on IDs, i get correct results. If i add a second table linked on IDs, i suddenly get a cartesian product of those two tables. (if i query for just one employee: table one has 118 records for that employee. table two has 125 records. when i query both tables linked by ID i get 14,750 records! (118*125)) Here is part of my sql (ive basically stripped it down and have excluded many of the tables for now): all ID fields are employee ID numbers - which would be the common link between all of the tables. Expand|Select|Wrap|Line Numbers
View Replies !
Joining Two Tables With Multiple References
Anyone have any idea how to join two tables where more than one column in the child table is referencing the same key in the parent table? table #1: members (memberID, firstname, lastname) table #2: log (issuerID, receiverID) Both issuerID and receiverID are foreign keys referencing memberID. I want a query that will list the names of the issuer and the receiver in one query... is this possible?
View Replies !
Joining Tables Based On Condition
I want to have a field determine what table the field should be joint on. For example, I have a field that is one of eight different values (contacts, leads, accounts, ect...). I want to be able to join on the specific table specified by the field because this field determines what type the id field corresponds to.
View Replies !
Joining Tables & Grouping Results
I am working with two tables, one listing members and another listing transactions they have made. I would like to create a list that has member details (from the member table) and total value of transactions each member has made based on how many entries in the transaction table that member has. I guess I can run a query like this: select * from member, transaction where member.id=transaction.member_id and then go thru each row to produce a summary of results im after...
View Replies !
Create A New Table By Joining Two Tables Together
TableA and TableB have one field in common: studynumber. This works: SELECT * FROM main3 JOIN studies_001 ON main3.studynumber = studies001.studynumber; Now I want to create a new table based on that JOIN. I can't seem to do it - I changed the fieldname in one table to snumber to avoid duplicate fields but this does not work: CREATE TABLE studies_try1 SELECT (main3.studynumber, main3.studydate, main3.studytitle, main3.studyintro, main3.studytext, main3.booknumber, main3.messagetype, main3.media2_filename, main3.teacher_id, main3.media1_filename,studies_001.id, studies_001.snumber, studies_001.chapter_begin, studies_001.verse_begin, studies_001.chapter_end, studies_001.verse_end) JOIN studies_001 ON main3.studynumber = studies_001.snumber;
View Replies !
|
TRACKER
