Tagged Questions

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

learn more… | top users | synonyms

0
votes
1answer
79 views

Get all items from list table which don't have a successful log entry for 'sent' in log table

How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent ...
2
votes
1answer
87 views

Clustered Index / Index Oriented Table performance in join, worse than nonclustered?

Can a clustered index (or IOT in Oracle) be detrimental, when to be used on a very "broad" table, but only few columns are used? In this case, the "Product" table is used only like a junction table ...
1
vote
2answers
886 views

Full outer join does not work for two small tables, error 1064

folks. I am currently exploring the capabilities of MySQL while doing simple exercises with diverse queries. I was reading the following article that explains how table JOIN works: ...
1
vote
1answer
740 views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
1answer
58 views

Formulating a Join Query for PostqreSQL

I have a quick question, if someone can help, I would be grateful I have the following tables User, Video, User_video user has userdetails (user_id, name, email) video has videoDetails ...
1
vote
0answers
67 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
1answer
4k 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 ...
1
vote
0answers
93 views

Relation between lossy decomposition and Natural join?

I am confused with the lossy decomposition and natural join. Natural join(with common attribute): only respective common attribute for the resultant table is formed. Lossy: Natural join on tables ...
0
votes
2answers
239 views

How can the LEFT JOIN return more results than the unjoined query?

Consider the following queries: mysql> SELECT count(*) FROM list l WHERE l.source='blink'; +----------+ | count(*) | +----------+ | 3372 | +----------+ 1 row in set (0.00 sec) mysql> ...
1
vote
0answers
123 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
0
votes
1answer
604 views

Calling another function inside a postgres function

I have a function A and function B. Function A Name is f_a() and the code is: Select id,address from A; Function B Name is f_b() code is: Select C.address,B.name From (Select id,name from B, ...
1
vote
1answer
50 views

Estimating a Size of Joining a Relation with itself

I'm studying size estimation of logical query plans in order to select a physical query plan. I was wondering what is the size of joining (natural join) a relation to itself? e.g R(a,b) JOIN R(a,b)
1
vote
2answers
98 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
0
votes
1answer
111 views

Mysql list data from left join query

I have 2 table and i want to pull data from both of them. This is sample for my table structure: Table tbl_a = id, time, note, customer Table tbl_b = id, time, name, surname, note This is my mysql ...
0
votes
1answer
177 views

Data Analysis with MongoDB or MySQL

I have a need for processing some data in order to come up with some statistic. This is the scenario: Each user can answer several questions from the question list. They pick their answers from a ...
0
votes
1answer
91 views

Join and Count based on Specific User ID in SQL

I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar ...
1
vote
1answer
174 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
195 views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
1answer
90 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...
0
votes
1answer
105 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
4
votes
2answers
2k views

SQL join query to show rows with non-existent rows in one table

I am trying to get some reporting done for employee time records. We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of ...
0
votes
1answer
33 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
2answers
195 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
0
votes
1answer
167 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
1
vote
1answer
26 views

Optimised query to select document and possible annotations

I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current ...
4
votes
1answer
5k views

Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

TLDR; If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening. Following up on my previous ...
0
votes
1answer
41 views

How to create new column with value from other table

I have a table called 'menus': menu_id | sort | page_id | parent_page_id ---------|------|---------|---------------- 1 | 0 | 1 | NULL 1 | 1 | 2 | 1 1 | 2 | ...
1
vote
2answers
884 views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself ...
1
vote
1answer
167 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
109 views

How to prevent Cartesian joins when value can be in either field?

I have a table of articles where the same article may be presented to different audiences with slightly altered text depending on the user's access level. When this occurs, the article will have the ...
0
votes
0answers
439 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
0
votes
1answer
109 views

Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

I've hit a brick wall and please ask help. I'm building a table up for (in this example) a ticketing system where e.g. Ticket 1 will allow Parent: Either parent Peter or Sally, Child: to come without ...
1
vote
3answers
1k views

Conditional Join - Specific Column Select

I have tables like these: Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...) Then say that I have conditional join like this: select isnull(Emp.Full_Name, ...
-1
votes
1answer
64 views

COUNT WITH JOIN [closed]

I've the following tables: fss_post(post_id,text) fss_comment(idComment, post_id, text) Is possible to write a query that give me as result a row for each post_id with total comment count for ...
0
votes
1answer
140 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
0
votes
1answer
84 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
1answer
48 views

Append columns with values from has many reletionship

I have two tables orders and items. Every Order has many Items. orders - id - created_at - paid_at items - id - title - amount - quantity - order_id Let say that there are 3 kinds of items. Chair, ...
0
votes
1answer
610 views

Query with PIVOT fails with second LEFT OUTER JOIN to same table

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm ...
1
vote
1answer
136 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 ...
5
votes
1answer
3k views

Query to compare two subsets of data from the same table?

My problem: I'm trying to compare to subsets of data within one table, and I've got two methods that partially work and one certainty that there's got to be a more correct way to do it. The idea ...
2
votes
2answers
3k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
0
votes
1answer
258 views

MySQL : LEFT JOIN not fetching required data

I'm using left join in MySQL as mentioned below: SELECT Q.QUESTION_ID, school_id FROM Q_MASTER AS Q LEFT JOIN ( SELECT keyword, school_id FROM ...
0
votes
1answer
200 views

Efficient way to write query for multiple table which depends on column value of a table in mysql?

In the given schema, there is a table A having column A1,A2,A3,A4 There are three more tables i.e. B,C,D If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup ...
0
votes
2answers
1k views

how to join table from different database with different Users of database?

I have Two database called. Database1: host: 127.0.0.1 user: root1 pass: root1 dbname: db1 Table 1: Category ID INT(11) Primary Key Name VARCHAR(255) Database2: host: 127.0.0.2 ...
0
votes
1answer
169 views

Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

I have a table of around 350m rows on a linked server in which I added an additional INT column to serve as a count(external_identification) of records as the result of a join on PACKAGE and DOC2. ...
2
votes
1answer
1k views

Concatenate one-to-many field in single result?

Say I have the following query: SELECT * FROM AppDetails, AppTags WHERE AppDetails.AppID = '1' AND AppDetails.AppID = AppTags.AppID Which gives the following results: AppID AppName ...
0
votes
0answers
240 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
1
vote
1answer
59 views

What ways are there to limit the processor / ram requirements of a specific query?

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that. What ways are there ...
0
votes
1answer
947 views

Getting multiplication of column values on a joined table based on condition

The title was best I could find to explain my question, I don't think it helps though. Anyways, I have two tables: CREATE TABLE YEARLY_RATES ( RATE_YEAR NUMBER(4,0), RATE NUMBER(6,4) ); ...
1
vote
1answer
114 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...