An SQL join clause combines records from two or more tables or views.
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
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
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` = ...
10
votes
3answers
1k views
Column name naming conventions and best practices
I would like some expert opinion on best practices when it comes to column naming.
The background is that according to Wikipedia, the following syntax,
SELECT ... FROM Employees JOIN Timesheets ...
0
votes
0answers
25 views
Missing values in INNER JOIN MS-Access?
When I run the following query in MS-Access:
SELECT a1.ConStateNumber, a2.ConStateNumber
FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 INNER JOIN
qryW2_q3_sum a2 ON a1.ConStateNumber = ...
-2
votes
3answers
68 views
Left Join not giving desired result
I have two Datasets
FEES PAYMENTS
CrsCode InstNo FEE Regno CRSCODE Instno Payment
CA1 -2 100 R1 CA1 -2 100
CA1 -1 200 ...
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
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
66 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 ...
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 ...
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 ...
-1
votes
1answer
76 views
Want to show 3 tables in the same gridview and want to update/delete in parallel [closed]
Suppose I have 3 tables. But I want to show them it in same gridview and want to update and delete operations in parallel. How can I do that?
table 1: workshop
id contents demonstration ...
1
vote
1answer
90 views
Immediate refresh materialized views (MQT) with OUTER JOIN under DB2
DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE).
I cannot find a simple solution to get round this problem. Perhaps with ...
0
votes
1answer
90 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY querry
I'm using PostgreSQL 9.1 with a Ruby on Rails application.
I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
0
votes
0answers
43 views
one vs two column index difference when doing JOIN query?
Let`s suppose that alfa,beta and gamma contains milions of rows so we need to create indexes obviously to get optimal performace for this query :
SELECT * FROM alfa
JOIN beta on beta.id = ...
2
votes
0answers
46 views
update statement with self join
I needed to copy information from one row of a table to another.
I noticed I could do the following:
update address
set col1 = a2.col1,
col2 = a2.col2,
.....etc
from address a1, address a2
where ...
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 ...
0
votes
1answer
32 views
Getting the name of the column that was used in a join?
This may be a bit of a weird question:
Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get ...
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 ...
1
vote
0answers
29 views
parenthetic grouping of join clauses
What is the difference between these joins?
a left join b left join c
(a left join b) left join c
a left join (b left join c)
Does the grouping only affect the order of the joins? Will the query ...
0
votes
2answers
86 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`
+--------------+ ...
-1
votes
2answers
60 views
strategies to get data from diff database [closed]
Problem
I have two databases user_works database and pricing database.
I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
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 ...
1
vote
1answer
227 views
Visually create a join using PHPMyAdmin
I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join. Is this possible on PHPMyAdmin?
-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`, ...
0
votes
1answer
70 views
Why is there such a huge performance difference in these two join statements?
I have a query which i first wrote as (query has been simplified a bit) :
select ROW_NUMBER() OVER (ORDER BY c.CategoryTypeID), c.name, count(p.id) from category c
inner join product p on p.indID = ...
2
votes
2answers
56 views
How to JOIN two table to get missing rows in the second table
In a simple voting system as
CREATE TABLE elections (
election_id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255),
CREATE TABLE votes (
election_id int(11),
user_id int(11),
FOREIGN KEYs
for ...
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,
...
3
votes
1answer
87 views
Oracle: Non key-preserved table should be
I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
0
votes
2answers
43 views
Expand sparse table with self outer join on distinct values
I have this:
g1 | g2 | x
---------+-----------+-----
New York | Monday | 210
New York | Tuesday | 258
Chicago | Monday | 30
Chicago | Wednesday | 25
LA | Thursday | 40
...
2
votes
2answers
53 views
Postgres RIGHT JOIN with custom array
I'm using Postgres 9.1 and want to get a result with some blanks where there is no data. My query looks like the following:
SELECT institution_id FROM ... WHERE institution_id IN (1, 3, 4, 5, 7, 9)
...
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 | ...
1
vote
1answer
121 views
join the two tables which possess one to many relationship
In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' &
Table2:'Ex'
description of ...
0
votes
1answer
91 views
oracle huge hash_area_size
I am wondering what is the maximum setting for hash_area_size parameter?
I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour. 10Gb is not that much ...
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 ...
5
votes
6answers
6k views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
1
vote
1answer
72 views
Join Calender Table
I have a Datatable and a Calendartable.
I do a join on this tables to get sequential dates if there is no data for this day.
My problem now is that there a several id's and i need a count for each ...
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
119 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
96 views
How and what is the most efficient way to join two tables, retaining a particular field from both?
I have two tables, with the following columns:
Table 1
ID
Component
Data1
Data2
Table 2
ID
Component
Data3
Data4
If I have rows in these tables, with items Component1, Component2, Component3, ...
1
vote
1answer
84 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 ...
2
votes
3answers
173 views
Is it better to seperate a big query into multiple smaller queries?
There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using ...
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; ...
3
votes
2answers
198 views
Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.
We need ...
5
votes
2answers
248 views
How to use merge hints to isolate complex queries in SQL Server
I can understand that if I join individual queries that are "individually fast" the combination may become slow because the default execution plan may be non-optimal. However when I know the number ...
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 |
...
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
73 views
Is there a standard way to replace codes with values from a lookup table for reporting or analytics?
Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
