MYSQL :: How Do I Generate Results Based On Totals Of Another Table But For 1st Table?

How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?

TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+

TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+

View Replies !

Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this:

SELECT A.id AS Id, A.nome AS Nome
FROM main_table A
JOIN joined_table JT ON JT.id = A.id_cat
ORDER BY ("first whoever have a row in" another_table AT)

another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first...

I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)

View Replies !

Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query?

Example result for ProductX:

View Replies !

Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using:

***********
SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC
***********

I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?

View Replies !

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

View Replies !

Generate Table Of Week
Is there a way to generate a table automatically that looks like this:

YearWeek StartDate EndDate
-------------------------------------
200701 2007-01-01 2007-01-06
200702 2007-01-07 etc.

Ideally, the query would generate all the YearWeeks from year 2000 up until CURDATE, not including the current week (i.e. all "complete" weeks).

Another option would be to simply store YearWeek in a table, and automatically select StartDate and EndDate accordingly.

I guess another option would be not storing it in a table at all, simply generating it at runtime.

View Replies !

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

View Replies !

Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

View Replies !

MySQL Monitor To Generate SQL Script For Table
Is there a way to get the mySQL monitor to generate the SQL script for the creation of a table? I'm kinda looking for the same thing SQL Server does to allow you to preview the SQL script for a table.

View Replies !

Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows:

Customer table contains:
1) customer_email_address
2) customer_newsletter (value 0 or 1)

Visitor table contains:
1) email

The visitor table contains email addresses from customers that have signed up through another system.

I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.

View Replies !

Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Is There A Simple (!) Way To Auto-generate And Insert 10000 Sample Records Into A Table?
I want to test the response times and performance of MySQL DB with a large table.
For this I need a way to insert thousands of automatically generated records into
a previously created table.

Is there an easy way to achieve this?

Is there a script feature which let me do something similar like

For i=1 to 100000 DO (
INSERT INTO TAB234 VALUES (i, randomNum(1,10), randomChar('a','z', length=12), 'ALWAYSTHISTEXT') )

View Replies !

Generate Multi-results From Date Range
I am running MySQL 5.0.15. I am not an SQL expert. I have the following situation: I have a simple table that holds brief info on some actions that we expect to happen.

actions (
id INT unsigned PRIMARY,
name varchar(128) not null,
start datetime not null,
end datetime not null,
)

A record could have time values that both happen on the same day or span one or more day boundaries. We are not creating a "calendar" but the display interface of a monthly calendar view is useful. If I have the data: Code:

View Replies !

Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table

This is a SQL that I am trying to use

UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000,
SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000,
SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000
WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1;

I got error

"ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO
LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SY' at line 1"

I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?

View Replies !

Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email.

I want to delete the rows completely having the duplicate email.

I found a way out is to use some thing like this:

Code:
insert into newtable (email)
select distinct email from oldtable
But this way, only the email column is populated in the new table. I want all the other columns as well.

View Replies !

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.

View Replies !

Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement:

UPDATE ApplicationTbl
INNER JOIN AcademicTbl
ON ApplicationTbl.CampusID = AcademicTbl.CampusID
AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period
SET ApplicationTbl.App_Status = 'Qualified'
WHERE AcademicTbl.Sem_OnCampus >= '1'
AND AcademicTbl.GPA >= '2.4'
AND AcademicTbl.Judicial_Sanction IS NULL

It keeps saying its wrong. Even though I know its not

View Replies !

Selecting From One Table Based On Another?
I have a table for products

and another table for the type of product it is like this:

I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc.

but how can I then select a product based on criteria from the types table

say I wanted to select all products that are type = "Band" and type != "Fancy" ?

View Replies !

Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.

View Replies !

Deleting From A Table Based On Record Id
I need to execute the following query:

delete from history where id=(select min(id) from history where user = username);

this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause"

How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.

View Replies !

List Available Items Based On Prerequisite Table
I'm trying to develop such a scheme:

TABLE Prerequisites
PrerequsiteID (FK to Upgrades table; states a prerequisite for UpgradeID in the other column)
UpgradeID (FK to Upgrades table as well)

TABLE BuildingUpgrades
BuildingID (FK to Buildings table)
UpgradeID (FK to Upgrades table; states what Upgrade Building already has)

So it's a Building -> Building_Has_Upgrades -> Upgrades -> Upgrades_Have_Prerequisites thing (Building has 1..n Upgrades, Upgrade has 1..n prerequisites). In order for Building B to have an Upgrade U, that Building B has to have all the Upgrades which are required to have Upgrade U.

What I want to do is to list all buildings with all upgrades available for them. I've been trying for almost whole day to solve this issue with different inner, left and right joins, subqueries and what not but I can't really do it in a clean way.

The only working thing is a query, which I am ashamed of because it seems like a real waste of CPU time to me:

SELECT BUPG.BuildingID, PRE.UpgradeID, COUNT(PRE.PrerequisiteID)
FROM BuildingUpgrades BUPG
JOIN Prerequisites PRE ON BUPG.UpgradeID = PRE.PrerequisiteID
GROUP BY BUPG.BuildingID, BUPG.UpgradeID
HAVING COUNT(PRE.PrerequisiteID) =
(
SELECT COUNT(PrerequisiteID)
FROM Prerequisites
WHERE UpgradeID = PRE.UpgradeID
GROUP BY UpgradeID
)

What it basically does is takes all the available Upgrades, counts prerequisites that have been met by the building and checks if it's equal to the number of all prerequisites for that Upgrade. And it works but I still think there is a much better way to do it.

View Replies !

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

View Replies !

Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model?

By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one :

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM ecom_categories AS level0
LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID IS NULL
ORDER BY level0_name, level1_name, level2_name, level3_name

View Replies !

Update Based On Row Offset From Begining Of Table?
Is there a way to update a table row based on its offset from the begining of the table. I want to be able to update based on a rows position when its brought up when all rows in a table are selected. ie; select * from table.

View Replies !

Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records.

I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are.

I have found some
SELECT COUNT( * ) AS num_entries, title
FROM title_table
WHERE title >1
GROUP BY title

But this doesn't seem to work, although looks like it's on the right sort of lines?

I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!

View Replies !

Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag:

resource - 1:M - tagged_resource - M:1 - tag

So a resource can have many tags and a tag can belong to many resources. The data would look like this:

RESOURCE TABLE
id: 1
name: resource1

TAG TABLE
id: 1
tag: new
id: 2
tag: tested

TAGGED_RESOURCE
resourceID: 1
tagID: 1
resourceID: 1
tagID: 2

How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE?

I have tried using 'IN' but it acts like an OR operator rather than AND.

View Replies !

Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city.

What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'.

So is it possible to make those 2 query in one so I do not need to do a php loop:

SELECT city, COUNT(city) FROM profile GROUP BY city;

then

UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city

View Replies !

Multiple Table Search Based On User Input
I'm working on a database containing our research data. I know very little about PHP, MYSQL, or database structure (except keep like data together in different tables and have a key field in each).

I'm currently working with 3 tables (tbl_2006 and tbl_2007 structures are identical): ....

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 !

How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image.

The file with the new data is a .txt file, a sample of which looks like this:

v_products_modelv_products_imageEOREOR
5361453614.jpgEOREOR
5361553615.jpgEOREOR
5372453724.jpgEOREOR

The beginning part of the table looks like this:
+----------------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------+------+-----+---------------------+----------------+
| products_id | int(11) | NO | PRI | NULL auto_increment |
| products_type | int(11) | NO | | 1 | |
| products_quantity | float | NO | | 0 | |
| products_model | varchar(32) | YES | MUL | NULL | |
| products_image | varchar(64) | YES | | NULL | |
| products_price | decimal(15,4) | NO | | 0.0000 | |
| products_virtual | tinyint(1) | NO | | 0 | |

From what I've read over the past few days...I think I need to:
(a) delete the EOREOR column from the text file;
(b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.

View Replies !

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

View Replies !

Retrive Record Based On Results
Is there anyway to create a loop in mysql so that I can use a variable to return a record, and then get variable from that record to return another record and so on until the variable from the last result set it 0

View Replies !

Grouping Results Based On Internal?
I have in a database which stores insurance quote requests.

I want a query that counts the number of quotes by each referrer, grouping quotes made within a 10 minute period as 1 quote

Here is an example of the table and the wanted results.
TABLE
ID referrer type time ip
1 XADW0124 MC 1161683434 82.7.164.59
2 XADW0124 MC 1161683464 82.7.164.59
3 XADW0124 MC 1161683484 82.7.164.59
4 XADW0124 PC 1161684425 62.173.110.62
5 XADW0139 MC 1161685322 195.137.84.105

RESULT
ID referrer type time ip
3 XADW0124 MC 1161683484 82.7.164.59
4 XADW0124 PC 1161684425 62.173.110.62
5 XADW0139 MC 1161685322 195.137.84.105

View Replies !

Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.

View Replies !

Showing Results Based On A Date Range
I have a calendar application, which stores dates as characters in its own column, ( was designed externally and using a now defunct openforge project)
So y = year m = month and d = day.

So the result would be ....

View Replies !

Joining Two Table Results
I've got two tables.

Table 1 (ID, DATE, TOY)
Table 2 (ID, DATE, TOY, DESCRIPTION)

I'd like to join them so the results are ID, DATE, TOY, DESCRIPTION meaning I want table 1 to look like table 2.

All my attempts have resulted in millions of rows being returned, not the 77k there are. I'm assuming this a left join job? I've read around the forumn and only encountered answers that go up above what I'm trying.

View Replies !

Concat 2nd Table Results Into First?
Given three tables used to track multiple email addresses and phone numbers for a person:

Table 1: id, name, address
Table 2: table1_id, email
Table 3: table1_id, phone

How can I do a select to end up with all emails and phones concated together into a single row per individual:

id, name, address, "email,email,email", "phone,phone"

The solution needs to work with MySQL 3.23 (or possible MySQL 4.0). 4.1 is NOT an option, so group_concat is out.

View Replies !

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

View Replies !

Sum Results From Union On Single Table
select sum(x)
from
(select sum(player1score) x from matches where player1='name' and tournamentid=1)
union all
(select sum(player2score) x from matches where player2='name' and tournamentid=1);

My table has player1, player2, player1score, player2score, tournamentid

I need to get all the scores of player1 or player 2 as needed.
The statement gives -
ERROR 1248 (42000): Every derived table must have its own alias

View Replies !

Multi Results From Table Fields
I'm trying to get results from querying two tables - let's call them users and profiles.

Within the profiles table, there are 3 fields; the user id (uid), a question id (qid), and the text value of the response (resp).

What I'm trying to do is to get the user information from the users table (SELECT * FROM users), dependent upon answers given to two separate questions.

Example: the user completes 3 questions, with a qid of 1, 2 and 3. Broadly, I'd like to do a SELECT using LEFT JOIN profiles ON users.uid = profiles.uid -but- I want to pull the info WHERE the response to qid 1 = "20 - 25" and qid 2 = "Male", thereby getting a list for which both the conditions apply (i.e. a list of users who are male and aged between 20-25).

I'm failing at pretty much the first hurdle; anyone has any suggestions before I go the php looping route?

View Replies !

Update Query Results To Another Table
So I'm learning, bear with me, this is probably pretty easy(but I am a bit perplexed) but I'm writing a php page for a cron. I am selecting a user name from a table basically selecting all row entries in that table with a certain username, and adding them together.

$query=mysql_query(SELECT UserName, SUM(AcctOutputOctets) FROM radacct GROUP BY UserName)

The output is this(when I run a query in mysql)

UserName..............SUM(AcctOutputOctets)

test..................... 345566

Basically i'm adding up all the users entries and totaling their Usage.

Now the question. I want to write that to another table named mtotacct.

format like this.

Username..............TotalOctets.

I'm just a little uncertain how to properly set this up to pass the results from the query to the UPDATE statement.

View Replies !

Geting Random Results From A Table
i'm trying to get random results from a table.

The goal is to get a new headline everytime the page is loaded (or reloaded).

The table named articles has the following fields:
headline - self explained
sumary - a brief description of the headline
area - which is a number from 1 to 9
date - the date (YYYY-MM-DD) when the article was posted

I can do that with simple arrays using the shuffle () command but it's not working with an associative array from a query result.

View Replies !

Select From Table And Count Results In Another
I have to tables table one containing airports and table two containg bookings. What I need to be able to do is count how many rows are returned form the booking table that matches each airport in the airport table.

So I might have in my airport table
Newcastle
Heathrow
Standstead
etc etc

and in my bookings table I have

Newcastle
Newcastle
Standstead
Heathrow
Heathrow

And what I want returned is
Newcastle(2)
Standstead(1)
Heathrow(3)

How can I go about doing this.

View Replies !

Using Results From One Table To Populate Column Names In Another
I have a table of gene names. I would like to use this table to populate the column names of a second table (all these columns will be of the same type ie INT(4) to represent expression level). The list of genes is large so I don't want to type in the columns by hand.

View Replies !

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.

View Replies !

Quering Works Except Need To Sort Results By Other Table...
I have the following query:

SELECT plugin_id FROM tc_plugin_lists WHERE (page_id='1' AND position='0') ORDER BY tc_plugins.sort_number ASC;

This almost works, the problem is the ORDER BY clause. It should order the results by the sort_number field from another table called tc_plugins. Now it throws an error...

SELECT items FROM other table ORDER BY other tables field values where field is same as in 'items' ASC

?

View Replies !

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

View Replies !