Retrieve Data From 2 Tables In 1 Query
I've got 2 tables content & sections I need to:
SELECT id, title, section_id , status FROM content
and now I'd like to get title from sections WHERE id = section_id from the first part of the query.
I've tried with unions but doesn't seem to like it..
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
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 !
Get 4 Tables Data In One Query
I am new to mysql relational programming, and creating a tutorial site like pixel2life. I am trying to get the data from 4 tables 1st table is main tutorials table, 2nd is subcategory (subcat) table, 3rd is categories table and 4th is users table. I don't know how to do it exactly, I googled but not got the proper method. I only got some hints http://www.brainbell.com/tutorials/M...ing_A_Join.htm from this site. I've created the following query which selects the title from tutorials table, subcategory (s_cat) from subcat table which reference is available as s_cat_id in tutorials table, then category (cat) from categories table which reference is available in subcategory (subcat) table and username from users table which reference is availble in tutorials table. Can somebody tell me this query is correct or there is any alternate method PHP SELECT d.title, s.s_cat, c.cat, u.username FROM tutorials d JOIN subcat s ON d.s_cat_id=s.id JOIN categories c ON s.cat_id=c.id JOIN users u ON d.author_id=u.id
View Replies !
Retrieve MySQL Data
Until recently I ran a php/apache mySQL wbsite on MacOSX. For some reason I can't get the machine working again so now the site is offline. I succeeded to copy the MySQL data from the Mac OX partition. However, this is the 'raw' data - not a SQLdump but the frm, myd and myi files for each table. The data is not of much use if I want to use it in a website again, I'd rather have a SQLdump. So now my question is: Can I just add these files in the data directory of another mySQL installation I setup locally under win2k and be able to make a dql dump?
View Replies !
INSERT Data And RETRIEVE
I realize this is a simple question. I tried to search through the forums but am not sure how to word the question to get any results. My search queries returned no matches from the forums... If you can answer my question that would be great. Even better would be a suggestion on how to search this forum so that the answer turns up. this is the SQL so far: INSERT INTO image_object SET image_name='11185131276164.jpg', image_float='left', image_caption='Caption', image_alt='Alt Text'; What i need is for MySQL to return a result telling me the auto_increment id that was given to the record at the time of the insertion.
View Replies !
Retrieve From Hierarchical Data
i have a table like this. id parentid c3... ------------------- 0 0 1 0 2 0 3 1 4 1 5 2 6 2 7 2 8 5 9 5 10 5 11 3 12 7 i want ids that has a id as parent or grand-parent or grand-grand-parent, so on. ----------------------------2 -----------------------5--- 6--- 7 ---------------------8 9 10-----12 Suppose if i want subids of 2 then it should return 2,5,6,7,8,9,10,12.
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details" What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query? $query = " INSERT INTO sub_pages (mainpage_id, intro_text) VALUES ('$main_data->mainpage_id','$sub_intro'); INSERT INTO eiland_details (inwoners, oppervlakte) VALUES ('$detail_inwoners',$detail_oppervlakte')";
View Replies !
How Speed Up Data Retrieve From Large Table
I try to retrieve duplicate data from database but it take a lot of time to display and very slow.Even using index also same .Here is my sql statement : $sqlselect2 = "SELECT DISTINCT p1.resume_id, p1.user_id, p1.email, p1.name, p1.gender, p1.house_tel, p1.office_tel, p1.hand_tel, p1.new_ic, p1.old_ic, p1.address1, p1.address2, p1.city, p1.dob FROM resume_personal p1 ,resume_personal p2 where p1.$searchString=p2.$searchString And p1.resume_id!=p2.resume_id Order by p1.$sort $order "; $result2 = mysql_query($sqlselect2,$conn) or die(mysql_error()); Anyones got the better solution to retrieve duplicate data or to improve the speed?
View Replies !
Query: It's Possible To Retrieve Only Numbers From A Field?
How to retrieve only numbers from a field? select firstname, address from `acme`.`anytable` where idanytable=100; +-----------+--------------------+ | firstname | address | +-----------+--------------------+ | ERICKA | RUA B NR 50 AP 202 | +-----------+--------------------+ select firstname, onlynumbers(address) from `acme`.`anytable` where idanytable=100; +-----------+---------+ | firstname | address | +-----------+---------+ | ERICKA | 50202 | +-----------+---------+
View Replies !
Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'") set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'") Is there any way to do this in one SQL query?
View Replies !
Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc. To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table: categoryname vegetables categoryid 1234 categorycreated 10th Jan 2000 categoryowner Bob firstitemid 4567 firstitemname cucumber firstitemcreated 12th Jan 2000 firstitemdesc It's long and green Here's an example record in the itemdata table: itemid 4568 itemname orange itemcreated 13th Jan 2000 itemdesc It's round and, er, yeah. This seems really bad design to me but what do I know. Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields. In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order. itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids. I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest. I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Unable To Retrieve VARCHAR Data In Wchar_t Buffer
My database has a single table with a single column of the type VARCHAR(20). I insert one record into this table through the command line and then I try to retrieve this record through ODBC API. I bind a wchar_t structure to this column with the type as SQL_C_WCHAR using SQLBindCol. But when SQLFetchScroll returns, the buffer is still empty. I am trying this on Redhat Linux 9.0 with unixODBC-2.2.3-6, MyODBC-3.51.09 and MySQL-server-4.0.21-0. The exact same code works fine on Windows.
View Replies !
Query To Search And Retrieve File For Download
I am new to web development and have this challenge. I want to design a dynamic website for a college community using PHP and MYSQL. The objective of the site is to hold the annual report of all the departments such that visitors can select the desired department and the year of the report. The years could stretch back to as long as the college existed but I want to limit the backdate to 10 years for now. The visitor should be able to select a department and desired year, view the report online and also be able to download the same report stored in a directory in ms word or PDF format. What should the table structure look like? Also the PHP and SQL script to search for the stored files and make them available for download.
View Replies !
Can You Retrieve Information From Describe Table Query?
I was wondering if it is possible to extract the information from describe table to an application. So that a user may choose with which fields of a given table he wants to perform a search. More specifically is there a query that will return me for example the fields for that table and their respective types, that may be forwarded to something like PHP, and print them to the user?
View Replies !
Insert And Retrieve BLOB Data Into MySQL Database, And Retrieving To Webpage
Fairly OK with the MySQL side (created database, and queried ok, getting the responses I want, etc!) Now have 2 photos i want to attach to each record in database table.(don't know if they should/can be part of the table, or just reference a path to location, given that this is a website, and will need to be updated quite a lot as I go) What I want is that, when a query is sent, the resulting records must pull the images with them , and display on results page.
View Replies !
Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing). Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort. So, in this case I should get 25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table) Why do I go about writing such query?
View Replies !
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
View Replies !
Add Data To Tables
Iam using full text searches but need some help with them. I just want to know how to add data to the tables etc. Please help me out as I dont know much about MySQL.
View Replies !
Data From Various Tables
I want to get a list of all files and folders inside of a certain folder. One thing is important: can I get rid of all the duplicate data that arises from both the entries in `folder` and in `file` having entries like `name` and `last_modified`? When I just use select * from `folder`, `file`, these show up doubly. This is actually my main problem... So, how would I do this? So basically, there is a certain `folder_id` which I want to examine. All entries in `folder` that have `parent` = that ID should be returned, and all entries in `file` that have `folder_id` = that ID should be returned as well. It doesn't need to be recursive: just one folder's contents are needed.
View Replies !
Data From 2 Tables
SELECT t1.name, t1.items, t2.name, SUM(t2.total) AS tt FROM `table1` AS t1, `table2` AS t2 WHERE t1.name = t2.name AND tt >= 20 GROUP BY t2.name ORDER BY t1.items I am trying to order the items from table1 based on the total from table2. The problem comes from the WHERE tt >= 20, I always get an error from this. Any ideas on how fix this?
View Replies !
Data From Two Tables
I want to output data from two different tables in one thing of code i have one table of songs with artist, name, desc etc....but i have another one with ratings. The rating table has a column of an id number that corrosponds to the auto_increment key column of the songs table i want to get just the rating column from one table and all of the columns from the other i have $query = "SELECT * FROM songs"; but i want it to be like $query = "SELECT * FROM songs AND SELECT rating FROM ratings WHERE id={$songid}"; is there any easy way to do this?
View Replies !
Getting Data From 2 Tables
I have 2 tables table 1 id date other infomation.... table 2 id date other infomation.... I want to know how to get ALL records from both tables, sorted by date. Full joins, two outer left joins, and combine. Can someone please give the statement(s) I need to get the info out. I was thinking of just creating a new table, and dumping the values from both the other tables, but there must be an easier way.
View Replies !
Comparing Data From 3 Tables
I am trying to wrap my head around the sql statement needed to get the data I need by comparing three tables. I have three tables: authors, submissions, and ratings. I need to see all the submissions that a specific author has not rated. In other words, I compare the submissions table to the ratings table and find instances where there is a submission but not a rating from a particular author (signified by the row rater_id) I've tried different combinations of INNER and OUTER joins, unions, subqueries... and nothing is working for me.
View Replies !
Comparing Data In 2 Tables
I have a table of users which is updated once a week by the client sending me a spreadsheet, and me uploading the CSV to my scripts which then import the data into the user table. This all works fine. However instead of sending me incremental data that can be just imported, the client has now taken to sending me a complete data list. The problem with this is that I cannot just empty the user table and re-populate with the new data as I will lose the primary key values which I use elsewhere. My solution to this was to create a new table for importing with the same structure as my user table, import the csv to there, then somehow compare the tables and add the rows missing from the import table into the user table. My question then, is this possible using a SQL query. Can I compare 2 tables, and add missing data from 1 into the other as new records? I could code a software solution, but with around 30k records in the user table I think a SQL query would be the best approach.
View Replies !
Dividing Data Into Two Tables
I was wonder at which point it is wise to divide data into two tables rather than have it all in one table. For example, a web forum should have the thread information in one table and the actual content in another table (these two tables are of course linked). What is the case with a table that contains user information such as uid, username, password, session_id, name, address, phone, email etc. Some of the data is requested on every page load (such as session_id and uid) and other data is only requested on specific pages (name and address is only requested when the user profile details are needed). Will I gain any significant performance advantage if I separate the user information into two tables, one that contains the data selected on every page request and another table containing the information for less frequently selected data? All fields are either of the type int or varchar. Let's say there's a maximum of 30 columns in the user information 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 !
Inserting Data Into Two Tables
if I want to insert data into two tables how do I do this? Is this right? I'm guessing not as it doesn't work What should I do? $query = "insert into requests (word,status) values ("$req","pending") AND insert into known_by (word,user_name) values ("$req","$user_name")";
View Replies !
Retrieving Data Across Two Tables
What is the easiest way to join one table to another table in a select query? Here's the break down. I've written an MLS IDX solutin that has pretty much every field I need in one table (br2data). I can handle that. But now my client wants the ability to add "acreage" as one of the search criteria. The problem is acreage is in another table(br2feats). The common key between the two is `mls number` To further complicate this is the two worded column header (`mls number`) How do you represent that in a join? br2data.`mls number` So my question is what is a way to write this with a select statement such as: select * from br2data, br2feats where < br2data criteria> and <br2feats acreage criteria>
View Replies !
Fetch Data From 3 Tables
I have 3 tables in my marketplace/store. Please note that products can be stored in multiple categories, hence the need for the joining table products product_id name title description categories id name parent_category product_category product_id category_id How would I select and list 30 products for any given category, like category ID #1 for example?
View Replies !
Selecting Data From 7 Tables
I have 7 tables in database. All of them shares a common coloumn called PMID.. i want to select data from all these 7 tables on the bases of PMID and am using the following query.Code: $query = "Select * from pmstep1,pmstep2,pmstep3,pmstep4,pmstep5,pmusa,pmca where pmstep1.pmid = pmstep2.pmid = pmstep3.pmid = pmstep4.pmid = pmstep5.pmid = pmca.pmid = pmusa.pmid ='shasha'"; but when i execute it on database using phpmyadmin. or command line to check my system hangs and it start to behave like 386 computers.. Please guide me that how can i select data based on this common coloumn from 7 tables. i never used JOINS and am not sure whether it has to do with joins or not.
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 !
Counting Data Using 2 Tables
I have two tables, one has students information in, the key fields for this problem are StudentID which is the Primary Key and Gender. The second table has details of reports the students have done, the key fields required for this problem are ReportID (PK), StudentID, ReportDay, Report Date. As detailed below. Table 1 called Students StudentID (PK) Gender Table 2 Called Reports ReportID (PK) StudentID ReportDay ReportDate What I need to do is count the number of Boys who have been on report on Monday between two set dates, I can query Table 2 to get the total number of students who have been on report on Monday between $StartDate and $EndDate but I don't know how to extract the data from Table 1 so that only Boys are counted. The common link is the StudentID, unique in Table 1. Code:
View Replies !
Pulling The Data From Two Tables?
i already experienced this before the problem is i can't recall the right function to use to pull data from MySQL. Let say i have two tables TABLE 1= cclp_players TABLE 2= cclp_players_stats Now i'm going to use cclp_players to pull data from cclp_players_stats.
View Replies !
Insert Data Into 2 Tables Using Php
i was wondering how i would go about insert data collected on a php form into 2 separate tables. i want to collect email and password for an admin table and everything else for a separate table why cant i just do this? $query = "INSERT INTO user_admin (s_email, s_password, salt) VALUES ('$email', '$encrypted', '$salt')"; mysql_query ($query) or die ('Could not register new account.'); $query = "INSERT INTO student (s_forname, s_surname, s_uni_id, s_email, s_course,) VALUES ('$forename', '$surname', '$uni_id', '$email', '$course')"; mysql_query ($query) or die ('Could not register new account.2'); the email and password get stored into the user_admin table but nothing is collected for teh student table do the values such as $forename need to be declared anywhere else before?
View Replies !
Displaying Data From Two Tables
have two tables table 1 = "product_list" with the following fields: ID prodct_name unit_price parent_category_id data displays as follows: id product_name unit_price parent_category_id optional_stock_id 1233 Brunsfelsia 20lt 125.50 344 table 2 = "category_structure with the following fields: Id category_name parent_category_id id category_name parent_category_id 341 Plants & Trees 0 342 Conifers 341 343 Plugs & Trays 341 344 Standards 341 345 Shrubs & Ground Covers 341 346 Palms 341 347 Toparies 341 ........
View Replies !
Select Data From 3 Tables
How to select data from three tables with one query? table1 {id,field1} table2 {id2,field2} table3 {id,id2} I know the 'field1'. In the 'table3' there are connections with the 'table1' and 'table2' id's defined. So i have to find out the 'id' for the 'field1' in the 'table1', then to look in 'table3' for the 'id2' that is connected with the 'id' and in the end return 'field2' with the found 'id2'.
View Replies !
Displaying Data From Three Tables
I have three tables I am working with. There is one table I would consider the "main table" which includes log entries. The log entries in the "main table" store username as a number. A second table has a primary key of a number and the number maps to an actual active directory username. How can I select data from these three tables but have it display the actual username instead of just the userid number (which in and of itself doesn't mean anything)?
View Replies !
Using Sum And Selecting Data Fom 2 Tables
Here is my prob.. ADMIN TABLE --------------------------------------- adm_id | adm_login --------------------------------------- FILE TABLE ---------------------------------------------------- id | file | size | adm_id ---------------------------------------------------- Now the result i want is to list all admins with there sum of total file size everyone have uploaded eg..
View Replies !
|
TRACKER
