MYSQL :: Getting Totals From 5 Tables In One Query...

Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments

The fields are:

invoices
--------
InvoiceNo
InvoiceDate
CompanyNo

invoicedetails
--------------
InvoiceNo
ProductNo
Quantity
UnitPrice

invoicepayments
---------------
InvoiceNo
PaymentDate
PaymentAmount

For each row in invoices there will be 0 or more rows in
invoicedetails and invoicepayments, with the InvoiceNo field linking
everything together (i.e. one to many relationship between invoices
and invoicedetails and invoicepayments).

I need a query that will give me a list of invoices that still have
money outstanding on them.

To manually do this I would loop through the invoices table and for
each InvoiceNo I would gather all the matching rows in invoicedetails
and invoicepayments. To get the invoice total I would multiply
Quantity by UnitPrice for each invoicedetails row. To get the total
paid I would add up the PaymentAmount. Then I'd compare the invoice
total and the payment total and if the payment total was less than the
invoice total I'd know that there was still some money outstanding on
that invoice.

Now, how do I write a single query to do this? I using MySQL 4.0.20
(unfortunately because I don't control the server I can't upgrade to a
version of MySQL that support subqueries). I'm guessing I'll need to
use the SUM() function to add things up, and GROUP BY to group the
invoicedetails and invoicepayments so I only get one row per invoice.

I can get a total for each invoice by using the following query:

SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal
FROM invoices, invoicedetails
WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo
GROUP BY invoices.InvoiceNo

However I'm at a loss as to how I would modify this query to also
total up the invoicepayments to give me a PaymentsTotal and then
calculate the difference between the InvTotal and the PaymentsTotal to
figure out if there is still money outstanding.

View Replies !

Gathering Totals From Multiple Tables
I've got a little bit of an issue which I need a little bit of mysql-guru help with. I want to get the top 5 users who have authored the most articles, and how many articles each has authored (total). The problem is, I have 3 tables in which I store the articles. I've got articles_faq, articles_kb, and articles_ref.

Each of these has an auto_increment field 'id', and an 'author' field (there's obviously more fields, but they aren't relevant).

How would I get this data in a single query? Is it even possible using MySQL?

Although I'm improving my MySQL skills and knowledge quite a bit, this is beyond what I'm capable of, that's for sure.

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 !

Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes:

SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood
from survey where jobclass = 'Salaried' group by good

That returns this:

numgood | scoregood | totgood
27 | 4 | 108
70 | 5 | 350

What I need are results that include no votes cast for the other values:

numgood | scoregood | totgood
0 | 1 | 0
0 | 2 | 0
0 | 3 | 0
27 | 4 | 108
70 | 5 | 350

View Replies !

Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement?
so far I have:

select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date)

but this doesn't include the 'zero' days (days when there were zero messages).

View Replies !

Totals/Mean Values
I assume this is a very simple question, I just don't know the answer!
I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc.
What I want to do is:
a) Total the price columns for each and take the average price - have all of this calculate automatically
b) Also call on these total values from an intro page displaying the totals for each year
First, is this possible?
Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic

View Replies !

Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish:
I want to be able to tell how many rows in a given table, and for a given INDEXED
column, carry any given ID.
Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose
one ID is: '56007'. I want to be able to tell how many rows in the entire table have
that ID. I know that I can use the select keyword, but i'm wondering if there's another
way, because the table i'll be doing this for can be up to 350 Million rows, and the
vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs.
I am only concerned with that small percentage of rows that have duplicate IDs

View Replies !

Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows:

field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40

how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table??

for above example, the output should look like:
field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
Total 50 | 100 | 300 | 400 | 200

View Replies !

Computing A Difference In Totals.
I am using php/MySQL 4.0 ..

If I had a table consisting of:

team l points
-------------------------------------
teamA l 15
teamA l 10
teamA l 5
teamB l 5
teamB l 10
teamC l 5

if (mysql_query("SET @rank = 0;", $conn))
{
if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn))

How would I get the result below?

rank l team l points l behind
--------------------------
1. teamA 30 0
2. teamB 15 15
3. teamC 5 25

I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?

View Replies !

Showing Totals And Subtotals In One Row
I have a table with the following fields:

ContractID | CustomerID | ProductID | Quantity

For each Contract there is one record: Who has ordered which product in what quantity.

Now I'd like to generate a report that shows:

- which products were ordered (SELECT ProductID ... GROUP BY ProductID)
- at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC)
- and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery?

Sample-Output:

P_ID - Quantity - Customer's quantities
----------------------------------------
1230 - 10'000 - A: 2'000, B: 8'000
1240 - 8'000 - A: 7'000, C: 500, D: 500
1120 - 6'000 - C: 6'000
...

How shall I build the SQL statement?

View Replies !

Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example:

select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC;

+-----------+------------+----------+
| a | b | Total |
+-----------+------------+----------+
| z | 2004-01-14 | 24 |
| x | 2004-01-05 | 22 |
| b | 2004-02-11 | 20 |
-------------------------------------

Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.

View Replies !

Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately.

So far the closest I've come is:

// to display the total for one day
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date='2007-05-25'

OR

// to display the total for all days
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date BETWEEN '2007-05-24' AND '2007-05-31'

With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.

View Replies !

Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004.

With the following table structure:
TABLE_A
id
date
amount

My current set up is like this, based on calendar year:

PHP

// get a list of years in the db
$years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date))
FROM table_A r
ORDER BY YEAR(r.date) ASC");

foreach ($years as $s) {
    $yearSum = $db->getOne("SELECT SUM(r.amount)
    FROM table_A r
    WHERE YEAR(r.date) = '$s'");
    //echo something here
}

ISSUE1:
I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years.

ISSUE2:
I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.

View Replies !

Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is:

- Specify a Date range
- Count number of rows in that range
- Get column totals for that range
- Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :)

This is a large table with many columns so here is what i had planned:

//OPEN CONNECTION HERE, SET DB
//First query gets date range:
$result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'");
//now we get number of rows:
$num_orders = mysql_num_rows($result);

After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?

View Replies !

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction.

Sound doable?

MySQL 4.1

Data looks like this:

user, amount, date
==============
1, 50, 2003-11-23
2, 34, 2004-10-04
3, 45, 2005-08-30
3, 98, 2006-04-02
3, 76, 2000-02-03
2, 91, 2000-12-04
1, 11, 2003-11-05
3, 22, 2003-03-06
4, 34, 2006-03-07
5, 45, 2006-06-24

I figure I can group by userID but how do I get the date of the most current transaction?

Using the data above, the query would return:
1 (user) 61 (subtotal) 2003-11-23 (last transaction)
2 (user) 125 (subtotal) 2004-10-04 (last transaction)

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 !

"Totals" Row For Columns
Looking for the result below from the sample table listed. Using MySQL 4.0/php. ROLLUP is not supported in my version of MySQL ...

table1
--------------------------
Person l Units l Amount
--------------------------
person1 l 7 l 11
person1 l 8 l 11
person2 l 13 l 6
person2 l 13 l 7
person3 l 11 l 7

SELECT Person, SUM(Units), SUM(Amount) FROM table1 GROUP BY Person

person1 l 15 l 22
person2 l 26 l 13
person3 l 11 l 7
"totals: l 52 l 42" = desired result

View Replies !

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.

View Replies !

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.

View Replies !

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....

View Replies !

Query From Two Tables As If It Were One
I have two tables with identical columns videos and photos.

I basically need to do this:

SELECT * FROM MERGE(`videos` `photos`) ORDER BY `created` DESC

So they can be displayed in chronological order on a result page.

View Replies !

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.

View Replies !

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?

View Replies !

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 Replies !

FLUSH TABLES Via C Query API
Using FLUSH TABLES via the C query API mysql_query() hangs if the table is
locked already. That is to say, nothing prevents me from running a LOCK
TABLES twice; it won't tell me "it's already locked, don't try to run a
FLUSH".Anyone know how to find out if a table is already locked? And don't say
"call the DBA" :)

View Replies !

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.

View Replies !

How To Query Two Separate Tables
What query should I use if my objective is to check if a record exists in either of the two separate tables.They have the same column name.

Ex. Column Name - supplier, table name - ros_poe_entry and item_supplier.

View Replies !

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>";

View Replies !

Query For Derived Tables
I'm having a little trouble with the following query.Code:

SELECT * FROM
(
SELECT Songs.SongId, Songs.SongTitle FROM
Songs INNER JOIN SongGenre ON (Song.SongId = SongGenre.SongId)
WHERE SongGenre.GenreId = (SELECT GenreId FROM Genres WHERE Name = 'Blues')
UNION ALL
SELECT Songs.SongId, Songs.SongTitle FROM
Songs INNER JOIN SongMood ON (Song.SongId = SongMood.SongId)
WHERE SongMood.Mood = (SELECT MoodId FROM Moods WHERE Name = 'Sexy')
) AS SearchResults
GROUP BY SearchResults.SongId
HAVING Count(*) = 2

The syntax error i get is that each derived table needs an alias. I've tried a few different things but they haven't seemed to solve the problem (other syntax errors arise).

View Replies !

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

View Replies !

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)

View Replies !

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?

View Replies !

Insert Into 2 Tables In 1 Query
how do i insert into 2 tables in 1 query?

View Replies !

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?

View Replies !

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?

View Replies !

Query Involving Many Tables
How do I select pid when ggcx='y'?.....

View Replies !

How To Rename All Tables In One Query
I have a big database of 250 tables and i want to rename all the tables like:

myproject_tb1

myproject_tb2

and so on in one query.

View Replies !

Query Joining 26 Tables
Is there any way a MySQL Query joining 26 tables wont become absurdly long?

View Replies !

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?

View Replies !

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.

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 !

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?

View Replies !

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.

View Replies !

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 .....

View Replies !

Union Query To Join 4 Tables
I am trying to make a union query to join 4 tables. I have reduced the tables to just 5 fields and made sure that the field types and names are the same. I keep getting an error message ODBC call failed.

I have tried various combinations of the tables and find that I can use any 2 of them but as soon as a third is included in the statement the query fails.

Is there a limit on the number of tables in a union query. The union query looks like this in its simplest form when I have made sure that the fields names, types and position match but I have also tried by specifically naming the fields in the same order for each table

select * from T1 union select * from T2 union select * from T3 UNION select
* from T4;

View Replies !

Get Rows From Multiple Tables In One Query
Is there a way to select rows from multiple tables in one query?

Say I have the following tables and columns:Storestore_idItemitem_iditem_store_id

I want to get a store by it's id + all the items associated with that store id. Do I have to make two separate queries for this? One to get the store, and another to get all the items for that store.

View Replies !

How To Query Multiple Tables Properly
I tried to use the below code, but its only check the first row in the table only. Its not checking all the rows in a table.

PHP Code:

Â SELECTÂ books.bid,books.desc,books.imgÂ FROMÂ books,visitor,statsÂ WHEREÂ visitor.ipÂ !='$vip'Â ANDÂ stats.hitsÂ <Â '1000'Â ANDÂ books.bidÂ =Â visitor.bidÂ ANDÂ books.bidÂ =Â stats.bidÂ

View Replies !

Sql Statement Query For Multiple Tables
Ive been trying to put together a php page for the last couple weeks that does a query on multiple tables at 1 time, and returns the results from each. In other words, i put in a search, it searches all the tables, then returns the results from whatever tables it finds my data in. each table is extremely different from the other, and as is the data. Im pretty sure that its just my statement that is wrong. Here is my statement but I cant seem to get it working right.

select 'table1' results from table1 where field1= '$var' or field2 = '$var'
union
select 'table2' results from table2 where field1 = '$var'
union
select 'table3' results from table3 where field1 = '$var'

$var is my search that is put in.

View Replies !