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

learn more… | top users | synonyms

0
votes
1answer
24 views

sql query for hierarchal search [on hold]

In the below relational table, I want to search all user id's whom are are under any particular sponsorid in hierarchical manner User Id Name Sponsor Id 10030080 TULSI RAM ...
0
votes
0answers
23 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
19 views

Select range of non-identical date from different table

On MySQL5 with innoDB, I have sensor data in multiple tables, all with an ID and a DATETIME field, like so (two tables shown, but there are more). The IDs and DATETIMEs are not identical between ...
0
votes
1answer
41 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
9 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
22 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
35 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
18 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
19 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
24 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 ...
0
votes
1answer
24 views

Update various fields with if condition compare other table fields [closed]

Update Query Not working for some fields alone. update charged u INNER Join mis_charged v on v.id=u.id set u.act_val= if(v.DAYS=u.Activation_date,USG,Null), u.one_val= if(v.DAYS=u.Day_One ,USG,Null), ...
0
votes
1answer
42 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| | ...
0
votes
0answers
28 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
14 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 ...
2
votes
1answer
46 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 ...
-1
votes
2answers
53 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
19 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
29 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, ...
0
votes
2answers
48 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
0answers
11 views

Postgres Join that allows for an empty table [migrated]

I have the following schema, and I'm having a lot of trouble joining these tables properly. I'm trying to get all folders that don't have a file updated in the last 10 mins in them. But I want to ...
0
votes
1answer
15 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 ...
1
vote
1answer
89 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 ...
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
94 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
35 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
22 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
68 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 ...
-1
votes
1answer
30 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), ...
0
votes
2answers
41 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 ...
-3
votes
1answer
15 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
105 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
40 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. ...
0
votes
2answers
69 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 ...
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 ...
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
vote
2answers
72 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
61 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
22 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 ...
1
vote
2answers
76 views

Calculating percentage from two tables

I have two tables: First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table. Second one contains interactions of mentors with mentees on different ...
0
votes
1answer
48 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
20 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) ...
0
votes
0answers
25 views

Footer with counter of records, using union of query

I have a report that has a header (display the current date), I use UNION to join the header with the main query that returns multiple records, it has 10 columns. After that another UNION for joining ...
0
votes
2answers
60 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
0answers
32 views

Can I optimize a left join for a single match?

Consider a join A LEFT JOIN B ON A.id = B.a_id: B.a_id is nullable -- most but not all B records point to an A record. A can, at most, only have one matching B record (business logic). Not all A ...
0
votes
1answer
22 views

Calculations involving thresholds between two tables

I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is ...
0
votes
1answer
29 views

Joining WordPress Comments with Post Titles from MySQL DB

I'm working with a wordpress database and need to get some data on the comments. The goal is to get comments from a decommissioned site. I have the database running on my local system and can ...
0
votes
2answers
27 views

Mysql join not working

I have database with two tables: logs & src_info mysql> describe logs; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | ...
1
vote
1answer
34 views

Is it possible to pick next row's cell value on same column if condition not matched?

I have a table flaggedcomment and a table price. In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as ...
2
votes
2answers
86 views

Is there a way I can speed up queries against this particular view?

I have three tables in a PostgreSQL database that I'm querying via a view and some joins. CREATE TABLE network_info ( network CIDR NOT NULL, some_info TEXT NULL, ...
0
votes
2answers
38 views

join value in a RETURNING which is not inserted

Im doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert.Is it possible? I get a ...