An SQL join clause combines records from two or more tables or views.
1
vote
0answers
33 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
3
votes
3answers
171 views
Are individual queries faster than joins?
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?
...
1
vote
2answers
49 views
Join on different types
In a database I have two tables:
The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying
The two tables are related: if it exists a ...
-1
votes
1answer
34 views
Performance issues with query of many joins on MySQL server [closed]
We have serious performance issues with a query joining a lookup table (stores key values pairs) around 20 times. The lookup table contains around 100.000 entries.
SELECT DISTINCT
...
1
vote
1answer
17 views
JOIN to read first table and get possible relationship in the second table
Consider a main table as
CREATE TABLE groups
(
group_id int(11) NOT NULL AUTO_INCREMENT,
group_title varchar(255),
PRIMARY KEY(group_id)
) ENGINE=InnoDB
and a second table for relationship as
...
2
votes
1answer
32 views
How can I include more query in one query?
These are my tables:
tbl_answers =>
aid
qid
answer
uid
dateposted
emailnotify
namedisplay
status
isbestanswer
tbl_questions =>
qid
question
...
0
votes
2answers
31 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` = ...
0
votes
0answers
27 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
86 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
31 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
107 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
44 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
59 views
Complicated join with where clause
I have four tables:
sales
sales_thumbs
sales_images
sales_sizes
sales table:
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | ...
2
votes
1answer
70 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
66 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
45 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
47 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
1answer
106 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
35 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
33 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
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 ...
1
vote
0answers
65 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
97 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
62 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
1answer
102 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
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
60 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 ...
3
votes
1answer
89 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
46 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
59 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)
...
0
votes
1answer
62 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
66 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
134 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 ...
-2
votes
1answer
72 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
127 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
98 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 ...
1
vote
1answer
73 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
121 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
66 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
75 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
votes
1answer
81 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
97 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, ...
3
votes
3answers
196 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 ...
1
vote
1answer
96 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 ...
3
votes
2answers
204 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 ...
1
vote
1answer
378 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 ...
1
vote
1answer
175 views
Retrieving data from inner join using LIMIT and OFFSET
These are my two tables:
table1
qid[PK] |gid[PK] |abcd | xyz | date
---------------+---------+---------+------+------------
00001 | qwe | 54 | a | 1994-11-29
00002 ...
