All Questions
56 questions
0
votes
1
answer
27
views
Get difference in values of second table from IDs in two columns of first table?
Given these two tables:
Table1
id1
id2
224
2341
5234
9394
6243
2342
⋮
⋮
Table2
id
value
224
2
2341
52
2342
24
5234
23
6243
242
9394
99
⋮
⋮
I would like to create this table:
Table3
id1
...
- 103
1
vote
1
answer
38
views
MySQL Tennis Ladder View Creation Question
I am creating a Tennis Ladder VIEW using MySQL and wish to do the following:
Properly create a view that includes: All player's names
These player names will come from the table: User - using the ...
- 21
0
votes
1
answer
480
views
MySQL query: Detect different rows between two tables and show differences in result table
I have two tables in a MySQL database.
Table1
a_id
1
3
4
5
6
Table2
b_id
1
2
3
I would like to spot the missing row from Table1 a_id 2, and missing rows from Table2 b_id 4,5,6, which should be shown ...
- 157
1
vote
1
answer
1k
views
How do I SELECT elements using WHERE with another table's record values in WHERE statement?
Problem summary
Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
- 13
0
votes
1
answer
31
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID ...
- 4,531
0
votes
2
answers
90
views
How to UPDATE faster with GROUP BY?
I have a typical article and tag_map tables,
CREATE TABLE `articles` (
`ID` int(11) unsigned NOT NULL,
`Tags` tinyint(3) unsigned DEFAULT NULL,
`Title` varchar(512) DEFAULT NULL,
PRIMARY KEY (`...
- 4,531
1
vote
1
answer
38
views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY ...
- 4,531
0
votes
0
answers
69
views
I'm having trouble generating a list of downloadable items (data from three tables) with a column showing most recent download time for each
I am using the following query to generate a comprehensive download log with data from multiple tables (reduced to three for this example):
SELECT wp_download_log.ID
,wp_download_log....
- 23
0
votes
2
answers
308
views
Inherit INNER JOIN fields in MySql SELECT QUERY
I want to know how I can join one field of an INNER JOIN table in a SELECT QUERY.
Here's what i got so far:
In this query my result would be NameA, NameB and idC, but i want to join tableA and tableC ...
- 15
1
vote
1
answer
30
views
Assistance with MySQL query
I have following tables
Tags
id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1
products
id | proudct_name
1|product1
2|product2
product_tags
id | product_id | tag_id
1|1||1
2|1|2
3|2|1
i ...
- 113
0
votes
1
answer
48
views
Why last table don't using index for joining?
By the help of others members i get an Query idea for my previous problem. But after writing this, the EXPLAIN showing that my last table do't using index for join but the joining ROW is PRIMARY KEY, ...
- 101
1
vote
1
answer
118
views
Why Output is wrong after doing the left join as subquery
I have TWO QUERY which work perfect, but when i join those two query as LEFT JOIN the output become wrong. So what is the problem.?
Query 1:
select Batsman.innings_no,
bowler,
sum(...
- 101
0
votes
0
answers
45
views
Comparing count of 2 tables, is this the correct way?
I'm building a survey system where
A client that has many users
A user can have multiple surveys.
I have a table users and a table survey_completions, I'm wondering if this is the correct (or best) ...
- 169
0
votes
1
answer
756
views
How to optimize UPDATE with a nested SELECT subquery?
I wrote a complicated UPDATE query, and it works, but it looks menacing. Here's what I'm trying to do:
In each topic user 'Bob123' posted anonymously. When you post anonymously in a topic, you get ...
0
votes
1
answer
45
views
Which query execution will be faster
In the following queries, which query execution will be faster. When I tried executing them in SQLWorkbench, each time query execution time is varying.
Also, can you guys suggest some better tools ...
0
votes
1
answer
7k
views
How to join 3 tables in MySQL
I’m looking at 3 tables here with:
1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign ...
- 11
-2
votes
1
answer
59
views
Subquery and subselection for Count/Number doesnt work
for example, for company (3) and his partners, I need the number of events with payouts. But i get only the number for one partner, im my example i have
i have 3 tables (t_company, t_partner, t_event,...
- 1
-1
votes
1
answer
4k
views
Replace Subquery with JOIN - MYSQL
Can you please suggest how i can replace subquery in fetching select columns to improve the performance of query :
SELECT sa.id AS patAppIdSA,
sa.rte_id AS patRTEId,
sa.case_type_id AS ...
- 109
1
vote
3
answers
288
views
MYSQL Update data based on top 10 records of each user
I have a table users and other table user_scores. In user_scores, I save daily score of every user based on a algorithm. Now I want to calculate AVG user_score by only considering latest 10 records of ...
- 391
1
vote
3
answers
12k
views
Joining two tables by the nearest match
General problem: I want to do a left join on two huge tables, but there is no matching key - I want to join the left table with the "nearest" row in the right table. In this example I want to join ...
- 211
1
vote
2
answers
156
views
Possible to rewrite this query as a join?
I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:
SELECT
events.*,
(SELECT MAX(updated_at)
FROM events e
WHERE e.user_id =...
- 113
2
votes
1
answer
13k
views
Filtering subquery results with group by
I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and ...
- 23
0
votes
1
answer
336
views
Select one row from n number table; Join / Union table with looping
I have a list table.
locations_list
id | location | table
----------------------
1 | Location 1 | location_1
2 | Location 2 | location_2
3 | Location 3 | location_3
location_1
...
- 113
0
votes
1
answer
5k
views
Joining subqueries with GROUP is slow
Can anyone help me with the following query?
SELECT
voc.username AS Medewerker,
voc.bestvastonbep_count AS Doorgezet,
IFNULL(cc.call_count, 0) AS Telefoongesprekken,
voc.bestvastonbep_count / ...
5
votes
2
answers
42k
views
MySQL LIMIT within JOIN
I have an 1:m association.
symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created.
I need to ...
- 191
1
vote
1
answer
157
views
Finding posts with exactly set of tags per user
I'm trying to determine the best way to find all posts that have an exact match with a list of user tags. For example, user has tag IDs: 1,5,9, and 11, while post has tag IDs 1, 5 and 9, thus this is ...
- 13
1
vote
0
answers
35
views
Getting Available Date/Times For Attendees And Avoid Conflict
+------------+ +---------------+ +--------------+
| attendees | | attendee_times| | meeting_times|
+------------+ +---------------+ +--------------+
| attendee_id| | at_id | | ...
- 646
0
votes
1
answer
6k
views
MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record
I am joining together three tables in order to display the names of recommended providers for a property.
First I join my properties (alias p) table to the properties_recommended_providers table
...
- 103
1
vote
1
answer
60
views
How to improve my mysql query execution performance
I have a main table named prog as below:
CREATE TABLE `prog` (
`prog_id` int(11) NOT NULL AUTO_INCREMENT,
`prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`prog_edit_date` varchar(...
0
votes
1
answer
54
views
Mysql query to get count result and group by
I've got three tables with different records for users,
User
username | realname | date
evn-az-3ju john 11/2012 03:09:40 p.m.
jwyvm_rdyt steve 12/2012 ...
- 101
2
votes
1
answer
169
views
JOIN with the last id
I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better ...
- 161
1
vote
1
answer
555
views
Multiple subqueries in columns with joins to return multiple rows
I have a table which stores id(primary key) of users and not their names (Names are stored in another master table) and also the the department id of user and not the name of the departments and there ...
- 13
1
vote
2
answers
687
views
how do I use a value from the select in a joined subquery?
I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
- 93
4
votes
3
answers
42k
views
Optimise MySQL SELECT with LEFT JOIN subquery
I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds.
SELECT
s.siblings,
l.id
FROM
`list` l
...
- 195
7
votes
1
answer
9k
views
Subquery can't find column from superquery's join
I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" ...
- 294
2
votes
1
answer
1k
views
Join query returning 0 rows - impossible where
My query is performed from a php web request using a PDO prepared statement.
My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order ...
- 304
6
votes
3
answers
8k
views
Return 2 columns from MySQL subquery?
I'm new in MySQL programing and I have one problem.
I created this query:
SELECT *,
topics.createdate AS TopicCreateDate,
(SELECT (SELECT username
FROM users
...
- 219
0
votes
1
answer
2k
views
MYSQL join on subquery optimization
This is a distilled version of the query that I am trying to run:
SELECT * FROM table1 t1
INNER JOIN (SELECT * FROM table2 WHERE foo = 'bar') t2
ON t1.id = t2.id;
The above query is incredibly slow ...
- 101
4
votes
2
answers
1k
views
Join subquery by year and month
I want to collect data from differed tables grouped by year and month.
I'm using subquery for each sub data.
Using mysql.
SELECT YEAR(td.db_date) AS stat_year, job.total_jobs
FROM ...
- 143
2
votes
2
answers
36k
views
Multiple COUNT fields, GROUP BY
I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?
I'm aware of WITH ...
1
vote
2
answers
1k
views
Select highest id of each type in mysql
I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ids....
- 11
1
vote
0
answers
626
views
Update row value with a sub-select join
I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table.
The query looks like this (...
- 123
1
vote
1
answer
94
views
Why conditioning before and after join, gets different results?
I want to left join two tables and then return some of the records.
My first query was:
SELECT *
FROM card
LEFT OUTER JOIN user_card ON card.id = user_card.card_id
WHERE card.package_id =14
AND ( ...
- 13
0
votes
0
answers
62
views
Subquery optimization
I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
I am working with an existing design.
What I want is to be able to select ...
- 101
3
votes
1
answer
70k
views
MYSQL query to find records between two date range
I am working in a web application using PHP and MySQL.
I have two tables
Employee
employee_id email_id
3 [email protected]
4 [email protected]
5 agent2@...
- 277
1
vote
2
answers
1k
views
Subquery or multiple left joins, or both?
I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
- 13
0
votes
2
answers
362
views
How are derived table implemented in MySQL
When MySQL makes a derived table as a result of
SELECT (SELECT ...) etc
or SELECT * FROM a JOIN (SELECT * from B) etc
or SELECT * FROM (SELECT ...)
Are these derived temporary tables created in-...
- 1,013
2
votes
2
answers
3k
views
"All joins can be rewritten to sub queries", is this statement true?
I wonder if there any case that a join can not be converted to a sub-query.
BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
- 197
3
votes
1
answer
28k
views
UPDATE a column by COUNT of another table
It might be a naive question, but what is the difference of these two queries and which is preferred?
UPDATE table1,
(SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B
SET table1.Freq = B....
- 4,531
2
votes
1
answer
5k
views
Using OFFSET in a large table by a simple sub-query
The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM ...
- 4,531