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

learn more… | top users | synonyms

3
votes
2answers
43 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
1
vote
3answers
72 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
68 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 ...
0
votes
1answer
73 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 ...
2
votes
2answers
87 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
68 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, ...
3
votes
1answer
79 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 ...
4
votes
2answers
109 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
-1
votes
2answers
73 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 ...
5
votes
2answers
80k views

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, ...
2
votes
1answer
69 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 ...
0
votes
2answers
52 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: ...
6
votes
5answers
385 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
2answers
39 views

How do I write this cross Join in MySQL?

It should create a set of columns (ControlRange, SensorBias, SensorError, VDCurve, INSHalfCurve, INSSensCurve, ExogDEXCurve, InitGlucose) and all permutations of their values. I want the order given ...
3
votes
1answer
175 views

Minimizing Page Fetches

I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use ...
2
votes
2answers
80 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 ...
1
vote
1answer
49 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 | ...
1
vote
1answer
3k 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 ...
0
votes
3answers
76 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 ...
0
votes
0answers
24 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) ...
0
votes
1answer
88 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 ...
1
vote
2answers
1k views

Best practice - use of derived table or use the original table in join?

Imagine I have the following tables in Teradata : TableA : Field1|Field2|Field3 TableB : Field1|Field4|Field5 I am trying to join these two tables Method 1 : SEL TableA.Field3, TableB.Field4 ...
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
24 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 ...
2
votes
1answer
57 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 ...
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, ...
3
votes
1answer
2k views

How to use a group by in inner join

I have Two tables: Table 1 ID totalamount --- ----------- 100 1000 101 500 Table 2 ID Individualamount --- ---------------- 100 500 100 300 100 100 101 200 101 300 I Need a result ...
1
vote
1answer
30 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
217 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 ...
1
vote
1answer
101 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
57 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
59 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 ...
0
votes
1answer
63 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
0answers
23 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
30 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 ...
2
votes
2answers
46k views

SQL Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update ...
0
votes
1answer
48 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
39 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 ...
0
votes
1answer
23 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
31 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 ...
4
votes
2answers
66 views

Join subquery by year and month

I want to collect data from differed tables grouped by year and month. I'm using subquery for each sub data. Using mysql. SELECT YEAR(td.db_date) AS stat_year, job.total_jobs FROM ...
-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 ...
-8
votes
1answer
9k views

Using inner join to get data from multiple tables [closed]

I have 5 tables: A (a_id, a1, a2) B (b_id, b1, b2, a_id) C (c_id, c1, c2, b_id) D (d_id, d1, b_id) E (e_id, e1, e2, d_id) Here is some information table b have a_id match with table a (a_id). ...
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
351 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
54 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
34 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
26 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 ...
0
votes
1answer
70 views

Sum of values for each row

SELECT ratings.programme_id, programme.programme_name, ratings.ratings FROM programme INNER JOIN ratings ON ratings.programme_id = programme.programme_id I have two tables a and b a has id and name ...