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

learn more… | top users | synonyms

3
votes
2answers
112 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
29 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 . | . ...
0
votes
0answers
20 views

How to get records in group when records are duplicated?

I have following table structure. Here id is primary key and auto incremented.Here Records are duplicated and id with 1 is parent records and id with 2 & 3 are its child records. same with id ...
3
votes
1answer
75 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
33 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
26 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
63 views

LEFT JOIN DEFAULT NULL VALUE

I had query simplified like this : SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id Which b is from group concat select (very dynamic column). Sometime there are 3 column in b, sometime ...
0
votes
0answers
27 views

Join two tables and count a column using mdx

I am trying to join the two tables as below Table1 --------------------------- Product| StoreType| Store --------------------------- 1 | 10 | X ...
0
votes
1answer
26 views

placing conditions in a join query join on vs where

Is there and advantage to one of the following options over the other or are they the same ? select * from T1 Join T2 on T2.id=T1.t2_id and T2.field=value; vs. select * from T1 Join T2 ...
0
votes
1answer
48 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, ...
0
votes
2answers
53 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
25 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 ...
0
votes
1answer
37 views

Creating Database Tables for Reviews functionality that incorporates Tags/Categories

I'm wanting to create some simple website functionality through a little database idea I have in order to give myself the ability to create/retrieve customer reviews based upon tags/categories. More ...
2
votes
1answer
2k 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
62 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| | ...
2
votes
1answer
71 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
37 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
31 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
67 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
21 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
33 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, ...
1
vote
1answer
113 views

How to put the results of a query into a variable?

I have written a Postgres anonymous block. In which I have written join query on multiple tables like: select a.portal.*, a.p_fb_config.*, a.p_gplus_config from a.portal left outer join ...
0
votes
2answers
81 views

Join multiple Tables by date

i got some problems on my approach to join a couple of tables by a date range. This are my tables: RETURNS: RETURN_DATE RETURN_AMOUNT 02.05.14 2 02.05.14 3 02.05.14 1 ...
0
votes
1answer
17 views

Adding a column to aggregated data where new value only applies to a subset of the aggregation

Having trouble explaining this. I have a table like this: Label | Value A | 3 B | 1 C | 4 And another like this: Label | Value | ID A | 1 | ID1 A | 1 | ID2 C ...
2
votes
2answers
40 views

Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion. I have a data set that is really just a string of words. Think of ...
1
vote
2answers
108 views

Query to return zero if nothing exist

I have a table With currency, paymenttype and invoiceamount. I have to write a query to get currency, paymenttype and total invoiceamount made. This is pretty simple with group by. But actually I ...
0
votes
1answer
47 views

How to split a multivalued column, do a lookup and update another column?

This is a part of my previous question. How can I achieve the following for all the records in my table using a single query? SET @TeamMembers = TeamMembers from tblProjects where ID = '125' ...
0
votes
0answers
31 views

How to get list of ids from joining two tables (one to many) whose results is not present in a list?

Say Table 1: tID, tName Table 3: t2ID, tID, tSubjects One tID can be have multiple t2ID. What I want is list of tIDs who's t2IDs should not just have a list of t2IDs (1,5,6). i.e They can 1,2,5,6 ...
1
vote
0answers
98 views

Limit relation one to many or one to one

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps ...
0
votes
1answer
45 views

Mysql JOIN with 25million rows

Recently I got stucked with a table where its grown to +25million and I have got a query that's doing a JOIN on it. The query take 2.5hours to execute even though I got Good 96GB RAM and RAID10 with ...
0
votes
2answers
43 views

Join that returns always 1 record

I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value). I need a join to this ...
-1
votes
1answer
36 views

Mysql query giving incorrect values [closed]

When this SQL query is executed it gives incorrect value, please advise select sum(cash_management.cash_in), sum(cash_management.cash_out), sum(cash_management.pay_in), ...
1
vote
2answers
712 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
-3
votes
1answer
16 views

Outer join with blank spaces to accomodate both sides

I am trying to join two tables both relating to cities. One table has the columns City and Writer, the other has City and Park. I want to end up with a table that has the columns Writer City Park. If ...
2
votes
3answers
502 views

Find duplicate records across one-to-many relationship tables

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly. ...
2
votes
1answer
104 views

SQL aggregate function performance in a table with many records that is well-indexed

My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information. There are three tables: ...
0
votes
2answers
130 views

How to select the largest value of the same column in multiple tables?

I have multiple tables which have some columns with the same meaning, eg., event_timestamp. The schemas are like following: Table A: ...
0
votes
2answers
78 views

Creating joined rows from multiple tables on date field

I need to be able to take Table A and find the records that match in Table B and Table C based on its ID and add them together. The trick is that I only want to add the results from Table B and Table ...
2
votes
1answer
55 views

INSERT multiple values via join

I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys. ...
2
votes
0answers
19 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
-1
votes
1answer
53 views

Joining 3 tables of given sizes in most efficient way

Say I have 3 tables, A, B , C of sizes 10, 10^4 and 10^6. What is the most efficient way to join them? This is my reasoning. Assuming you join over primary key, if you join A with C first, you will ...
1
vote
1answer
2k views

MYSQL query to find records between two date range

I am working in a web application using PHP and MySQL. I have two tables Employee employee_id email_id 3 tl1@jasmine.com 4 agent1@jasmine.com 5 ...
2
votes
1answer
59 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
1
vote
2answers
85 views

1 hour query, is there another way to do this?

I saw a couple questions similar to this but none could answer my question, really. I have two tables, and I am trying to find those rows where a match is found. user has about 350,000 entries and ...
0
votes
0answers
121 views

count rows with same value and get column from other table

I'm trying to count product numbers for a period of time there are three tables. invoice (table) Column Column ------------------------------------------- |invoicenumber ...
0
votes
1answer
32 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 ...
0
votes
2answers
272 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
1answer
56 views

What's the point of SQL joins if you have OLAP?

I want to understand the purpose of using SQL joins from a business analysis perspective. Let's say I want to analyze customer data. Why would I want to pull data from multiple tables using join ...
0
votes
0answers
31 views

Use join to aggregate data from various normalized tables

I have a database as follows: tbl_clients (Primary Key: client_id, Foreign Keys:place_id,district_id,zone_id,country_id) tbl_places (Primary Key: place_id) tbl_districts (Primary key: district_id) ...
1
vote
1answer
226 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...