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

learn more… | top users | synonyms

1
vote
1answer
48 views

MySQL FULL JOIN on same table

I have a table like this: And I want to get something like: fecha siguiendo seguidores 2016-01-02 430 1430 2016-01-03 450 NULL 2016-01-04 NULL 5000 ...
1
vote
1answer
58 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
1answer
37 views

Natural join question, I have the answer, but I don't know why it's correct. Help?

So I have a relatively simple database schema. I'm not sure why the where clause in the answer magically makes the dept_name duplication problem go away. I'm new to all this and I'm sure the answer is ...
0
votes
1answer
76 views

How do I join information schema to the table's data?

I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible? I actually found another way to work around it. I am using Innodb. For the structure it's kind of ...
3
votes
1answer
50 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...
0
votes
2answers
65 views

Perform join on multiple tables

I have three tables right now in a postgres DB: Artists ID Name Shows ID Date Venue Price Is_sold Ages Time Multi_day Pit Artists_shows ID Show_id ...
3
votes
2answers
44 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
1
vote
3answers
76 views

Union Query format in mysql

I am training in MySQL. I have database called ships. My goal is => For each class, define the number of ships of this class that were sunk in battles. Result set: class, number of sunked ships. Here ...
4
votes
1answer
70 views

NOT IN (together with join) query

I have two tables : Part, OrderLine: Part (PartNum PK, PartDesc, Price) OrderLine (OrderNum PK, PartNum PK, QtyOrdered) And I want to find out the parts that were not ordered. I think I have a ...
2
votes
2answers
155 views

Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3

I am trying to filter a table which has NON-UNIQUE transaction id's, joined to a products table on 1 common column. The filter needs to be as follows: equal to :group1 AND NOT equal to :group2 AND ...
2
votes
1answer
100 views

Inner Joins between Subsets of the Same Table

All, I am trying to figure out the syntax for doing joins between subsets of the same table. I have: Employee ( EmpId PK , EmpFirst, EmpLast, EmpMid, DateHired, SSN, DateBirth, Gender, PhoneNum, ...
6
votes
1answer
61 views

Postgresql join removal

I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL. If a view is ...
3
votes
1answer
80 views

Select another row if NULL

In a MySQL database I have these tables: Table 1: News news_id int (PK) category int (FK) rating tinyint Table 2: News Detail news_detail_id int (PK) news_id int (FK) language_code char ...
0
votes
1answer
74 views

How to optimise multiple joins with OR?

I need to extract some statistical information form drupal database tables. The problem with drupal is that it splits it's data for entities across multiple tables and getting simple information from ...
-1
votes
2answers
105 views

Can we do a Join on SQL tables on M:N relationship?

hope this is not too dumb/simple. I have been practicing joins recently and I have never seen a join done between tables T1, T2 that are in an M:N (Many-Many) relationship. Instead, joins are made ...
2
votes
1answer
73 views

Join query returning 0 rows - impossible where

My query is performed from a php web request using a PDO prepared statement. My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand :brand by order ...
5
votes
1answer
220 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
0
votes
2answers
233 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
2answers
57 views

I have following two MySQL queries and i want to execute it inside one procedure

Following query returns 10 rows of recipe id's i wants to pass first query's output which is of 10 rows to the second query and display the output of both the queries inside a procedure. Query 1: ...
1
vote
1answer
58 views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
2
votes
2answers
98 views

Join on multiple columns with OR

I have a table of 1 million record to join another table with 100000 records. However, there are 5 potential keys (lets assume Account Number, email address, membership number, alternative email, and ...
0
votes
0answers
27 views

How to use COUNT() properly with a 3 table join

I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) ...
6
votes
5answers
568 views

SQL queries to only show most recent purchase records for individual food items

I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item. Here is a ...
0
votes
0answers
12 views

Schema of equijoin in relational algebra

Why does equijoin return a schema consisting of all attributes from both relations (including intersection) when equality between them have already been established? That is, we know that for any ...
0
votes
1answer
27 views

Cumulative data and dates - my joins aren't working

So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are ...
0
votes
1answer
147 views

Join query with closest date in “ON” clause

I need to make an OUTER JOIN on two tables, based on 2 columns, first id an INTEGER id, which is pretty straightforward to use in the query, but the second one is a DATETIME, which doesn't actually ...
2
votes
1answer
61 views

Join query with different column types in “on” part

In need to make a JOIN on 2 tables based on columns which have different column types. On table A, I have a DATETIME field and, on table B, I have a DATE and a TIME field, which combined would match ...
1
vote
3answers
74 views

Return 2 columns from MySQL subquery?

I'm new in MySQL programing and I have one problem. I created this query: SELECT *, topics.createdate AS TopicCreateDate, (SELECT (SELECT username FROM users ...
1
vote
1answer
31 views

Efficiently perform multiple similar updates

I'm trying to extend some existing SQL (SQL Server) that builds a table of all items to act on using a number of queries. Some of these are very similar and costly, and I was hoping to get some advice ...
2
votes
1answer
223 views

SELECT values with a condition and JOIN

I am not sure that is a "SELECT/JOIN" problem but I really don't know how to formulate the question. Perhaps you could suggest a better title I have a table (table-a) with a list of items, I Want to ...
0
votes
3answers
78 views

MySQL optimizer won't use my index

With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename....
0
votes
2answers
63 views

WHERE on SQL with JOIN on large tables

We have a set of large tables (millions of records) on a MySQL DB, having schema like so (simplified): T1: id, uid, rid, text1, text2, int1, int2, ... T2: id, rid, tag_id, created_at T3: id, owner_id,...
0
votes
0answers
78 views

Mysql FULLTEXT index not used in Many to Many table join search

I've got a set of data in the Books table, with BookID as the primary key. I've also got Subject and Location tables, each of which is joined to the primary Books table with a Many to Many table. ...
0
votes
1answer
62 views

SQL Query help: co-workers and employees from one query

I have two tables defining users and user employments. The user table looks like this: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) COLLATE utf8_unicode_ci ...
1
vote
1answer
262 views

How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

I have two tables: 1 with an Unique ID with the first time they visited (in date format) and a historical addresses table with the same Unique ID, a start field (date field), an end field (date ...
0
votes
0answers
24 views

Is it possible to stack similar columns from a subquery and join the other unique columns in one query?

I am fairly new to the sql world and have been struggling with a query. I have two subqueries that are generating tables with two columns. The first table is part # and model_countH. The second table ...
0
votes
0answers
31 views

Is it better to filter a small set of row with a BOOL or through a JOIN?

Let's say that I have a simple Books table: Books ----- id name description ... It's a pretty small table, at the moment with 60 records and it can grows, but slowly. On a webpage I want to show ...
1
vote
1answer
21 views

DB2 LUW: how to influence query planner's choice of join

Suppose I have two tables, A and B. They "share" the same primary key, which is indexed in both tables. Table B also has a timestamp column which is separately indexed. I do a subselect on B using ...
0
votes
1answer
79 views

Mysql join non-unique columns

I have two tables, want to join them by an non unique column. CREATE TABLE `order_item` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `soi` char(1) DEFAULT NULL, `product` char(4) DEFAULT ...
0
votes
1answer
52 views

How to make an union on 2 joined tables without affecting the sum on the column? [closed]

I have the following tables: cant: id CANT_VAL COD_VAL fk_id_chest c1 c2 18059 18.56 R12 1 100 11 18060 11 R10 2 200 22 18061 15 ...
0
votes
1answer
25 views

How can I group two joined tables

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL fk_id_chest 18059 18.56 R12 1 18060 11 R10 2 18061 15 ...
0
votes
1answer
35 views

How can I group (by) two joined tables [closed]

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL 18059 18.56 R12 18060 11 R10 18061 15 R11 col: FK_ID_COL_DMA ...
0
votes
1answer
41 views

Using group by on several tables with problems [closed]

I have a poorly-designed table in an Oracle database like this: sr: id an_rap 21 2015 rap: id cf 28185 123-123 ch: id fk_id_rap FK_ID_SR 12348 28185 21 dma: ch_id ...
-1
votes
1answer
21 views

Trying to get all checkins open during day at specific times

Db structure looks somewhat like this. id, transdate, opentime, closetime 1 2015-11-02 2015-11-02 17:51:00 2015-11-02 18:51:17 2 2015-11-02 2015-11-02 17:58:00 2015-11-02 19:26:52 3 ...
1
vote
1answer
158 views

MySQL very long-running queries

I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside. Currently I'm ...
1
vote
0answers
21 views

Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

PosgtreSQL 9.4 I have two tables: CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer ); CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL )...
0
votes
1answer
494 views

SQL80001 - incorrect syntax near ')'

In the below code (that I've inherited), I'm getting the message "SQL80001 - incorrect syntax near ')'", the error being on the third line from the bottom with the + sign. I've looked at THIS SO ...
0
votes
1answer
60 views

Why does STRAIGHT_JOIN consume more CPU?

Why does STRAIGHT_JOIN consume more CPU than a regular JOIN? Do you have any idea? When I use STRAIGHT_JOIN on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. However, ...
0
votes
0answers
38 views

limit aggregate function across join

I have a facts table, and each fact has one-or-more distributors in fact_distributors. A distributor is an entity, and for reporting purposes entities are grouped together into an entity_group via ...
0
votes
0answers
28 views

How can I combine join and random choice from one table but 2 conditions?

I want to choose 200 entries randomly from a table However, I have two conditions the first 100 should be white and the other black. How can I join them? One possibility to do is to run following ...