Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order.
I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So:
SELECT * FROM order_log WHERE product's group_id = 1.
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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.
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
Using Many Tables In A Query
I'm developing a script, which creates a bunch of tables for each registered member. And I need to sum up some statistical information, like records count, based on tables of each user, say: user1_posts, user2_posts and so on. How efficient would it be to use a query with its from clause consisting of thousands of tables in this case? Should I look for another way to do this?
Need To Query Two Tables
I am new to MySQL, but I'm sure the pros here can figure this one out pretty easily. There are two tables; one that lists all of the clicks from all members and one that lists all of the links. Basically, when the member logs in I want them to see a list of links that they have not clicked on. The clicks table has a user_id and link_id column. The links table has the link_id column and some other info.
Can You Query Against 2 Tables?
In one table I have a list of users and in the other a register to keep track of when they are in. The query I need to build will check that the user belongs to a particular group (in the user table) AND that there is no data against there name for today i.e. date("YY/mm/dd") in the register table....
Two Tables--One Query
I've got two tables: carddb_games and carddb_cards. Their structures are like so:Code: carddb_games { game_id tinyint(3) game_name tinytext } carddb_cards { card_id smallint(5) game_id tinyint(3) card_num tinytext card_name tinytext card_effects tinytext card_special tinytext Is it possible to make a query such that it will go through each game in carddb_games AND return a count of how many cards exist with a matching game_id within the carddb_cards table? I am not sure as to how to go about it, if such is even possible.
Two Tables In One Query
i'd love if anyone could help me out on the following situation: I do have a db with two tables for price comparisations - my example:table 'items' contains an entry with the itemno '123' and a basic price of '5.00' plus additional stuff like description etctable 'prices' contains 3 entries for the itemno '123' with the prices '6.00', '10.00' and '20.00' Now i need to catch several data from the table 'items' and additionally the highest price for the item '123' in one query. The tricky thing is, that an item possibly could have no entries in the 'prices' table and in that case the kinda default price from the table 'items' should be taken.
COUNT() 2 Tables In 1 Query
So I have these two queries: $result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); $result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); They output data like Month Year, Total Views So I can see how many total times a page was visited that month for a specific user_id. The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked. I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together. Is it not possible to do a count() on two tables in 1 query? Here's one of the many combined I tried. $sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)"; Can anyone help me here or is it not possible to do it in 1 query?
Need Query To Join 2 Tables Through A 3rd
I have a table called client (primary key = clientID), another called clientFamily (pk = clientFamilyID) and a table that joins those two called client2clientFamily. The latter table only has two rows: clientID and clientFamilyID (no pk). On the client table, say the client has 3 children and 2 siblings. Those would go on the clientFamily table. The part I don't know how to do is how to assign those 3 kids and 2 sibs to that particular client. I know I do it via the client2clientFamily table but I don't know the query. I'm also not sure I'm thinking of this right. The fields on clientFamily start off like: clientFamilyID spouseFirstName spouseMiddleName spouseLastName spouseOccupation motherFirstName motherLastName motherOccupation fatherFirstName fatherLastName fatherOccupation I think that part's ok, but if I add: childFirstName childLastName childBirthday childSex and sibFirstName sibLastName sibOccupation how are, for example, a child's birthday and sex are going to be linked to the correct child? And a sib's occupation linked to the correct sib? Now I'm wondering if I need a separate table for clientChildren (and a joining table client2clientChildren) and another one for clientSibs (& client2clientSibs) instead of putting everything in clientFamily. Ugh.
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
3 Related Tables In One Query?
I want have these 3 tables: - Properties : contains a list of properties - Images : contains images (the urls of) - Attributes : contains a key=>value pairing for properties, any number of per. What i need to do is: Get all the properties in the database get the Image relating to the property, where the Image:ID = Property:mainimg get ALL attributes relating to the property, where the Attrib:Property = Property:ID how would i do this? I am aware of how to join two tables, but what about with 3?
Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks. I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it. i am guessing that it has something to do with joins but mysql really isn't my thang! I Have tried something like: select count(hotel.id) as atb, count(trainer.rec_id) as det, count(club.id) as ml, count(activity.id) as sl from hotel, trainer, club, activity but that returns the totalled amounts added together which is why I figure a join is needed. My aim is a online stats panel something like: Leisure Clubs Onsite: 2442 Trainers Onsite: 232 Hotels Onsite: 1978
SQL Query Across Multiple Tables
I wish to make a 'Latest Comments' section, but I am unsure of the most efficient query to use. I have comments spread across 6 different tables, named as below; cms_artistcomments cms_featurecomments cms_freshtalentcomments cms_newscomments cms_reviewcomments cms_wallpapercomments Each set up as follows; comment_id artist_id (changes for each table) comment_author comment_post comment_date What I would like is to extract 'so many' number of comments and in reverse date order, to give the latest 'so many' comments.
Query On Multiple Tables
I'm new to mySQL and databases and was hoping someone would be kind enough to help me out. I have two tables, events and images. I want to get the data from column image_path based upon two variables, $event_date and $event_id. The date is only in the events table but event_id is a foriegn key in the images table. I'm really struggling with the syntax and have got really good at generating errors!
Query Multiple Tables
I have a database called 'xxx', i need to query 3 tables inside this database called a, b, and c. Each table has a common field called artist: i need to select artist from all 3 tables (a, b and c) and group the results by artist. It has a catch of i needs them all to comply with a LIKE statement like below. So far i have: SELECT a.artist, b.artist, c.artist FROM a, b, c WHERE a.artist, b.artist, c.artist LIKE '$_GET[url]%' GROUP BY a.artist, b.artist, c.artist ORDER BY a.artist, b.artist, c.artist ASC I know this doesnt work but im guessing its along those lines on how it does work?
Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go about pulling back all the info for that entry across all the tables? I have tried doing this: WHERE main.Date = table2.Date AND table3.Date AND table4.date however it returns loads of data when it should only pull back the one entry.
One Query For Multiple Tables
I am using MySQL 4.0 I have 4 tables w/ the same columns. Table1, Table2, Table3, Table4, under database=survey. I want to count how many surveys have been completed under each Table. Using one query, I want the total of each Count(survey_id) from each table. Basically, Select Count(Survey_id) from Table1 + Select Count(Survey_id) from Table2 + Select Count(Survey_id) from Table3 + Select Count(Survey_id) from Table4. HOw can i do this in one query?
Query 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?
Update Query Between Two Tables
I am having a problem with getting an update query to work. here's what I am using: Update stck Set vendor = 'items.vendor' where prtn = 'items.prtn' I am getting a items table not found error. I thought this is what I needed to do to update many records from one table to another.
Query From Multiple Tables
I want to construct one single query that gets values from three different tables Table "A" has a column "id" Table "B" has columns "topicid", "topic" and "userid" Table "C" has coluns "userid" and "address" A.id links to B.topic_id. B.userid links to C.userid I want to get A.id, B.subject, C.address from all three tables in one query. Due to some other constraint, I must use left join with the first two tables: select A.id, B.topic from A left join B on (A.id = B.topicid); How can modify the above query to get C.address in the same query?
Query Two Tables For A Word
I have a table of words (dict), a basic dictionary. And i have a spell check page that will check if words are in the dictionary using this query: select * from dict where words = '$Words[$W]' I want to add a secondary table (dictlearn) with new words that the use can add. When I try this I keep getting an ambiguous error for words: select * from dict, dictlearn where words = '$Words[$W]' How can I query both tables for a word to see if there are any matches in either table?
Query On Multiple Tables
Tables: patient ------- patientID | name scans ------ scanID | scantype | scandate | patientID What I want to do is create a table that has a list of patient names and their latest scandate and scantype I can't quite figure out the syntax
Query Involving Two Tables
PHP Code: SELECT domains.full_domain FROM domains, pop_words WHERE (pop_words.word LIKE 'domains.domain%' OR pop_words.word LIKE '%domains.domain') I'm sure you can see what I am trying to do..... but I am getting no results!? (& I know there should be some)
Birthday Query From 2 Tables
I'm really new to mysql so please reply with a full explanation I want to show a list of users whos birthday it is on that day. I want the users login name and their age displayed. the problem i'm having is that the login name is stored in a different table than the birthdate and i really have no idea how to query 2 tables at once. the login name is stored in members table while the birthdate is stored in profiles. I have the following but as you can see it only displays id and age from the profiles table and the id in that table is numerical so it only shows: 1 28 3 30 ect. Plus i think it shows for the upcoming month rather than the day <?php $result = mysql_query("SELECT id, birthdate, EXTRACT(DAY FROM birthdate) AS birth_day, EXTRACT(MONTH FROM birthdate) AS birth_month, extract(year from now()) - extract(year from birthdate) as age FROM profiles where extract(month from birthdate) = extract(month from now())") or die("Query failed: " . mysql_error()); while ($row = mysql_fetch_array($result)) { echo $row['id']; echo " " . $row['age'] . "<br>";
Joining Multiple Tables In One Query
I was wondering if anyone could see the best way to lay this functionality out. I have the following tables and fields (comma seperated part). Assume the vendors userid = 5. I want to grab a list of all users that are within that vendors territory. [table] users userid, zipcode * multiple users per single zip code [table] vendors userid, vendorid [table] assoc_zipter zipcode, territory * multiple zip code per single territory [table] assoc_venter vendorid, territory * multiple territories per single vendorid Here is the SQL written out. SELECT * FROM users WHERE * vendors.vendorid=5 * vendors.vendorid = assoc_venter.vendorid * assoc_venter.territory = assoc_zipter.territory * assoc_zipter.zipcode = users.zipcode I am trying to get this all in one sql statement. Here is a sql statement that does not work: SELECT * FROM users JOIN vendors ON (vendors.userid=5) JOIN assoc_venter ON (vendors.vendorid=assoc_venter.vendor) JOIN assoc_zipter ON (assoc_venter.territory=assoc_zipter.territory AND users.zipcode=assoc_zipter.zipcode) GROUP BY users.userid
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
Fulltext Query On Multiple Tables?
I've built a database for an organisation, in it are 3 tables i need to include in a search, news, events, and locations. Each have a title and a description. What I've been trying to achieve (and failed at, hence me running out of time) is to have the user enter one search query, and the results page, paginated, returning matching results from all 3 tables, which will then be listed in the search as "News: ..." etc, and the plan was to order them by the fulltext score. I considered running three consecutive fulltext queries and combining them in PHP, but then I won't be able to limit the result sets for paginating them.
Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team. The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem. The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit. Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?
Making A Two Tables MySQL Query...
I'll try to explain this the best I can. Hopefully there are some people out there that can help me with this one since I am completely lost. :confused: First some information. I have two tables with this structure:
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..
MyOLEDB Show Tables Query
Is there a way to retrieve the names of the tables in a database if you are using the myOLEDB provider in a C# application? Unfortunately Show Tables doesn't seem to work.
Counting Records In 2 Tables Using 1 Query
I have these 2 queries. SELECT count(*) gifts FROM gift g WHERE g.this and g.that SELECT count(*) events FROM events e WHERE e.this and e.the other thing is there a way to put these into one query..... SELECT count(g.*) gifts, count(e.*) FROM gift g, event e WHERE . . . . so far nothing seems to be working .....
SUM Query By LEFT JOINing 2 Tables?
I'm having a parent and a child table: mysql> EXPLAIN child; +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | parent_id | int(11) | | MUL | 0 | | | quantity | int(11) | | | 0 | | | status | enum('r','p') | | | r | | +-----------+---------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> EXPLAIN parent; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | name | varchar(30) | | | | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.03 sec) In the child table I'm inserting all quantities related to the parent table. Status in here means whether the quantity was received ("r") or was paid ("p"). So, for each user in the parent table I wana find out how much he/she received and how much he/she spent to find out the total money he/she has. Ok, writing 2 queries for this purpose is easy, but what if I wana use only one query? Look at this:
Query List Of Tables In Database
Is there anyway I can query list of tables of a database just using one query string? But not using two seperate query strings - "USE [database];" "SHOW tables;"
Query Remote Tables Syntax
I have the same database & table on two unix servers that are in a remote location. One table has a couple fewer rows in it than the other. I need to find out which rows they are. I am pretty familier with Sql serve syntax, but cannot find the corresponding syntax for doing this in mysql. server a has the correct amount of rows (1 million) server b has 999,500 rows. I was going to use the following query to find the missing rows: Select a.pkey from table a outer join <server>.<db>.<owner>.table b where b.pkey is null this query doesnt seem to work for me. I read the manual web page about setting up obdc connections, but I didnt see how to do that on unix systems.
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')";
Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables... Projects -> id, project_name, project_type Variables -> id, variable_name Symbols -> id, symbol_name Mapped -> project_id, variable_id, symbol_id if I had to find (project_id=5, variable_id=2, symbol_id=3) AND (project_id=2, variable_id=14, symbol_id=1) AND (project_id=34, variable_id=78, symbol_id=44) How could I write a query that would return the results for these all at once? I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3
Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.
Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and forename from a tbl of users (they are always distinct) where user_id in that tbl equals friend_id in another table friend_id, which 'should' be unique ie there 'should' only be one row of a 'friend connection', but in the case of bugs etc this might not be true, what i want to do is select only the row of tblusers with the user details that are matched by join condition below, but only ONCE for each, ie not in as many times as there are the same connection in tblfriends. For some reason the result i am getting is a repitition of the 2 fields in tblusers (that i am selecting) for as many rows as there are in tblfriends that match the join condition. How can i make it only return a row once?
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
Use A Mapping Table To Get Query Results From Several Tables
Hi all, Ok, so I have 4 tables... Projects -> id, project_name, project_type Variables -> id, variable_name Symbols -> id, symbol_name Mapped -> project_id, variable_id, symbol_id if I had to find (project_id=5, variable_id=2, symbol_id=3) AND (project_id=2, variable_id=14, symbol_id=1) AND (project_id=34, variable_id=78, symbol_id=44) How could I write a query that would return the results for these all at once? I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3 ....
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
Seemingly Simple FULLTEXT Query Over Multiple Tables
Can't seem to get my head around this query. Here's what I have: I have 2 tables: TABLE a, TABLE b: Table a Columns: UID, FName, LName, Bio Table b Columns: UID, Specialty I want to search these FULLTEXT Indexed table columns (bio, Specialty) from the different tables for a string, and output the resulting UID and Username, one row only per UID (UID matches between tables)... To put it another way, if the search string is found in either of the 2 columns in the 2 tables, I would like to output the UID and FName from TABLE A. SELECT DISTINCT E.UID, E.FName, E.LName FROM contact AS E, brg_uid_specialties AS C WHERE E.UID=C.UID AND MATCH (E.Bio) AGAINST ('bubble noodle Oranges') OR MATCH (Specialties) AGAINST ('bubble noodle Oranges')" The resulting output from my sample tables is an enormous dataset (when it should only get 1 hit), and I can't figure out why... .
|
TRACKER
