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

learn more… | top users | synonyms

0
votes
1answer
12 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 ...
0
votes
0answers
10 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 ...
3
votes
1answer
71 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, ...
0
votes
1answer
29 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", ...
1
vote
5answers
73 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
26 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 ...
4
votes
2answers
90 views
+50

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
28 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 ...
0
votes
1answer
21 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 ...
1
vote
1answer
29 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
24 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 ...
2
votes
1answer
42 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 ...
1
vote
1answer
43 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 ...
0
votes
1answer
69 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 ...
2
votes
1answer
80 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 ...
1
vote
1answer
78 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
0answers
155 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 ...
0
votes
0answers
22 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
51 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
72 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
25 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
35 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
42 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
19 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
36 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
30 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 ...
0
votes
2answers
30 views

Table JOIN and CREATE new table extremely slow

I'm using something like this to create a new table: CREATE TABLE result AS (SELECT calls.*, targ_with_planned_calls.* FROM calls INNER JOIN planned ON calls.first_id = ...
1
vote
0answers
40 views

How to display all the dates between multiple two dates in a table?

I want to display all the dates between two dates for a particular record And this is the table : ID Start_Date End_Date 1 2013-01-14 2013-01-18 2 2013-02-01 2013-02-04 and now i want to get all ...
4
votes
3answers
103 views

MySQL JOIN query produces wrong result

I have two tables complaints and complaints_reply in my MySQl database. Users can add complaints which are stored in complaints the complaints reply are stored in complaints_reply table. I am trying ...
10
votes
1answer
148 views

How to optimize a query so that it seeks on one index first, and then another index after that

I have two sets of earth measurements from satellite data, each with time fields (mjd for mean julian date) and geography positions (GeoPoint, spacial) and I'm looking for coincidences between the two ...
2
votes
1answer
48 views

Join more than 3 tables in one single query, with many comparisons and no IDs

I use an overlycomplex and poorly designed software at my work, and now I need to produce a report of invoices issued between a period of time. Invoices are held at table ENTR2012, which uses the ...
0
votes
1answer
41 views

INTERSECT query rewrite as JOIN?

I have two queries written in PHP against a Microsoft SQL Server database (I know this is weird). I'm querying one table. I want the records shared by both results sets ONLY. Here is query #1: ...
1
vote
1answer
172 views

Query with group by having count 0

I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count ...
4
votes
6answers
1k views

How to find the nth highest salary in SQL?

I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)? EmpID Salary 1 90000 2 80000 3 54000 4 ...
1
vote
0answers
32 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
-2
votes
1answer
44 views

Retrieve multiple rows from table2 based on single row from table1 in SQL Server [duplicate]

I have two tables and I am using SQL Server. table1 structure is Campiagn_Id Campiagn_Name 1 test1 2 test2 3 test3 and table2 structure is Campign_Id EmalList ...
0
votes
1answer
27 views

How to join tables without identifiers?

For school I have to do this: Query what salary scale an employee belongs to displaying these collumns: s_schaal.schaal, werknemer.name, werknemer.salaris. But there are no identifiers between the ...
2
votes
2answers
251 views

Join two tables and return data and count in a single query

I have two tables in an SQL Server database, one with two columns and one with four: tbl_email_list email_list_id int (PK) email_list_name varchar tbl_email-details email_uniq_id int (PK) ...
0
votes
1answer
76 views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
0
votes
2answers
58 views

inner join select - group by?

I have 2 tables (Oracle database): ANEXA2A_TABEL1: id fk_id_anexa pet1 pet2 alt1 alt2 1 123 10 1 20 2 2 321 11 2 10 3 ...
1
vote
1answer
24 views

making a where clause optional in a sql statement

Here is my current query: `SELECT `performers`.`hash`, `performers`.`alias`, `performers`.`date_updated`, `performers`.`status`, IF(`performers`.`status` = 'active', 'deleted','active') AS ...
0
votes
0answers
12 views

How to calculate sum of a column in a table accessed with multiple joins and where condition

I am unable to find correct sum of vaccinated based on campaign id. no matter which campaign I choose to get sum of vaccinated on the basis of that campaign id it always returns the same data. Please ...
2
votes
1answer
81 views

Oracle 11g hash join vs nested loops question

I'm using Oracle 11g and working on a legacy system. I have two tables, A and B. A has ~40M rows and B ~4M rows. A has a foreign key on B (B is the parent of A). A query does essentially this: ...
0
votes
0answers
28 views

How to display additional column in select with true/false value [SOLVED]

Solved. Look in comment. I am using PostgreSQL 9.1. I have 3 tables: simcards: id serial NOT NULL, iccid character varying(30) NOT NULL, customer_id integer customer: id serial NOT NULL, name ...
0
votes
1answer
34 views

Meaning of JOINs in sql

I was trying to interpret the relationship between JOINs like LEFT-OUTER JOIN, RIGHT-OUTER JOIN, etc with relationships like Many-To-Many, One-To-Many etc in Hibernate or database language.
0
votes
0answers
26 views

Performance hits with MySQL Left Joins and Order By

There are a lot of questions out there regarding this issue, but most answers seem to be very case-specific. I've tried to make this general so it'll help others too. The Issue We have a smallish ...
1
vote
1answer
24 views

Table for actual values

I have a database where logging happens and is processed in a data-warehouse styled environment. This means there are staging cycles where the data comes in and then is processed. For a reporting ...
1
vote
1answer
61 views

How to join SQL tables where value is Between min and max value

I am currently looking to join two tables together based on an IP and IP range. Both the IP and Range are currently stored as BIGINT. For example we have a table MasterData with IPs in integer ...
2
votes
1answer
36 views

Converting a GraphDB schema to a relational database schema

I currently am using a graph database and am considering moving off of it to a relational database like Postgresql. My concern is that I have lots of relationships between entities (since this is ...
0
votes
0answers
13 views

creating best results from two tables

I have two tables, one is the results of an assessment from users(per row) and column starting with 819....X1 represents the qid. The other table holds the score for the answers (A1,A2,A3), both are ...