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

learn more… | top users | synonyms

1
vote
1answer
33 views

Get a specific number of rows from parent table?

I have a parent table, foo, and a child table, bar, in a mySQL database. Each row in foo may have zero or more child records in bar. I want to get exactly 15 rows from the parent table, and all ...
1
vote
1answer
36 views

Query using joins [duplicate]

I have the table Vendors (VendorID PK, VendorName, VendorContactFName ,.....) I want to find out the VendorName and VendorContactFName for those Vendors whose Contact First Name is different from The ...
1
vote
2answers
97 views

how do I use a value from the select in a joined subquery?

I'm trying to avoid a stored procedure or bringing it into code but they may be my only alternatives. Here's my sql. Does anyone know any SQL voodoo that could make this happen without a stored ...
0
votes
3answers
79 views

Optimise MySQL SELECT with LEFT JOIN subquery

I have a query which combines a LEFT JOIN and subquery. The dataset is quite big and the time to execute the statement is over 70 seconds. SELECT s.siblings, l.id FROM `list` l ...
0
votes
1answer
55 views

How to group joined tables by aggregate function, specifically by average group-name type?

I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt: SELECT ...
1
vote
1answer
24 views

right join leaving one row from right table

I am using MariaDB,in my sample database I am having two tables department and emp. I want to display all the department having less than 50 employees. In this case it should display all the ...
0
votes
1answer
57 views

How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

EXAMPLE Table Dept: dept_id dept_name dept_status --------------------------------------- 1 Sales 1 2 Develpment 1 3 HR 1 ...
2
votes
2answers
65 views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
0
votes
1answer
22 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
2
votes
1answer
42 views

SQLite JOIN … USING - rowid works, but then it doesn't

This works. SELECT * FROM Demographics JOIN MeasuresofBirthAndDeath ON Demographics.rowid = MeasuresofBirthAndDeath.rowid; This does not. SELECT * FROM Demographics JOIN MeasuresofBirthandDeath ...
0
votes
1answer
49 views

Join a calendar table and a transaction table

The database (DB2) in my company contain two table : Warehouse_transaction contain item_id, transaction_qty (negative or positive value), transaction_date ; Calendar contain all days between 2012 ...
0
votes
2answers
31 views

MySQL Union not showing 2nd query result set

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this. select * From ...
1
vote
1answer
58 views

How to mount a query to join by searching by last date

I have the following tables: TB_PRODUCTS ID COD_PROD Name =============================================== 1 100 Video monitor 2 101 Keyboard 3 102 Mouse ...
1
vote
2answers
47 views

An aggregate may not appear in the WHERE clause

I'm attempting to execute the following query to return a data set where the Updated Date is the most recent. I'm thinking my joins are causing the issue, but I'm not sure, and have not made any ...
2
votes
1answer
24 views

ON clause of JOIN specific syntax?

I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just ...
1
vote
1answer
45 views

Count instances of value from one column in another column located in another table

Let's say I have the following tables : Table 1 task | date 100 | 2016-01-01 101 | 2016-01-01 103 | 2016-01-01 Table 2 task2 | date 113 | 2016-01-01 113 | 2016-01-01 103 | 2016-01-01 ...
5
votes
2answers
89 views

Removing duplicate rows with two or more left join tables

With a query like this SELECT a.id, a.name, COALESCE( json_agg(b.*), '[]'::json ), COALESCE( json_agg(c.*), '[]'::json ), FROM a LEFT JOIN b ON a.id = b.a_id LEFT JOIN c ON a.id = ...
0
votes
0answers
24 views

Is joining two tables from the same DB faster than in separate DBs? [duplicate]

Would a query / view for joining two tables that reside in the same DB perform faster than one table from each DB in the same instance? I'm using SQL Server 2008 R2 and it's a simple inner join.
2
votes
2answers
50 views

Cross-product between table column and input values

I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values. Something along the lines of: WITH {1,2} as Input Select * From ...
4
votes
1answer
93 views

Table Joined To Itself Appears Inefficient

My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to ...
1
vote
1answer
53 views

MySQL Query on bad designed table with multiple Selfjoins

i have to create a report out of a bad designed database (I cannot change) It saves different Formulars into a form_values table ---+-------+-----------+------- id | value | form_item | entry ...
8
votes
4answers
400 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
31 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
65 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
53 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
135 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
39 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
32 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 ...
0
votes
1answer
37 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
31 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
77 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
61 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, ...
4
votes
1answer
154 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
70 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
65 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
110 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
37 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
27 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
46 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
55 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 ...
0
votes
1answer
69 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 ...