An SQL join clause combines records from two or more tables or views.
0
votes
2answers
25 views
How to use GROUP BY after the Having clause Mysql
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
All the accounts which is not ...
0
votes
1answer
56 views
Simplify subqueries with join
I'm trying to create a report in SQL Server Reporting Services that shows the availability of multiple sites. The sites are monitored by a tool that uses a SQL Server 2012 database to store the data.
...
-1
votes
0answers
54 views
2 Outer Joins on Same Table? [closed]
Here is a question which has been boggling me for few days now, and I searched and searched but couldn't find any convincing answer !
Simple question, why is it restricted to have 2 Outer Joins in ...
0
votes
1answer
24 views
How to create the multiple sum query with joins
Please consider following database:
Table `invoices`:
-- invoice_id
Table `amounts`:
-- amount_id invoice_id type_id amount
I am trying to get sum for each type from table like this:
SELECT
...
1
vote
1answer
32 views
Joining data from legacy database
I'm trying to figure out how to write this query.
Table 1 - Booking
BOOKING_ID | BOOKING_DATE | etc...
1 | 2013/01/01 |
2 | 2013/01/01 |
Table 2 - BOOKING_PROCEDURE_TYPE
...
1
vote
2answers
44 views
Prune unused joins
I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case.
I have a bunch of tables with the following structure:
...
0
votes
3answers
44 views
find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle)
I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same ...
1
vote
3answers
161 views
Will performance be better if I specify a filter multiple times?
Do I gain more performance if I repeat the filter in the WHERE clause on both sides of an OUTER JOIN?
To clarify using code:
select *
from #main_select
left outer join
(
select
main_id
...
-2
votes
0answers
102 views
Cross-product generates duplicate rows, how to unique/distinct the result?
CREATE TABLE tbl_T0(fid int, haz float);
CREATE TABLE tbl_T1(fid int, foo text);
CREATE TABLE tbl_T2(fid int, bar money);
Populate each table with some data, then create views with ROW_NUMBER():
...
2
votes
1answer
51 views
How to optimize multiple self-JOINs?
I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.
One of the tables looks as follows (relevant cols only):
CREATE TABLE IF NOT EXISTS CountryData (
...
0
votes
1answer
17 views
HOW to “SUM” and “JOIN” for single query of a poll results?
I have a poll database as
CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ...
2
votes
1answer
69 views
How to avoid large joins when determining which rows a user is authorized for?
Typical wep app situation: you have logged in users querying for resources exposed through an API. As a hypothetical example, company administrators quering for their employees' time sheets:
company ...
6
votes
3answers
189 views
Splitting SQL query with many joins into smaller ones helps?
We need to do some reporting every night on our SQL Server 2008 R2. Calculating the reports takes several hours. In order to shorten the time we precalculate a table. This table is created based on ...
1
vote
1answer
48 views
Retrieving 10 records from multiple tables
I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 - all posts, Table 2 posts that user read.
There are some scenarios;
User has never ...
2
votes
0answers
86 views
How can row estimates be improved in order to reduce chances of spills to tempdb
I notice that when there are spill to tempdb events (causing slow queries) that often the row estimates are way off for a particular join. I've seen spill events occur with merge and hash joins and ...
1
vote
0answers
59 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
319 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
60 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
43 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
22 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
35 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
40 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
43 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
97 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
126 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
53 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
65 views
Complicated join with where clause
I have four tables:
sales
sales_thumbs
sales_images
sales_sizes
sales table:
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | ...
2
votes
1answer
95 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
75 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
49 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
54 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
141 views
Error “column does not exist” in a SELECT with JOIN and GROUP BY query
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
39 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
92 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
155 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
64 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
133 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
72 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
66 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
124 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
51 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
78 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
66 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
70 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
155 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
76 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`, ...
