1
vote
1answer
12 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
19 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` ( ...
0
votes
0answers
14 views

self join of single table to find time difference of adjacent rows of a single column which are not in sequence

I want to find the time difference between two adjacent rows of a single table which are not in sequence.i made a self join of table and tried but i did not achieve what i need .can anyone please help ...
1
vote
1answer
37 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
2
votes
1answer
58 views

Understanding JOINs and why the syntax works the way it does

Heyo, I'm confused as to how this works: UPDATE original SET price=(SELECT price FROM new WHERE new.id=original.id) WHERE id IN (SELECT id FROM new); What it does is updating an original table ...
1
vote
2answers
37 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
75 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
2
votes
1answer
33 views

WHERE attribute having two relation lines in the same SQL

I have a database designed with tree tables: checklist (id, name) tag (id, name) checklist_tag (checklist_id - FK, tag_id - FK) Running this SQL: SELECT cc.id as checklist_id, cc.name as ...
0
votes
1answer
27 views

Query to join three tables

I have three tables which I have to join. I'm not a good SQL writer. Can please sombody help me to prepare this using joins: branches ----------------------------------------- branch_id | branch_name ...
1
vote
1answer
29 views

Merge a table into an existing one

I know how to JOIN two tables as create a new table out of the results, using CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ). I forgot to include some columns from the JOIN in the ...
0
votes
2answers
38 views

Using a join to return multiple values of the same column?

I have two tables user Rank In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table. ...
0
votes
1answer
60 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
1
vote
2answers
63 views

Get only the oldest entry in a join

So I have a table bugs |bugid| title| the table is lacking a date column. But when creating a new entry, most likely always a new entry gets created in bugnotes as well saying "bug created" with the ...
0
votes
1answer
43 views

How to simulate a table join on itself?

Let's assume this is my current data: product_number date value 100 2010-01-01 1 100 2010-02-01 1 100 2010-03-01 1 200 2010-01-01 1 ...
1
vote
1answer
14 views

Select the last logged change before a specified time from a change list

I want to get the last change for all issues issue, that was made before a specified end time. I have 3 tables. projects +---+------------+ |ID |project_key | +---+------------+ |1 |bug_tracker | ...
0
votes
1answer
39 views

How to join 3 tables together given 2 many-to-many relationships tables in between?

I have 3 main tables: Person, Publication and Conference and 2 many-to-many link tables in between: person_publication and person_conference I want to join all 3 main tables together (showing me all ...
1
vote
2answers
84 views

Mysql LEFT JOINing same table twice

mysql> SELECT * FROM customers; +----+------+ | id | name | +----+------+ | 1 | Matt | | 3 | John | +----+------+ mysql> SELECT * FROM purchases; +----------+------+-------+ | owner_id | type ...
0
votes
1answer
27 views

Optimizing a query so MySQL will use the indexes for the joins

Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL. Wrote a query but it's very slow. Here's the query: SELECT Table1.*, (long list of fields for each ...
1
vote
1answer
40 views

Joining Three Tables by using IDs as Foreign Keys

I'm starting out with learning MySQL and so far I realized I need to JOIN three tables. The three tables are Users Groups Members There is a list of users, a list of groups that users can join, ...
0
votes
1answer
61 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...
3
votes
1answer
42 views

Doing a join to search geolocation points in zones

I have a series of geolocation points in a table, and a series of "zones" in another; I'd like to do a join to identify which zone each point belongs in (if at all.) Points table looks like: ...
0
votes
1answer
38 views

Problem JOINing three tables

I want to show all the categories and the items inside that each category which should store in items_this_category. Note, I have created an associative table item_categories because one item can be ...
1
vote
1answer
72 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
0
votes
0answers
49 views

Very slow inner join

newby on mysql so help needed I created a my sql query, on their own they run fast but when I enter the inner join it runs forever. i did index theres id tables but no result in speed. here is my ...
0
votes
0answers
79 views

MySQL: Is it possible to join a virtual table with itself?

I am not sure if 'virtual table' is the correct phrase. I am instantiating a table X like so: SELECT * FROM ([some select subquery]) X and I would like to cross join that table with itself several ...
1
vote
1answer
111 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
4
votes
1answer
121 views

MySQL - GROUP_CONCAT returns duplicate data, can't use DISTINCT

I have a normalized database and I'm trying to return data from multiple tables using JOINs and GROUP_CONCAT. Problem: Rows are being duplicated with GROUP_CONCAT. I can't use DISTINCT because some ...
0
votes
2answers
70 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 ...
0
votes
1answer
72 views

MySQL query using left join on two inline views not returning rows without values in second view

For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second ...
0
votes
1answer
46 views

limiting rows in joins

So I have this problem with one of my queries. The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows id | ...
0
votes
1answer
80 views

MySQL: LEFT OUTER JOIN within reason

What I'm attempting to do is LEFT OUTER JOIN a table, but I only want the last inserted record of the joined table to affect the results. Currently each table row within the joined table seems to be ...
0
votes
1answer
71 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
1
vote
1answer
92 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
1
vote
1answer
505 views

MySQL: NOT EXISTS vs LEFT OUTER JOIN…IS NULL

Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better: ...
6
votes
3answers
568 views

Can someone explain why left joining two views in mysql is so slow?

Here is a question I asked yesterday - http://stackoverflow.com/questions/22180727/left-joining-two-views-is-slow. I got a good answer that helped me but I don't understand why the LEFT JOIN is so ...
0
votes
0answers
62 views

Selecting posts, user details and votes from 3 SQL tables

I have a user table with id, username, email etc.. post table with id, uid (user id), content etc and vote table with id, uid, pid (post id), vote (ENUM : '1' and '-1'). In the vote column of Vote ...
0
votes
2answers
94 views

Select * on inner join with common columns

I have 3 tables (mysql db) on which I want to do an inner join. The problem is that these tables have common columns. I know I could use aliases to differentiate them (select A.x as Ax, B.x as Bx, C.c ...
0
votes
1answer
66 views

Can't get rid of “temporary; Using filesort”

I can't get rid-off "temporary; Using filesort" when ordering columns from the second table explain SELECT * FROM post JOIN post_plus ON post_plus.news_id = post.id ...
0
votes
0answers
140 views

USING to join multiple tables with same field

I have 4 tables, each with a common field QuestionnaireId. I am wondering if there is any difference in output or performance difference between using USING on the field in each join or by specifying ...
0
votes
1answer
60 views

Ask about Inner Join & Union

i want to ask about my script this is doesn't work. i need to help ! SELECT A.*,B.mat_nama,C.mats_nama FROM pengeluaran AS A INNER JOIN(material AS B) ON(A.mat_id = B.mat_id) INNER JOIN(mat_satuan ...
0
votes
1answer
83 views

Get the value of one attribute from the reference key of another table?

I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ...
0
votes
0answers
44 views

MySQL - query join returning zero recs from table with data

I've provided a file that has the table structure and some sample data that I'm working with. I don't understand why I'm getting nulls from a joined table, where there is data. Here's my query ...
0
votes
0answers
122 views

Alternatives for subquery

There are three tables: product_hits, products, and product_subcategory_map. I want to count the number of product_hits and for each product. There could be more than one subcategory in the ...
0
votes
0answers
120 views

Select Foo object without any specific Bar events

Consider two tables: Foo: id INT, name VARCHAR Bar: id INT, foo_id INT REFERENCES Foo(id), event_type VARCHAR DEFAULT NULL, event_duration INT DEFAULT NULL Each Foo item can have ...
0
votes
1answer
55 views

MySQL: join query

I've got the following two tables: mysql> DESC domains; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
0
votes
1answer
62 views

Would it be better to do a sepparate query instead of a left JOIN?

Only a couple of rows is returned when not using LEFT since there isn't always data in the associated table - recipe_preparation. This is one of a couple of tables with the same "issue". SELECT r.id, ...
1
vote
2answers
109 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
0
votes
0answers
92 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
2answers
72 views

Is my JOIN syntax not valid? And have I understood JOIN correctly?

I've been reading up a bit regarding JOINs. And i just noticed that I've been writing the syntax a little bit different. Although I don't think there's any problem with it, I would like to hear your ...
0
votes
1answer
160 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...