MYSQL :: Count On Multiple Table Joins

Multiple Joins To One Table
I have a table called "tasks" which has, amongst others, 3 columns called

raised_by,
assigned_to,
parked_with

these columns contain the user ids of individuals who have specific responsibilities for the task. I have another table called "users" that contains all the user information, such as login names and passwords as well as two columns called

user_id,
display_name

I am trying to write a query that will return the display_name for the raised_by, assigned_to and parked_with fields.

View Replies ! View Related

Multiple JOINS On The Same Table
two tables:

table one "matches" has the following fields:
-match_id
-team_one (has values:Liverpool, Everton etc)
-team_two (has values:Liverpool, Everton etc)

table two "teams":
-team_name (has values:Liverpool, Everton etc)
-team_stadium
-team_manager....

View Replies ! View Related

Multiple Table Joins In A Select
How do I write a select query to join more than two tables? =20. Table A relates to table B and table B relates to Table C. I need to return fields from Table A and C that are related.

View Replies ! View Related

Multiple Left Joins In One Table
When doing multiple left joins in one table: for each join, which table is considered to be the "left" table (ie: all results must be returned).

View Replies ! View Related

Multiple Table Calls And Slow Loading Times... Joins?
just recently started using MySql and I think I've got most of the basics down - everything WORKS just not well. Essentially, I'm making an image gallery and the search/landing pages have thumbnails, pretty straightforward. The thumbnails are rollover slideshows of the images in the gallery so for each thumbnail preview there are a varying amount of actual thumbnail images that are loaded. The table structure is to this effect:

gallery table:
gallery_id, gallery_views, gallery_rating etc etc

thumbnails table:
gallery_id, thumbnail_link

So i may have an entry as such:
gallery_id = 21, gallery_views = 300, gallery_rating = 60

With several thumbnail entries:
gallery_id = 21, thumbnail_link = url/image1.png
gallery_id = 21, thumbnail_link = url/image2.png
gallery_id = 21, thumbnail_link = url/image3.png

What I have been doing thus far, say for the index page I grab the top 30 by views, is something like "SELECT * FROM galleries ORDER BY views DESC LIMIT 30" and then after bringing that into PHP in my while statement on each iteration I make a separate call that is like "SELECT thumbnail_link FROM thumbnails WHERE gallery_id = '$gallery_id'" and then output all the thumbnails for the rollovers. So let's say then for example that I want to show the top 30 by views, as well as the top 30 by user rating. At that point I'm making two calls to the galleries table, which arbitrarily we'll say contains 50,000 entries, but I'm also making 60 individual calls to the thumbnails table, which contains in some cases 20 or 30 thumbnail links per gallery and contains upwards of a million rows. Obviously my loading times are much higher than I would like them to be and I can't imagine this is the optimal way of making these calls. It seems like a fairly elementary concept but I can't seem to find something that works.

View Replies ! View Related

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

View Replies ! View Related

Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads
and posts. when i query my threads table with a join, i need to access
the users table twice to get the username of the first poster and last
poster. But how? I can only figure out how to get one or the other. Is
my design bad? eg

SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic,
username FROM DiscussionThreads, users WHERE
DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT
10 .

View Replies ! View Related

Joins, COUNT()ing And Grouping
I have a products table (products) and a category table (categories)

I would like to generate a list of categories with the number of products in each category.

The products table contains the category ID.

I was trying something like

SELECT
COUNT(a.id) as qty,
b.category_name
FROM
products a
INNER JOIN
categories b
ON
b.id = a.category_id

But then got lost on how to group it all together

View Replies ! View Related

Wrong COUNT() Result With Joins?
I'm using this query:

SELECT DISTINCT COUNT(Programme.ProgrammeID) AS Count
FROM Programme
INNER JOIN ProgrammeCategoryLink ON Programme.ProgrammeID = ProgrammeCategoryLink.ProgrammeID
INNER JOIN ProgrammeCategory ON ProgrammeCategoryLink.ProgrammeCategoryID = ProgrammeCategory.ProgrammeCategoryID
WHERE Programme.Enabled = Ƈ'
Which I was expecting to return the total number of records (11) that had Enabled = 1, but it returns 21 (there aren't even that many records).

I've messed around with GROUPING etc but I just can't figure it out. (By the way, the joins need to be there even though im not using them in this instance, as sometimes filtering is on ProgrammeCategory.ProgrammeCategoryID too).

View Replies ! View Related

Multiple Joins
My database has three tables with the following fields:

--tblIngredients--
IngredientID
Ingredient
IngredientInfo

--tblRecipeIngredients--
RecipeIngredientID
RecipeID
IngredientID
Quantity

--tblRecipe--
RecipeID
RecipeName
Directions

tblRecipeIngredients is a join table to link each recipe from tbRecipe with it's respective ingredient(s) from tblIngredients.

Now, I'm trying to write a query that will return all recipe names (RecipeName) that don't have each of the Ingredients specified. In other words, I'm going to have a form that allows the user to select their on-hand ingredients and I want the query to eliminate all the recipes that include ingredients that the user does not have on hand and return the rest. I hope I'm making sense here...

Anyhow, this is what I have so far, but it doesn't work the way I would like it to:

View Replies ! View Related

Multiple Self Joins
I am a newcomer to MySQL, from Access. My first project is porting a single-user, no-security Access DB to MySQL, largely due to Access size limitations, but I was also hoping for a sizeable improvement in speed. The data is a set of over 2,000 text files spanning around 130MB, with considerable growth expected over the next few years.

The app uses VBA to read in all the text and index the location of every word. A sample from one file is shown at the end. What is important is the dot-and-two-letter descriptor preceding each line, and the number of the block in which it lives. (A block is the range from one '._HS nnn' line to the next '._HS nnn' line.) ......

View Replies ! View Related

Multiple Joins To Same Field
I have two tables.
The first table (t1) has color codes that describe different parts of a book, such as page_edge_color, binding_color, type_color, and so forth.
The second table (t2) contains all of the color descriptions (it has two fields, one is the "code" and the other is "color_description"...e.g., "BK" = "Black".

I can get the description of one color from t1 by joining as follows:

Code:

SELECT
t2.color_description
FROM t1
LEFT OUTER JOIN t2 ON t2.code = t1.code

But how do I display the color description for more than one field in t1? In other words, how do I use the description table to describe multiple fields in the color code table.

View Replies ! View Related

Using Joins Or Multiple Queries
I have two very large tables, with relational id's. Would it be faster to
1. use a join on the two tables?
2. make two queries

If I were to make two queries would it be faster to use '...WHERE FIND_IN_SET(...' or to use '...WHERE IN(...'

View Replies ! View Related

Sum Two Tables With Multiple JOINS
i'm trying to get more efficient at SQL rather then creating tons of PHP work arounds to get the job done. I have two queries here for example. One query shows the Budget Cost/Hours of a Work Order, the other shows the RFI(aka Change Orders) for a Work Order. I want to sum the budget and the rfi in one query and get Total Hours/Total Cost. Here are the two queries ...

View Replies ! View Related

Multiple Complex Joins
How can one link two fields in one table to single field (Primary key) in another table in a single (left join) query, to return two values? e.g.

Table 1
ID (PK)
...other fields
Departure_ID
Arrival_ID
...other fields

"places" table
Place_ID
Placename (value to use in view)

I think this is illegal but, more probably, impossible; perhaps someone could suggest an alternative methodology.

View Replies ! View Related

Multiple Joins Expensive?
I need to extract some information which comes from two of several tables. I have a query with multiple joins - kind of like this:

SELECT
M.MessageID, C.ConversationID, M.SentDate, M.ReadDate, M.Read,
M.Sender, M.Receiver, M.Subject, M.Body, M.Answered,
M.LetterCategory, M.Type,
FROM Messages M
INNER JOIN MessagesInConversations C ON M.MessageID = C.MessageID
INNER JOIN Translators T1 ON M.Sender = T1.Username
INNER JOIN Translators T2 ON M.Receiver = T2.Username
INNER JOIN Admin A1 ON M.Sender = A1.Username
INNER JOIN Admin A2 ON M.Receiver = A2.Username

WHERE blah blah blah

My question is how inefficient is it to have so many joins? Is it ok, or should I restructure the data in some other way.

View Replies ! View Related

Inner Joins On Multiple Tables
I have multiple tables about 6 to be exact. I need to show columns from different tables in a swing application. How to I show colums from different tables in one table and then use the new table to create, update, delete and view. Do I need to create an inner join? If yes, how do I do inner join on multiple tables.

View Replies ! View Related

Multiple Counts And Joins
SELECT
tin.traffic_referer,
COUNT( tin.traffic_referer ) AS hits_in,
COUNT( tout.traffic_page ) AS hits_out
FROM traffic_in tin
LEFT JOIN traffic_out tout
ON tout.traffic_referer = tin.traffic_referer
GROUP BY tin.traffic_referer, tout.traffic_page
LIMIT 0 , 30
That's the code I'm trying to use but the result sets are coming up weird.

If theres no hits out then the results come up like they should:

http://babelfish.altavista.com | 6 | 0
But if there are any hits out then the results come up like this:

http://forum.phap.net | 6314 | 6314
The hits_in and hits_out always come up the same.

View Replies ! View Related

Problem With Multiple Joins
I have a database with three tables - eaGroups, eaGroupDelegates and eaDelegates.

Groups can hold multiple delegates and delegates can belong to more than one group, so I have used a link table (eaGroupDelegates).

What I need to do is return a list of groups that have a 'client' field set to a specific value, along with a count of the number of delegates in each group that don't have 1 in their 'deleted' field.

I thought the following statement should work, but it returns a high number of delegates, even though it should return none (i.e. there are actually no delegates in the groups for client 13). It does return the right groups, though.

SELECT eaGroups.id, eaGroups.name, eaGroups.parent, eaGroups.level, COUNT(link.delegate) AS delegateCount FROM eaGroups LEFT JOIN (eaGroupDelegates link INNER JOIN eaDelegates delegate ON link.delegate=delegate.id AND delegate.deleted=0) ON link.groupID=eaGroups.id WHERE eaGroups.client=13 GROUP BY eaGroups.id ORDER BY level, name

View Replies ! View Related

Multiple Joins Concatenated Into In A Single Row
I have some trouble understanding how I can make multible joins work the way I expect it to. I have a database that looks something like this: ....

View Replies ! View Related

Indexing Effeciently With Multiple Left Joins
Can anyone point me in the right direction here.
Tell me what to do, or where to find information?

I am trying to access a PHP page and it takes too long.
The problem is the SQL code is in not efficient.

How do you index effieciently with Joins?

The code has four Left Joins.
Looks something like:

$sql = "SELECT
distinct(hi.item_id),
hi.*,
CONCAT(c.contact_first_name,' ',c.contact_last_name) assigned_fullname,
c.contact_email as assigned_email,
p.project_id,
p.project_person,
p.project_color_identifier,
his.status_date
FROM helpdesk_items hi
LEFT JOIN helpdesk_item_status his ON his.status_item_id = hi.item_id
LEFT JOIN users u ON u.user_id = hi.item_assigned_to
LEFT JOIN contacts c ON c.contact_id = u.user_contact
LEFT JOIN projects p ON p.project_id = hi.item_project_id
WHERE $where" .

View Replies ! View Related

Using COUNT To Count Multiple Results
having a bit of trouble getting my head around how to set this query up. I've got a tracking script to monitor click-throughs for email campaigns I manage. It enters the users email address, ip, date etc. into a table each time they click off the email to the site.

I've got a query that works just fine to display every click, but what I'd like is for it to just display each email address once, with the number of times they've clicked through next to the address.

Here's the current query: ....

View Replies ! View Related

Joins Across Multiple Tables, Influence Which Item To Take If Only One Is Needed Of Each Kind
I'm trying to get a join across multiple tables to work where I have to walk across several pivot tables to get one of the selection criteria.

What I want is the latest entries for each supplied wp_terms (the category) in the wp_posts table. While fetching each one with its own query is easy, I'm stumbling when trying to build a query that returns the latest entry for a list of supplied categories. Group by doesnt seem to cut it, since it takes place before the order by.

Here is the query and its output (without grouping, this will return all entries for the specified categories): ....

View Replies ! View Related

Joins To Same Table
I have a table that contains two codes, both of which are expanded by the same table.
In an SQL SELECT I want to be able to expand both of these codes to their meanings.
Is this possible in MySQL. I am sure I read something about Table Alias at some point, but can not find the reference now.

View Replies ! View Related

Two Joins To Single Table
I am working on a small PHP project and I am wondering what the easiest way to join one table to another table "twice."

There are two tables involved, one is a list of users with a primary key userid and the user's full name. The other is a list of messages between users, with a column for the sender's userid, the recipient's userid, the message, a reply, and a few other metadata fields.

I am using the query "SELECT name, message, reply FROM comments, users WHERE sender = userid" which returns the full name of the sender, along with the message and reply. However, I would like to also display the recipient's full name. Should I perform a sepertate query from within the PHP on recipient, or is there a way I can perform the second join with the user table from the single MySQL query?

View Replies ! View Related

Question Regarding Table Joins
I want to set up a comment system where people both registered and unregistered can post, but I don't know how to write the query to retrieve the username. If someone is registered their userid will be stored in the comments table and referenced against the user table, but when they aren't registered their name will be stored in the comments table.
How can I query the database in a way that if there is no userid the username will be fetched from the comments table?

View Replies ! View Related

Multiple Count()s
I have a table with links, a log table to record clicks, and a vote table
to record votes on the links.

Is it possible to get the link data, the click count, vote count and vote
total in one query?

I can get the click count OK, but as soon as I add the vote count to the
query, it multiplies them; e.g. for a link with 14 clicks and 2 votes, it
tells me 28 for both figures. Here's the query:

select lnk.LinkID, URL, LinkTitle, Description, count(log.LinkID) Clicks,
count(Rated) Votes
from fn_links lnk
left join fn_log log using (LinkID)
left join fn_rate vot using (LinkID)
where CatID = $CatID
and Status = $Active
group by lnk.LinkID
order by Created

Then of course I want to include sum(Rating). I tried grouping on
different things, but it makes no difference.

View Replies ! View Related

Large Number JOINs And With Two FK To One Table
1) I have a first table with a PK (OwnerID) and a large number of FKs

2) I want to return the 'filename' column from the tables referenced by the FKs (and wonder if there is a better/faster/more concise way to do it than the JOIN syntax below?

3) In one instance there are 'bookcaseRight' and 'bookcaseLeft' fields in the first table which both reference the same 'bookcases.bookcaseID' -- and I cannot figure the way to do this (and/or/union all give improper results)

Simplified code below and Thanks in advance:
---------------------------------------------------

SELECT
doors.filename AS door,
bookcases.filename AS bookcaseL,
bookcases.filename AS bookCaseR,

FROM offices
JOIN doors ON offices.doorID = doors.doorID
JOIN bookcases ON offices.bookcaseR = bookcases.bookcaseID

{?AND?OR?Union?}
offices.bookcaseL = bookcases.bookcaseID

WHERE offices.ownerID = 1

;

View Replies ! View Related

MySQL Table Joins Compared With Other DB
I have only used MySQL as database. I have no experience with other databases like PostGre, MsSQL 2000, Oracle etc.

I feel that MySQL two table joins either natural join, left outer join or right outer join becomes VERY slow after the table size increases to over one million. I have tried optimization techniques and other stuff that I know and could search for BUT still it is slow in joins.

Is it something to do with the MySQL or it is with other database or it is with none, just something wrong with my queries (btw I am talking in general, not just single join query).

View Replies ! View Related

Count Multiple Values
I have a very basic question but I havent found answer to it anywhere.

I have a table that has following columns: date, answer1, answer2,
answer3, answer4
and entry: 2007-1-1, 'a', 'b', 'a', 'c'

What I want to do is count number of character "a":s as an answer.
Normally (if using COUNT, MATCH..AGAINST) mysql returns rows it finds.
I want to find out how many times this character "a" appears in a
record.

View Replies ! View Related

COUNT(*) Across Multiple Tables
I have a query to the effect of:

(SELECT COUNT(*) FROM tbl_a WHERE id="1234" ...) UNION ALL (SELECT COUNT(*) FROM tbl_b WHERE id="1234" ...)

This gives me two numbers--the counts of the relevant rows in each table that share the id. However, I would like to return the sum total of rows in *both* tables. Can this be done in one query with a SUM of some sort?

I'm using v4.0.14-standard.

I've done a quick search about the forum and couldn't quite find a solution to this.

View Replies ! View Related

Multiple COUNT() And GROUP BY
I think I'm on the right track, but I could use some help. I have a simple database with one table that contains observations of animal behavior. Columns include date, time, and animal_type.
I want an output that shows the number of times each animal type was observed, grouped by date. Something like this (sorry about the formatting)

date cat dog mouse
2004-1-1 1 2 3
2004-1-2 2 3 5
2004-1-3 1 2 3

I've gotten as far as getting one column, which would be this query:
"select date, count(date) as cat from observations group by date"
Can any one tell me how to get the rest?

View Replies ! View Related

Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null.

So here is my problem:

I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?

View Replies ! View Related

Count Multiple Comlumns?
i want to count several columns independently, so i tried:

SELECT count( a.id ), count( b.id )
FROM inbox AS a, forummessages AS b
WHERE a.id = '1' AND b.id = '1'

but that just produces:

count (a.id) - count(b.id)
58 - 58

true numbers are:
count (a.id) - count(b.id)
29 - 2

but i want to count the total number of messages in a where id = 1 and total number of messages in b where id = 1. Anyway to do this with just one sql statement?

View Replies ! View Related

How To Count() From Multiple Tables
I have 3 tables as follows:

Lesson:

lesson_id | lesson_name |
-------------------------|
1 | Lesson One |
2 | Lesson Two |
3 | Lesson Three |
4 | Lesson Four |
--------------------------

student_lesson:

lesson_id | student_id |
----------------------|
1 | 44 |
1 | 45 |
1 | 46 |
1 | 47 |
----------------------|

class_lesson:

lesson_id | class_id |
-------------------|
1 | 901 |
2 | 902 |
3 | 903 |
4 | 904 |
-------------------|

The following query:

SELECT crs_lesson.lesson_id, lesson_name,
count(crs_student_lesson.student_id) AS student_count,
count(crs_class_lesson.class_id) AS class_count
FROM crs_lesson
LEFT JOIN crs_student_lesson USING (lesson_id)
LEFT JOIN crs_class_lesson USING (lesson_id)
GROUP BY crs_lesson.lesson_id

Produces the following result:

lesson_id | lesson_name | student_count | class_count |
------------------------------------------------------|
1 | Lesson One | 4 | 4 |
2 | Lesson Two | 0 | 0 |
3 | Lesson Three | 0 | 0 |
4 | Lesson Four | 0 | 0 |
------------------------------------------------------|

This shows that the second count is always set to the same value as the first count even though the values should be different. Does anyone know how to obtain the correct values?

View Replies ! View Related

Using COUNT() On Multiple Tables
i've got 2 tables (client_names, users).

i want to select all from the client names and count users from the users table where the user matches the client. Which i'm doing. HOWEVER, what it's doing is no showing client names with 0 users. Is the GROUP BY hiding them?

here's what i have, but how do i add COUNT() to each row as a

SELECT client_names.name,
client_names.id,
COUNT(*) AS user_name
FROM client_names INNER JOIN users
ON client_names.id = users.client_id
GROUP BY name;

View Replies ! View Related

Order Of Table Joins Or Where Clauses Relevant
As we're on this topic in another thread right now:

Say I have a SELECT query from more than one table and with some =
conditions, does it matter in what order I enter the tables in the FROM =
clause and in what order the WHERE conditions appear in my query? Or =
does it make any difference if I use WHERE or HAVING? (I see that MS =
Access likes those HAVINGs...)

Of course my tables contain (maybe very much) more than some 100 records =
and are well-indexed, I believe.... but that's not my question for now.

I guess, the MySQL optimizer reads the table and column names in the =
specified order and tries to process them the same way, right? Or it =
joins the tables in my given order... And when are the resulting records =
reduced by matching against my conditions? Maybe someone can tell me a =
little bit about performance gains just by doing some 'manual query =
optimization'.

View Replies ! View Related

Full-text Searching And Table Joins
I googled this one but the only sites I could find were in German, and even then, judging by the emoticons, it didn't look like the posters were able to get any answers.

I'm trying to do a full-text search on two tables, and I'm getting errors.

SELECT * FROM artists, items WHERE MATCH (artist) AGAINST ('$term') OR MATCH (title) AGAINST ('$term') LIMIT 0 , 20

Works fine, but I need to get relevance.

SELECT * MATCH (artist, title) AGAINST ('$term') as relevance FROM artists, items WHERE MATCH (artist) AGAINST ('$term') OR MATCH (title) AGAINST ('$term') LIMIT 0 , 20

Returns "Wrong argument for MATCH". I've noodled around with the above statement for a while, and nothing seems to work. Does anyone have any experience with this and know how I can do it?

View Replies ! View Related

Combining Results Of 2 Table Joins With A Subquery?
I have two permissions tables. "user_permissions_single" contains company_ids, if a member is only subscribed to a select few companies.

user_permissions_single
===========================
user_id| company_id
------------------------
1| 10
1| 11
2| 12
Another table contains market_ids, if a member is subscribed to a certain market. (This prevents from writing multiple company_ids rows, when it can be handled with just one row.)

Code: ....

View Replies ! View Related

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

View Replies ! View Related

Can I COUNT Multiple Things In One Query?
SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '0' AND price <= '2499';
SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '2500' AND price <= '4999';
SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '5000' AND price <= '10000';

Consider the above queries, these are currently used to pull out the amount of vehicles failing into a particular price range and the output would look something like this

0 - 2499 (123)
2500 - 4999 (345)
5000 - 10000 (1234)

Now, it should be noted that there are more than 3 categories (often 10 or 15), and it is not just for price, we also do years (in decades at the moment, but may not always be). The ranges for the categories can and will change, and so the queries are built by PHP. The ranges in the above example would be supplied like this:
$range_arr['price'] = array('0','2500','5000','1000' .....
Anyway, this is just background

View Replies ! View Related