0
votes
1answer
40 views

Low-level relational operators from SQL

I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement; SELECT d.name, k.city FROM DOG d, KENNEL k WHERE ...
1
vote
0answers
32 views

SELECT, PROJECT, CARTESIAN PRODUCT and JOIN

I have the following tables; DOG +----------------------------------------------+ | Name Type Age Kennel_name Owner_name | +----------------------------------------------+ ...
0
votes
2answers
64 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 ...
3
votes
1answer
52 views

postgres filtering select based on combination of joins

I am having trouble filtering a request that depends on a combination of child elements (one-to-many relationship) Please consider the following example. CREATE TABLE sample_container ( id serial ...
0
votes
1answer
58 views

Get all items from list table which don't have a successful log entry for 'sent' in log table

How might one retrieve items that when joined either don't have a specific field (sent), or if they do have that field (sent) then they have an additional field (failure_reason) as well for every sent ...
1
vote
1answer
223 views

Select records in one table but not in another, when the second table is not matching on primary key

I have two tables from which I need only the results that appear in one table (list) which do not appear in the second table (cardinal). The list table's primary key is sku, and the table has a ...
1
vote
1answer
95 views

INSERT INTO selected columns creates new column from JOIN [closed]

I have an INSERT INTO ... SELECT.. query that uses a JOIN on two tables. The results of the query should take a subset of the information and insert it into an existing table. That part works, but the ...
0
votes
1answer
78 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
0
votes
0answers
135 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
1
vote
4answers
2k 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 ...
1
vote
1answer
108 views

How to add additional tables/fields into a query with multiple inner joins?

Given this query (many thanks to RolandoMySQLDBA from question Select records that do not have associations outside a certain list), how do I add other fields from other tables into my result? SELECT ...
1
vote
1answer
983 views

Error “column does not exist” in a SELECT with JOIN and GROUP BY query

I'm using PostgreSQL 9.1 with a Ruby on Rails application. I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id ...
0
votes
2answers
2k views

MySQL: Join one Row in a table with two Rows in other table

In my MYSQL Database COMPANY. I have two tables, like below in my diagram (arrow shows relations): `users` `user_login` +--------------+ ...
2
votes
2answers
2k 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
1answer
496 views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
2
votes
1answer
86 views

JOIN 2 tables, and query data from them 3 times

Basically I have 2 tables: post_meta and posts. post_meta has a post_id, key and value: post_id key value 6343 make Chevy 6343 model Volt 6794 make Chevy 6794 model ...
2
votes
2answers
606 views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
3
votes
1answer
2k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
1
vote
3answers
63 views

How can I query to do this?

I have 2 tables, in feeds I have my feeds and (feeds.ID) is the primary key. second table is items. and feeds.ID is realted to items.feed_ID, so with a simple inner join i can extract the data of each ...
1
vote
4answers
401 views

cross self join to get more rows - better solution?

I have a table employees: +----------------+------------------+ | Field | Type | +----------------+------------------+ | id | int(10) unsigned | | persons_id | ...
0
votes
1answer
2k views

How can I select the latest record having one state where no later records exist with any other state?

I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget". widget state timestamp ------ ----- ...
21
votes
1answer
2k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
1
vote
1answer
308 views

Simple select statement not returning correct data

I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and ...
9
votes
6answers
11k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...