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

learn more… | top users | synonyms

0
votes
2answers
57 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
2answers
362 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 ...
3
votes
1answer
418 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 ...
-1
votes
2answers
2k 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
231 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
1k 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
44 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
170 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
66 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
287 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
799 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 ( ...
1
vote
1answer
2k 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 ...
7
votes
3answers
2k 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 ...
0
votes
1answer
492 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
145 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 ...
2
votes
1answer
852 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 ...
1
vote
1answer
305 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 ...
5
votes
2answers
132 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 inner join ( ...
4
votes
3answers
3k 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
170 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
39 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
48 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
192 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
357 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
3k 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` = ...
11
votes
3answers
3k 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
172 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
160 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
295 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 ...
3
votes
1answer
466 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
vote
1answer
895 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
44 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
627 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
35 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
4k 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
288 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?
0
votes
1answer
106 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 = ...
0
votes
1answer
81 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
3k 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
242 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
691 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
161 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
283 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 ...
1
vote
1answer
412 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
843 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
121 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
159 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
386 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 ...
1
vote
2answers
404 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
499 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, ...