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

learn more… | top users | synonyms

8
votes
4answers
463 views

Query to select max value on join

I have a table of Users: |Username|UserType|Points| |John |A |250 | |Mary |A |150 | |Anna |B |600 | and Levels |UserType|MinPoints|Level | |A |100 |...
1
vote
1answer
37 views

How to select rows based on other tables - MySql

I have tables like this: CheckTable (id) (user_id) [0] [403] UserTable (id) (username) [403] [hello] I want to select only the rows from the UserTable that do not have the user id row in the ...
1
vote
0answers
32 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
0
votes
0answers
37 views

SQL Joining across multiple tables

I'm trying to query across multiple queries at once, it is fairly easy to run queries one after the other but that is not what I should be doing in this case. I have one free variable and the rest of ...
1
vote
2answers
54 views

A join that uses ordered phyical records is more efficient than others?

If i execute a join that uses ordered phyical records, it will be more efficient than a join that does not have the physycal records ordered? References are welcome.
1
vote
1answer
70 views

PostgreSQL conditional join and value distribution with materialized view

So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will ...
0
votes
2answers
55 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
1
vote
4answers
204 views

Selecting data from another table using a foreign key [duplicate]

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id ...
1
vote
1answer
40 views

MySQL: SELECT and JOIN with not exists values?

How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this ...
0
votes
2answers
35 views

How do I do a join and exclude certain records?

Table STUDENT has columns ID, LASTNAME, FIRSTNAME Table TEACHER has columns ID, TEACHER_ID, STUDENT_ID Students can have many teachers. Teachers can add students to their class. I want to be able ...
1
vote
1answer
39 views

MySQL - Many to Many Join. Left Join already Present

I am building a Attendance System for a Client and have become a tad stuck with JOIN's. I already have a left JOIN running, and want to add a JOIN on a many-to-many relationship but can't get my head ...
0
votes
1answer
33 views

How to get average of views before clicking on advertisement?

I've got two tables containing information about viewing and clicking on advertisement by each user: Clicks: userID Action Time 123 c 2016-01-08 01:57:00 123 c 2016-01-11 ...
0
votes
1answer
78 views

Why are correlated subqueries sometimes faster than joins in Postgres?

This relates to the dataset described in Postgres is performing sequential scan instead of index scan I've started working on adapting the import logic to work with a more normalised schema – no ...
0
votes
1answer
47 views

MySQL query too long to execute

I have one query that work pretty well: SELECT [MANY THINGS] FROM vehicule v0_ FORCE INDEX (PRIMARY) INNER JOIN categorie_vehicule c1_ ON v0_.idCategorieVehicule = c1_.idCategorieVehicule INNER ...
0
votes
1answer
15 views

aggregate on join latest value

This is a problem I often get stuck in and I hope there is a solution. Let's build an example: Table products: product_id, product_name Table Prices: product_id, valid_since, Price Lets say I ...
0
votes
3answers
69 views

PostgreSQL - Simple column binding

I have many tables of the same length (number of rows) that were previously ordered by some columns as they were being written on disk. My SELECT is of the form: SELECT t1.id, t1.value, t2.value, t3....
4
votes
1answer
160 views

PostgreSQL seems to create inefficient plans in simple conditional joins

Consider these two queries: SELECT t1.id, * FROM t1 INNER JOIN t2 ON t1.id = t2.id where t1.id > -9223372036513411363; And: SELECT t1.id, * FROM t1 INNER JOIN t2 ON ...
1
vote
1answer
76 views

INNER JOIN for 3 tables [duplicate]

I am doing a small course in SQL during my academic degree, I am very new with this material. I saw Inner joinning 3 tables on StackOverflow, I tried to do INNER JOIN between my three tables, the ...
0
votes
1answer
74 views

Left Join Not returning rows when date is in range of longer than 1 day

How do I left join Table A and B? SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM ( SELECT hour FROM ( SELECT * FROM delivery_hour ) xx )...
3
votes
3answers
76 views

Most recent order from multi join query

Here's the CTE version of the query, but it is returning multiple rows for the same customer. Not just the row containing the most recent open order date: with LatestOPenOrderDate_CTE AS ( Select ...
4
votes
1answer
131 views

Subquery can't find column from superquery's join

I'm running into some trouble with SQL: Basically I'm trying to get a result set back that contains a sum of ALL questions asked to employees (grouped by company) and also add the "onetime_items" ...
0
votes
1answer
38 views

Joining 4 tables of which one is unrelated

I have 4 tables as follows. I want to find a wholesum resultset, limited to 25 records, in the following way: Users from Table1 based on a condition And those Users should not be in Table2 [Non ...
0
votes
0answers
28 views

Get records with specific group name from table A and see if any exist in table B, if it does then add matching records in Table B to group

Let me start off by saying I am not a DB admin or a DB pro, I work with SQL as a php developer but that's about it. So I hope I can explain this properly and I hope one of you DB guys/gals can help. ...
1
vote
0answers
40 views

Fetch items where related rows has different values

I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
1
vote
1answer
47 views

MySQL FULL JOIN on same table

I have a table like this: And I want to get something like: fecha siguiendo seguidores 2016-01-02 430 1430 2016-01-03 450 NULL 2016-01-04 NULL 5000 ...
1
vote
1answer
58 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
0
votes
1answer
37 views

Natural join question, I have the answer, but I don't know why it's correct. Help?

So I have a relatively simple database schema. I'm not sure why the where clause in the answer magically makes the dept_name duplication problem go away. I'm new to all this and I'm sure the answer is ...
0
votes
1answer
74 views

How do I join information schema to the table's data?

I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible? I actually found another way to work around it. I am using Innodb. For the structure it's kind of ...
3
votes
1answer
49 views

Join table into different columns

I have two tables (simplified). table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer) I want to join these two tables, but depending on the type, I want ...
0
votes
2answers
63 views

Perform join on multiple tables

I have three tables right now in a postgres DB: Artists ID Name Shows ID Date Venue Price Is_sold Ages Time Multi_day Pit Artists_shows ID Show_id ...
3
votes
2answers
44 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
1
vote
3answers
74 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
70 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 ...
2
votes
2answers
134 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
91 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, ...
6
votes
1answer
60 views

Postgresql join removal

I just watched the very interesting video from Rob Farley "Designing for simplification" and I was wondering if the 4 rules given in the video for join removal also hold on PostgreSQL. If a view is ...
3
votes
1answer
80 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 ...
0
votes
1answer
74 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 ...
-1
votes
2answers
97 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 ...
2
votes
1answer
72 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 ...
5
votes
1answer
199 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
0
votes
2answers
207 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
2answers
56 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: ...
1
vote
1answer
57 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 | ...
2
votes
2answers
91 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 ...
0
votes
0answers
27 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) ...
6
votes
5answers
513 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
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
27 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 ...
0
votes
1answer
133 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 ...