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

learn more… | top users | synonyms

0
votes
2answers
40 views

Summarizing Pay Checks Based on Employee's Job Code

What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job ...
2
votes
2answers
26 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
2
votes
2answers
43 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
0
votes
2answers
48 views

Companies and addresses MySQL

I've got a database with 2 tables. companies addresses These are my tables. Adresses adress_id adress_adress adress_adressnumber adress_zipcode adress_city adress_postbus adress_country ...
1
vote
1answer
42 views

Join select queries on an id in SQL Server

I have three tables that SELECT from and I'd need to join the results. It feels like this should be a rather easy task but I struggle with the syntax. Working on Microsoft SQL Server Enterprise ...
1
vote
1answer
30 views

Select two aggregate functions on the same column using different criteria - performance

Dear stack exchange users, first question here. The database in question is a service database where a user hires a service. Now I would like to show the person that was hired an overview of how many ...
0
votes
1answer
48 views

MySQL JOIN vs LIKE - faster selects?

Okay, so first of all let me tell a little about what I'm trying to do. Basically, during my studies I wrote a little webservice in PHP that calculates how similar movies are to each other based on ...
1
vote
1answer
35 views

How to manipulate join order of PostgreSQL optimizer?

I am working on table join order optimization. While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL? How can I update ...
0
votes
1answer
51 views

Query optimization for multiple joins postgres

ER Diagram Brief description The database to be used is postgres. The database might contain upto 2 million AccountHolder records. Since the AccountHolder table is accessed and updated frequently ...
0
votes
1answer
27 views

Updating MySQL innodb table based on multiple joins

I have a table with the following (slightly simplified) structure CREATE TABLE `oak_relation` ( `o_id` int(10) unsigned NOT NULL, `k_id` bigint(20) unsigned NOT NULL, `initial` float unsigned NOT ...
1
vote
2answers
27 views

MySQL improve query syntax

I have this simple MySQL query: SELECT * FROM table1 INNER JOIN table2 ON table2.col = table1.id WHERE table1.id = '123' id is the primary key in table1. table2 has composite primary key of two ...
0
votes
0answers
21 views

Join does not not use index in PostgreSQL?

I have a statement that selects from two tables news and status. It seems that the index on the column status of the table news is not used. This table has about 70.000 rows and the query costs too ...
1
vote
2answers
50 views

Equality in JOIN or WHERE clause? Efficiency and style

Of the two queries below, which is the most efficient for postgresql? And which has better style in terms of readability, etc. The difference is the placement of the statement doctor.type != ...
0
votes
0answers
31 views

“DISTINCT” JOIN in mySQL [duplicate]

+----+-------------+-----------+---------+ | id | login_ip | hostname | user_id | +----+-------------+-----------+---------+ | 1 | 192.168.0.4 | 'host 1' | 1 | | 2 | 192.168.0.4 | 'host ...
1
vote
1answer
38 views

Large Data Long Query A Lot of Joins horibble performance

I have 3 Tables Person, Family, City Person Table (6 Million Rows) id (primary key auto_increment unsigned) family_id (index) city_id (index) full_name first_name (index) last_name (index) ...
2
votes
1answer
33 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
0
votes
1answer
27 views

Stacked Left Joins or Left Join inside a Subquery?

OK, this is a fun one. Designing a database for a gaming system. I have 3 tables that are relevant for the query I'm trying to work on right now. character_skill_log with the relevant fields of id, ...
0
votes
1answer
27 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
0
votes
0answers
26 views

Receiving duplicate results in one condition of JOINED query

I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas ...
3
votes
4answers
812 views

Self Join? Or am I wrong?

We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item: customer item Sally 1 Sally ...
1
vote
1answer
30 views

Help on MySQL joins

I'm building a system to monitor currency prices. Part of my database structure looks like this: My system is trying to pull the api ticker for each exchange with this query: SELECT distinct ...
0
votes
1answer
63 views

Merging tables, but ignoring duplicate columns

I have two tables I want to merge: city1(id, city, state, zip, lat, lon, county) city2(id, city, state) The tables contain overlapping city and state, but not primary keys. I can't figure out how ...
1
vote
1answer
32 views

Left outer join not returning all columns in a grouping query

I'm trying to query all users to find the price of orders in the last month. I'm doing this on postgresql. Here's a toy database to show my problem: I have a 'people' table and a 'orders' table. I'm ...
2
votes
1answer
73 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
3
votes
1answer
49 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
0
votes
1answer
43 views

Select Information from All Tables [closed]

EER Diagram Question List the customer number, customer name (first and last), order number, order date, employee number, employee name (first and last), product number, product name, and order ...
2
votes
1answer
44 views

(How) can I return the most specific row on a join?

I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
0
votes
0answers
19 views

PostgreSQL SELECT and JOIN question [duplicate]

Trying to fix the previous post text formatting issue there was and I did not recognize: I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( ...
0
votes
0answers
26 views

SQL JOIN excluding duplicate

I'm using PostgreSQL under ubuntu 14.04. I have two tables as below: create table material_base_name ( id bigserial, base_name varchar(50), constraint pk_materials_base_name primary key (id) ); ...
0
votes
1answer
44 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
0
votes
2answers
89 views

SQL join with multiple columns as FK to same list table

I have a table that has 5 references to a list table for normalization of the first table. So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, ...
0
votes
0answers
14 views

Handle a Search on 2 Big Tables

Hello i want to realize a exchange system with 2 tables. 1 Table for "the user is looking for" 1 Table for "the user has to share" the Tables are very simple id bigint user_id bigint object_id ...
0
votes
0answers
30 views

MYSQL Tables Join Filtering For Favorites

I am in need of some help figuring out the best way to join separate tables I am using. I am essentially trying to output a list of schools (first table in MYSQL) where one column is an empty star ...
0
votes
1answer
35 views

Joining a column from another table with InnoDB engine

I'm using MySQL with InnoDB and I want to join the location column from products table into a productStock table. I have this table 'products' mysql> select * from products; ...
0
votes
2answers
37 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
0
votes
2answers
42 views

Combine two queries from 1 table, 1 to Many

I have the following queries select o.increment_id, o.customer_id, round(o.base_grand_total * o.base_to_global_rate,2) as revGBP, DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder', from ...
0
votes
1answer
39 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 ...
0
votes
3answers
182 views

Alternatives for a LEFT OUTER JOIN query

Problem I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER ...
1
vote
0answers
61 views

How to use join with this query? [closed]

This query is working fine, and giving the right result. In this query all columns are in one table, but now I have made some changes. Now columns named Semester and Major are in different Table ...
0
votes
1answer
45 views

SELECT count vs DELETE JOIN. Why/how are they different?

Can someone explain in what way these queries could be different and the results are not the same? > begin; Query OK, 0 rows affected (0.01 sec) >select count(*) from tableA a JOIN tableB ...
0
votes
0answers
22 views

How to join 2 tables and traverse up hierarchy at the same time

Any help that can be given on a current problem I'm having in an Oracle environment would be greatly appreciated. I have 2 tables. One contains jobs to be performed on pieces of equipment, the other ...
0
votes
0answers
36 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
0
votes
2answers
37 views

How are derived table implemented in MySQL

When MySQL makes a derived table as a result of SELECT (SELECT ...) etc or SELECT * FROM a JOIN (SELECT * from B) etc or SELECT * FROM (SELECT ...) Are these derived temporary tables created ...
0
votes
1answer
52 views

How to JOIN more than two tables?

Following is the schema of my Tables: Marks_Table (SubjectCode,TypeID,MarksObtd) Subjects_Table (SubjectCode, Subject) Types_Table (TypeID, Type) I want to Select the Marks_Table table with ...
0
votes
1answer
25 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
0
votes
0answers
29 views

Packet and N-SubPacket Msql query ( same as catgeory and N sub catgeory )

First of i am PHP Developer , and working on huge project alone .i think i have made many mistake in creating table. and below is one of those. I am making packet type system when in my Packet Table ...
0
votes
1answer
76 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
2answers
58 views

mysql join not working as expected

i want to select schedule of specific teacher for a given day .... statement should assume that a teacher may teach in elementary and high school here is my statement `select class.name as ...
1
vote
1answer
153 views

Select all rows with multiple conditions in another table

Here is my simplified database scheme -- ----------------------------------------------------- -- Table `products` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS ...
0
votes
1answer
38 views

SQLITE table joining with group

MESSAGE TABLE USERS msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name 1 1 2 hi 22/1/14 ...