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

learn more… | top users | synonyms

1
vote
1answer
114 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
108 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
27 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], tat.[agent-...
0
votes
1answer
106 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
35 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
68 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 = ...
4
votes
3answers
174 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 ...
1
vote
0answers
1k 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 ...
1
vote
2answers
534 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
11
votes
1answer
195 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
6k views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = B....
2
votes
1answer
165 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
132 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
1k 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 &...
1
vote
0answers
198 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
779 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
35 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
3k 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) ...
2
votes
1answer
1k 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
217 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
27 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 `...
2
votes
2answers
744 views

Interpretation of multiple join order

I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table. If I use joins in ...
3
votes
2answers
3k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES('{"parent":null,"children":[2,3]}'); INSERT INTO test(data) VALUES('{"parent":1,"children":[4,5]}')...
2
votes
1answer
702 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
126 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
40 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.
3
votes
1answer
10k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
0
votes
1answer
379 views

Is it possible to simplify a query with multiple joins to the same table?

I have a rather large query which takes longer to run than I would like because of 17 LEFT JOINs on the same table. I've done some searching, but haven't found anything that is really similar to my ...
1
vote
1answer
2k views

Left outer join not returning all rows in a grouping query

I'm trying to query all users to find the price of orders in the last month. I'm doing this on PostgreSQL. Here's a toy database to show my problem: I have a people table and an orders table. I'm ...
3
votes
2answers
502 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
1
vote
0answers
370 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
127 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
50 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 ...
3
votes
2answers
484 views

Inner Join and Right Join same query

I'm using MySQL and InnoDB, I have a table reserver with 2 columns : idReservation and idChambre I have a table reservation and a table chambre Now I want my query to return every "chambre.id" who is ...
0
votes
2answers
258 views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
3
votes
1answer
360 views

Optimizing Join between a very small and a very large table

I have two tables: CREATE TABLE `TBL_REPORT` ( `ID_TBL_REPORT` bigint(20) NOT NULL AUTO_INCREMENT, `INDEX_NAME` varchar(100) NOT NULL, `INDEX_VALUE` varchar(100) NOT NULL, `...
1
vote
1answer
597 views

Query data from two tables and substitute foreign keys of first table with values of second [closed]

There is data table mysql> select * from data; +----+--------------------------------------+ | id | uid | +----+--------------------------------------+ | 1 | ...
0
votes
0answers
31 views

Subquery optimization

I was reading through this article - http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ I am working with an existing design. What I want is to be able to select ...
0
votes
1answer
162 views

Select max value from many to many tables

i have next tables in may data base: I want to select the book that has the max number of pages for each author. I did this : select auteur.nomauteur, auteur.prenomauteur ,livre.Title, livre....
0
votes
2answers
302 views

Counting unique records across 2 columns and computing a score based on boolean values

I have a table that looks like this in Google Big Query: custID InteractionDate Purchased? Sales 1 20150312 F 0 1 20150312 T 200 1 20150314 ...
0
votes
1answer
52 views

Uniq join 3 tables with relationship table not uniq

I am using Postgres. I have 3 tables: table_1 id table_1_2 table_1_id, table_2_id table_2 id The problem is the relationship table table_1_2 is not uniq. I.e., table_1_2 will have the ...
4
votes
1answer
7k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
0
votes
1answer
900 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
3
votes
1answer
644 views

Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches ...
0
votes
0answers
133 views

MySQL - Group By - How does it determine which row to return from joined table

I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up. I have 3 tables: foo bar bar_image And paring back all distractors, ...
1
vote
0answers
106 views

Poor performance filtering mysql spatial join

First of all, sorry for the crossposting. I ask this question in stackoverflow and they sent me here I have a mysql table with spatial references (GEOGRAPHIC_FILTER), like cities, countries, natural ...
-1
votes
2answers
477 views

Join 2 columns in one table based on equal values in a third column

I am trying to separate data within one column based on identical values in a second column. Our goal I to find out how many times each MD used a facility. Unfortunately, the table has separate ...
0
votes
2answers
41 views

Adding counts inside a COALESCE

So I have a continuation of this question. New requirements have thrown in a third table. So here's the breakdown of how the tables relate: TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, ...
1
vote
1answer
37 views

Need help rectifying IN/NOT IN clause

Hi there everyone! I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, racing,...