An SQL join clause combines records from two or more tables or views.

learn more… | top users | synonyms

4
votes
4answers
830 views

Table join to match without duplicates

I have a situation where I need to match arrivals and departures of assets in a location. The problem is that asset movements are not always entered into the database chronologically, nor is there ...
-3
votes
1answer
138 views

SQL Server: Month Range Join [closed]

Given that there are two dates like 2012-07-08 and 2013-02-06. I need to count record of each user for every month. If there is no data for a user in particular month then it should return zero. The ...
0
votes
2answers
76 views

Grouping results based on link table

I have three tables: users +----+-------+ | id | name | +----+-------+ | 1 | dave | | 2 | cher | | 3 | meg | | 4 | sarah | +----+-------+ groups +----+-------+ | id | name | ...
4
votes
1answer
645 views

Forcing Join Order

I have a table with just over a million rows. These rows can have a parent record in the same table which is found by joining to itself on 6 different columns (i.e. there is no single ParentID ...
4
votes
2answers
567 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
-1
votes
2answers
4k 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
391 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. ...
0
votes
1answer
2k 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 the sum for each type from table like this: SELECT ...
1
vote
1answer
45 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
217 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
68 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
422 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
1answer
1k 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
840 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
184 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 ...
7
votes
3answers
3k 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
394 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 ...
6
votes
2answers
1k 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
95 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 ...
8
votes
3answers
7k 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
435 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
vote
1answer
48 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
50 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 ...
1
vote
2answers
5k 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
222 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
182 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
527 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
436 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 ...
1
vote
2answers
264 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 ...
2
votes
1answer
1k 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
602 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
114 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
2k 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 ...
1
vote
1answer
4k 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
1answer
45 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
38 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
798 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
7k 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
vote
1answer
1k 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
108 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 = ...
3
votes
2answers
12k 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
6k 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 ...
1
vote
2answers
361 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
1k 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
91 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
237 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
331 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
1k 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 ...
1
vote
1answer
549 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
135 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 ...