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

learn more… | top users | synonyms

0
votes
0answers
11 views

creating best results from two tables

I have two tables, one is the results of an assessment from users(per row) and column starting with 819....X1 represents the qid. The other table holds the score for the answers (A1,A2,A3), both are ...
3
votes
2answers
110 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
32 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
62 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
25 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
25 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
46 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
35 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
61 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
68 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
66 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
111 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
80 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
107 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
96 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
710 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
468 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
103 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
123 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
51 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
119 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
31 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
267 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) ...