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

learn more… | top users | synonyms

1
vote
2answers
91 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 ...
2
votes
1answer
70 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 ...
1
vote
1answer
192 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) ...
0
votes
1answer
51 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, ...
3
votes
4answers
871 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 ...
0
votes
0answers
52 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 ...
5
votes
2answers
232 views

Group_by and having vs Joins when table is self joined 5-6 times

Now the situation due to which this question came to me. Schema is Table User_Read_Book user_id | book_id Now I want to get users who have read certain books. Say give me users who ...
0
votes
1answer
1k 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
36 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 ...
3
votes
1answer
65 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
203 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
50 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
1answer
53 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
0answers
20 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
44 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
0answers
21 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
58 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 ...
2
votes
1answer
1k views

Select multiple columns in subquery

I am having trouble selecting all columns in a sub query and can't seem to get anywhere with joining for replacement. My Table Data is: CREATE TABLE `w7nwd_com_mtapp_product_engine` ( `id` ...
0
votes
1answer
49 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
1answer
227 views

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
2answers
100 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
75 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
3answers
1k 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
68 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
73 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
50 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 ...
2
votes
1answer
3k views

CROSS APPLY versus INNER JOIN

I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well. Maybe someone ...
0
votes
2answers
81 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
0answers
31 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
215 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
1answer
63 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
43 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 ...
1
vote
1answer
2k 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
63 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 ...
1
vote
2answers
158 views

JOIN on variable number of columns

I have the two tables courses AND something_about_courses: courses course_code course_session course_type something_about_courses course_code course_session some_description To ensure that I ...
3
votes
1answer
4k views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
0
votes
1answer
1k views

Pivoting some row data into columns (SQL Server 2008 R2)

Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another. Full-Disclosure: I did ...
0
votes
1answer
137 views

queries for hierarchical data

This table is a questions and answers CMS and i want to know how data is fetched from these tables. +------+----------+-------+ | id | parentid | type | +------+----------+-------+ | 1 | 0 ...
0
votes
1answer
68 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
4
votes
2answers
18k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
2
votes
2answers
270 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
3
votes
4answers
12k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
0
votes
2answers
362 views

left join not working [closed]

CREATE TABLE `table1` ( `id` INT(10) NOT NULL ) ENGINE=InnoDB; CREATE TABLE `table2` ( `id` INT(10) NOT NULL, `value` INT(10) NOT NULL ) ENGINE=InnoDB; INSERT INTO table1 VALUES (1), (2), (3), (4); ...
1
vote
1answer
1k views

Joins in Update statement in sqlite

CREATE TABLE Table_1 ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , EmailId VARCHAR ) INSERT INTO Table_1 VALUES (1,'raj@gmail.com'); INSERT INTO Table_1 VALUES (3,'test@gmail.com'); ...
0
votes
1answer
94 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
0
votes
0answers
42 views

Minimize table joins in design

Probably didn't come up with the best title, but I'll explain what I'm trying to do. I need to be able to track gym members, trainers, workouts, and gym locations. I've created a simple example below. ...
1
vote
1answer
81 views

MySQL Join, Force NULL Row

I'm having a bit of trouble figuring out how to do this -- I need to select all rows from table t1, join table t2 -- but include a null row from t2 for each record in t1 (even if rows match in t2). ...
0
votes
2answers
77 views

MySQL get values from the other table by using inner joins

In mysql I have 3 tables called as ps_product_lang ps_order_detail and ps_image here are the tables sample data === ps_product_lang === CREATE TABLE IF NOT EXISTS `ps_product_lang` ( ...
1
vote
1answer
803 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...