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

learn more… | top users | synonyms

3
votes
1answer
47 views

COUNT number of column values with INNER JOIN [closed]

I'm new to PostgreSQL and I am making a Report. This is my SQL Fiddle: http://sqlfiddle.com/#!15/6ce465/5 So basically I have two tables, responses and questions. The structure is responses ...
2
votes
1answer
13 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
0
votes
2answers
33 views

PostgreSQL, merge join results

I have 2 tables with common columns and would like to know if it's possible to request this 2 tables by merging the results. Table "job" : parent_id, target_id, start_date, end_date, jobposition_id ...
1
vote
1answer
55 views

sql join query through Adventure works database [closed]

I want to show a list of Product Colors. For each Color then show how many SalesDetails there are and the Total SalesAmount (UnitPrice * OrderQty). Only show Colors with a Total SalesAmount more than ...
3
votes
0answers
46 views

type ALL in explain [closed]

I have tables like these: items +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
35 views

Oracle: Recursively self referential join with nth level record

I have self referential table like this: id |level | parent_id ---------------------- 1 |1 |null 2 |1 |null 3 |2 |1 4 |2 |1 5 |2 |2 6 |3 |5 7 |3 |3 ...
8
votes
1answer
94 views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
-1
votes
1answer
64 views

A complex query to Joining 2 tables and getting result [closed]

I have a database in MySQL. I have 2 tables, Structures as follows : t1 t2 I want to join both the tables and result should be like this : Employee_id | count(DNIS) | total_calls | count(DNIS) ...
1
vote
0answers
33 views

Efficient structure for querying membership in many-to-many relationship where tableA has ONLY the indicated members from tableB?

I'm working on an application with a PostgreSQL back end. There is a many-to-many relationship between items in one table and tags in another table. The specs require that the application be able to ...
1
vote
0answers
23 views

Dealing with joins in persistent key-value stores

One promising emerging category of NoSQL databases is key-value stores. These include Redis (disk-backed), Riak, Aerospike and others. The problem I struggle with the most is the lack of joins. ...
0
votes
2answers
56 views

Doing a left join and having every match include an extra null row

So I want to do a left join and have every row on the left table include a row of nulls on the right, even when there is a match. Is there a name for this type of join? And is there a better way to ...
1
vote
1answer
34 views

SQL Join Field and Content

I have this MySQL Structure: CREATE TABLE Fields ( ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ForUser VARCHAR(255) NOT NULL, ForCategory VARCHAR(100) NOT NULL, FieldName VARCHAR(100) NOT NULL ); ...
2
votes
1answer
47 views

Oracle's (+) notation within function

If we use (+) notation we can have outer join. For example: select * from tab_a, tab_b where tab_a.num = tab_b.num(+) Is outer join. If we use function around columns, for example: select * from ...
1
vote
3answers
176 views

How can I join two tables of an Oracle database containing all rows (not the intersection)

I have a Oracle database with a table t1 with: cod | text1 ----------- 1 | aaa 2 | bbb 3 | ccc table t2 with: cod | text2 ----------- 2 | abc 4 | mlc 5 | asc how can I optain ...
7
votes
1answer
94 views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
1
vote
1answer
66 views

MYSQL View with LEFT JOIN return all possible combinations

I am working on a view that joins multiple table. The data will be entered on separate table based on RepairID. The view will gather the data that is linking to the RepairID. My problem is after I ...
1
vote
0answers
18 views

Type of relational/object-oriented database with improved joins for object graphs?

I would like to know if there is a specific name or term for the kind of database described below: I notice one of the biggest problems when working with relational databases and object trees/graphs ...
2
votes
0answers
43 views

How do I make this Postgres view that performs a join updatable?

I've got two tables, products and subscriptions: CREATE TABLE products ( id bigint NOT NULL, title character varying(75), description text, manufacturer_id bigint, created_at ...
2
votes
3answers
335 views

SQL join with multiple columns as FK to same list table

I have a table that has 5 references to a list table for normalization of the first table. So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, ...
1
vote
1answer
8k views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
6
votes
2answers
140 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
1answer
23 views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
2
votes
1answer
105 views

How to select from 3 tables while using count on 1, and sum on another?

I'm stuck on this one query. Here is my setup: table 1 (shows) show_user show_count table 2 (views) view_user view_views table 3 (users) user user_name What I need to is produce results ...
3
votes
1answer
95 views

MySQL Join When Record in Joined Table Does Not Exist

How I can join two tables when record in joined table doesn't exist. I'm tried something like this: SELECT Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea, ...
1
vote
5answers
117 views

TSQL insert table using view — performance issue

I have a big view joining multiple tables that in a regular basis materialists itself into a table (this step occurs for viz's tool read a table instead of a view during data source refresh): select ...
0
votes
1answer
31 views

Lookup multiple ids in seperate table with 2 subquerys

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date. SELECT PRTIMESHEET.POSTED_TIME, PRTIMESHEET.PRRESOURCEID as "SUBMITTER", ...
2
votes
1answer
181 views

Joining on a field with different values

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually ...
0
votes
1answer
28 views

Problem using multiple JOINs

I have a list of customer IDs in table newsletter_subscriber. I have an EAV table that contains a lot of information about these customers, table customer_entity_varchar. For every entry in ...
0
votes
1answer
28 views

Postgres INNER JOIN same table failing on CartoDB

I have a table with data which looks like this +----+------+-------+ | cp | year | count | +----+------+-------+ | 1 | 2000 | 10000 | | 1 | 2001 | 9000 | | 1 | 2002 | 9500 | | 2 | 2000 | 8000 ...
0
votes
1answer
48 views

SELECT w/ INNER JOIN Query on Optional Columns

I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables. File2 is all about the ...
1
vote
1answer
39 views

SQL Server Query For Getting data in Specific Format

We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details. I am looking for a SQL ...
1
vote
0answers
32 views

Delete using join or using sub-query?

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where `id` in (select `deletable_id` from ...
1
vote
1answer
271 views

Joining multiple tables results in duplicate rows

I am running SQL 2005. I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. Here is my query: There are multiple tables that have ...
1
vote
1answer
47 views

Combining data from 3 tables depending values stored in 1 table

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single ...
1
vote
2answers
61 views

Aggregating results of two database views in one database view

I have a view with name of UserInfo like this: And another view with name of UserRoleInfo like this: I want to aggregate all the records of the two views for one user name like H.F into ...
2
votes
1answer
54 views

Filtering by minimum value per group problem

I have a select statement, which when simplified produces the following: +------------+----------+-----+ | Group | Name | ID | | vegetables | potatoes | 1 | | vegetables | carrots | 6 ...
0
votes
1answer
75 views

Cross Join and Left Join within a query

I have query which has a cross join and also a left join which produces an error Unknown column 'a.hour' in 'on clause' simplified version of query is like this SELECT b.*,a.hour,c.datetime FROM ...
8
votes
3answers
7k views

Is it better to separate a big query into multiple smaller queries?

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
1
vote
0answers
255 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
1
vote
0answers
180 views

Multi-Table Join to Single Destination [closed]

I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL. To ...
0
votes
0answers
29 views

Update a field in table when a combination of three fields with a second table is matched

I have a database form an old archaeological project which I imported into a mysql DB. I have a table "graves" with 400+ entries: "graves" id necr burials single multi 1 1 1 ...
0
votes
2answers
60 views

Select MAX DATE from joined Table

I have two tables, PROJECT and BILLINGS, as follows: Project_ID Project_Client ---------- ------------------ P1 ABC Company P2 DEF Company P3 GHI Company Proj_ID ...
1
vote
1answer
83 views

Optimizing a query that performs worse on a smaller dataset

I am trying to run this query. The * is just for simplification, I'm selecting ~10 specific fields for some calculations. SELECT * FROM `orders` LEFT JOIN `orders_processed` ON ...
0
votes
0answers
33 views

Estimate the number of Tuples from relations

Quick question, and great help if possible: I have an exam tomorrow on a database module which I'm revising for, and one of the past papers had this question: Consider the following three relations: ...
0
votes
1answer
85 views

How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?

I have the following SELECT statement: SELECT * FROM "Users" u INNER JOIN "Calendars" c ON (u."UserId" = c."UserId") INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId") WHERE ...
1
vote
1answer
60 views

MySQL select with join table

I have 3 tables, one of them is join table: user table: CREATE TABLE `user` ( `id`, `name`) media table: CREATE TABLE `media` ( `id`, `value`, `type` enum('M', 'T')) user_media table: CREATE ...
0
votes
1answer
56 views

Select range of non-identical date from different table

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between ...
0
votes
1answer
22 views

How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different?

I have this stored procedure which generates the following output. The query which generates the output from the stored procedure is as follows: SELECT tat.[agent-id], ...
0
votes
1answer
50 views

What is the most efficient way to exclude records with a particular ID in a MySQL query for random records?

I have an api endpoint that returns X amount of random posts from a table called "posts". This endpoint is used by a mobile app. It retreives random posts by doing a SELECT COUNT(*) on the posts table ...
1
vote
2answers
31 views

How to Create key (to be used in join) using concatenation?

I want to inner join tables in SQL Server 2008, however both tables have been set without Primary Keys. How to create a key using concatenation of existing columns? I tried the below but it only ...