Tagged Questions
0
votes
2answers
25 views
Query WHERE clause and JOIN
I'm having an issue using a WHERE clause and JOIN.
SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = ...
1
vote
1answer
29 views
How to add additional tables/fields into a query with multiple inner joins?
Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result?
SELECT ...
0
votes
1answer
102 views
How to get rows where count() is null?
This is my SQL request :
SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
m2_.libelleMention AS ...
0
votes
2answers
43 views
Slow query joining on subqueries with max
I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen.
Here are the create table statements. I'm very aware of how badly ...
0
votes
1answer
58 views
Complicated join with where clause
I have four tables:
sales
sales_thumbs
sales_images
sales_sizes
sales table:
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | ...
2
votes
1answer
65 views
Best practice: Unions or a derived table?
I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:
CREATE TABLE `pending` (
...
`invoice` int(11) DEFAULT NULL,
`lid` int(11) DEFAULT ...
3
votes
1answer
63 views
Get data from two tables with recursive relationships
I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a ...
0
votes
2answers
58 views
Database search with multi joins
I have a MySQL database and I want to perform a little bigger search.
I have about 10k records in one of the tables and It's expected to grow, but slowly.
The biggest problem is that to perform the ...
0
votes
0answers
33 views
How Can I do and INNER JOIN USING (2 columns)?
Suppose we have:
CREATE TABLE cscart_products_prices_temp LIKE cscart_products_prices;
ALTER TABLE `cscart_products_prices_temp`
DROP `percentage_discount`,
DROP `lower_limit`,
LOAD DATA LOCAL ...
1
vote
0answers
60 views
How can I join one to one to (many to one) without using group_concat
I have a table structure as follows:
--
-- Table structure for table `emails`
--
CREATE TABLE IF NOT EXISTS `emails` (
`ID` int(5) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE ...
0
votes
2answers
84 views
MySQL: Join one Row in a table with two Rows in other table
In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations):
`users` `user_login`
+--------------+ ...
0
votes
1answer
61 views
Speed efficient query for membership first joined, latest category from membership table (min, max)
I have the following table representing membership information:
CREATE TABLE IF NOT EXISTS `membership` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`organisation_id` int(11) NOT NULL,
...
4
votes
1answer
65 views
How to write this self join based on three columns
Hello there I have a following table
------------------------------------------
| id | language | parentid | no_daughter |
------------------------------------------
| 1 | 1 | 0 | ...
-2
votes
1answer
69 views
Three INNER JOINs from a performance POV [closed]
I'm creating a PHP app where in file I had to create a SQL query like this :
SELECT
ta.`ID`, ta.`UserID`, ta.`Timestamp`, tb.`Username`, tb.`Email`, tb.`Mobile`, tb.`City`, tb.`Firstname`, ...
2
votes
1answer
114 views
Selecting minimum value using a subquery
I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.
We have a table for prices ...
0
votes
1answer
43 views
Mysql Join with filters
I have two tables: MAINCOLOR and SUBCOLOR with the data as:
MAINCOLOR :
MAINID COLOR
1 RED
2 BLUE
3 GREEN
SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1 1 PINK 0
2 1 ...
1
vote
3answers
118 views
Complex query with multiple normalized fields
We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
0
votes
0answers
64 views
How can I get multiple rows instead of columns after self-joins?
This is my situation (the real table isn't about people but about paths):
[Table: People]
id | name | type | related_to
----------------------------
1 | | | 0
2 | Nico | Friend | ...
1
vote
2answers
70 views
How to get non repeat results
I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables:
CREATE TABLE `default_tb_envio` (
`codigo` varchar(7) NOT NULL,
`fecha` date NOT NULL,
`id_modalidad` ...
1
vote
1answer
82 views
How does mysql deal with queries that touches myisam and innodb tables?
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
If a query involves both myisam and innodb tables, will the query use table lock or row ...
1
vote
1answer
368 views
Select multiple columns in subquery
I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement.
http://sqlfiddle.com/#!2/2481e/1
The link is to the schema with my current ...
3
votes
1answer
335 views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
0
votes
1answer
153 views
Group By on column from joined table
its about the following query.
EXPLAIN SELECT phrase, infinitiv
FROM `dict_verbs_all` `t`
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN ...
0
votes
2answers
193 views
Slow query with straight_join for small result
I have a performance-problem with a query in my application and I don’t understand the behavior of mysql.
The query consists of different joins, especially the join to the mentioncache-table. If the ...
2
votes
1answer
77 views
JOIN 2 tables, and query data from them 3 times
Basically I have 2 tables: post_meta and posts.
post_meta has a post_id, key and value:
post_id key value
6343 make Chevy
6343 model Volt
6794 make Chevy
6794 model ...
3
votes
1answer
152 views
Complex SQL Query
I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing:
host_snmp_cache:
...
0
votes
0answers
111 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
2
votes
1answer
118 views
Update ranking on table
I have two tables:
mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
1answer
242 views
inner join on PK with extra criteria slow despite indices?
Given the two tables below I am struggling to understand:
why is the third query slow even though first two queries are fast
what exactly is EXPLAIN saying
can I do anything to significantly speed ...
3
votes
2answers
219 views
SQL ANSI JOIN precedence
I have a query that looks like this:
SELECT *
FROM TBLA A
LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
JOIN TBLD D ON C.Col5 = D.Col6
In which order ...
1
vote
0answers
321 views
MySQL join for multiple child records in a single joined row
I have one master table (teacher) structured like
teacherId Name Class
1 Praveen 10
2 John 9
and having a child table (student) structured like
studentId ...
5
votes
2answers
90 views
mysql join by 2 ways - how it works? - which gives performance difference
I had 2 different ways to query the content which showed performance difference when executed..
The 1st way is
EXPLAIN select SOME_COLUMNS
from
( select *
from A
where CONDITION
) p
...
1
vote
2answers
256 views
Count consecutive null rows from a joined table
I have the following tables:
members:
----------------------------------------------
| member_id | member_name | member_join_date |
----------------------------------------------
events:
...
2
votes
1answer
292 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
1
vote
1answer
381 views
Optimizing multi-table left joins
I have four table which I am trying to join together.
Table event
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
3
votes
1answer
1k views
how to fetch different data on compare rows of two tables
I have two tables in my database, one is user_app table in which user info are stored, table are as:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
1
vote
1answer
243 views
Mysql Join to get multiple row value from
I am new to my Mysql and need help regarding join
I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I ...
2
votes
2answers
104 views
Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]
I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
1answer
131 views
MySQL: Very slow query, not using index, no joins involved
I am running what I think is a very simple query:
SELECT SQL_NO_CACHE m.mbr_nbr
FROM tran.member m
WHERE m.cardnbr = 000099999930
The table member is a poorly written one, with 133 columns and ...
0
votes
1answer
209 views
Slow performance of MySQL Join Query
I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the ...
2
votes
1answer
293 views
Help with a complicated MySQL Query inserting data using select from 2 tables
I am stuck with a case and unable to get the desired result from my query..
I have to insert into a table some records based on multiple records from 2 different tables..
Tables: Target: ...
2
votes
1answer
359 views
MySQL 5.5.8 InnoDB Foreign Key, JOIN performance
We are using aws/rds, MySQL 5.5.8. all InnoDB.
For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
2
votes
1answer
110 views
Rewriting a query and eliminating subqueries
I have the following query :
SELECT ws_id, n50, num_singletons, num_segments_less_100, kmer, num_all_reads, number_bases_in_contigs,
num_bases_in_singletons
FROM stats_assembly WHERE ws_id IN (
...
3
votes
1answer
308 views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables ...
1
vote
4answers
334 views
cross self join to get more rows - better solution?
I have a table employees:
+----------------+------------------+
| Field | Type |
+----------------+------------------+
| id | int(10) unsigned |
| persons_id | ...
1
vote
1answer
162 views
Finding Duplicates in LEFT JOIN
Here is my table structure:
create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', ...
0
votes
1answer
998 views
How can I select the latest record having one state where no later records exist with any other state?
I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget".
widget state timestamp
------ ----- ...
0
votes
2answers
64 views
I couldn't compile this little query (needs 2 tables)(mysql) [closed]
i have 2 tables as like seen :
user table : id, userid , country
friends table : id, userid , friendid
so i want to get friendid rows from friends table where friends.userid == user.userid and ...
1
vote
1answer
61 views
Joins in MySQL database
I am a newbie to the MySQL database.I want to know different types of joins in MySQL and any link or any other kind of documentation with examples which will clear the doubts will highly appreciable.
3
votes
1answer
2k views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
