An SQL join clause combines records from two or more tables or views.
0
votes
2answers
1k views
Joining two tables on multiple criteria
I have two tables products and positions.
products:
id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)
positions:
id BIGINT PRIMARY KEY,
position ...
3
votes
3answers
233 views
How can I select all online friends?
I'm currently working on a social network software written in PHP & MySQL, and it's all pretty fine, but one thing I'm stuck about is online friends. My tables looks following
Users
...
9
votes
3answers
3k views
Should I use SQL JOIN or IN Clause?
I have a question about best approach. I am not sure which approach is best when data is considered variable in size.
Consider the following 3 TABLES:
EMPLOYEE
EMPLOYEE_ID, EMP_NAME
PROJECT
...
1
vote
1answer
227 views
Visually create a join using PHPMyAdmin
I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join. Is this possible on PHPMyAdmin?
4
votes
2answers
475 views
Right Full Outer Join Query
I came across 1 query which is
Select * from R Natural Outer Join S
Where R=(A,B) has tuples {(1,2),(1,2),(3,4)} and S=(B,C) has tuples {(2,5),(2,5),(4,6),(7,10)}.
To implement this I created 2 ...
2
votes
1answer
2k views
Using multiple PIVOTs JOINs and GROUP BYs for advanced report generation
I've developed a timesheet recording system, that allows users to record:
A Period of time (9:00 to 12:00)
What they did during that time (DutyActivity: 1 hour report writing, 2 hours training)
...
2
votes
1answer
239 views
Postgres planer JOIN removal
I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:
...
1
vote
3answers
815 views
Grouping results to get unique rows after multiple joins
this is my first question on dba.stackexchange, hopefully it will be worth it. I am usually a bit more active on Stackoverflow as I am a developer.
disclaimer : I don't have full control over the db ...
1
vote
1answer
286 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 ...
6
votes
2answers
2k views
SQL Server Join/where processing order
After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be ...
5
votes
6answers
6k 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
...
6
votes
1answer
447 views
Why can't we perform joins in a distributed database like Bigtable?
From this answer to What database does Google use?,
BigTable is not a relational database. It does not support joins nor does it support rich SQL-like queries.
Is this obvious? I do not ...
10
votes
3answers
1k views
Column name naming conventions and best practices
I would like some expert opinion on best practices when it comes to column naming.
The background is that according to Wikipedia, the following syntax,
SELECT ... FROM Employees JOIN Timesheets ...
3
votes
2answers
266 views
How do I calculate working business minutes?
I'm working on business inteligence reports and one of my task is to create reports for time tracking.
I have to report on business minutes (10 hours per day) within business week (monday to ...
4
votes
3answers
1k views
Optimizing query using view on EAV structure
An application is writing into a database that follows an EAV structure, similar to this:
CREATE TABLE item (
id INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE item_attr (
item ...
6
votes
2answers
2k views
What is more efficient, a where clause or a join with million plus row tables?
We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.
Sample structures:
MasterTable (Id, UserId, Created, Updated...) ...
1
vote
1answer
345 views
SQL Server: join two tables with “valid-to” dates
I have two tables like so:
T1_Key, T1_Data, T1_ValidUntil
1 A 2000-01-01
1 B 2000-06-30
2 C 2005-05-31
3 D 2004-12-31
3 E ...
2
votes
1answer
437 views
SQL Return Count of Answers with 0's for Choices from another table
My question went dead after one unhelpful answer on SO here
I need this to be optimized because it will be called often. It is going into a stored procedure.
I am given @slide (tblILDSlide.id) and ...
1
vote
3answers
114 views
Can I automate the “on” statement from MySQL queries?
Background
In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL.
I would like to know how (if) I can simplify join statements, e.g. by setting default fields ...
4
votes
3answers
1k views
Query to normalize table/combine row text
I have a table (call it oldTable) with columns like so:
ID (int),Rank (int),TextLineNumber (int),SomeText (varchar)
The primarykey is multi-part: ID+Rank+TextLineNumber.
I'm trying to ...
5
votes
5answers
11k views
Matching single column against multiple values without self-joining table in MySQL
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:
user_id ...
6
votes
1answer
173 views
Are relations slower than a big, inefficient table?
I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
2
votes
3answers
803 views
How do I join two tables on two different instances of DB2?
There are two instances of DB2 running on the same machine. One has a table aliased to the a table in the other one. Is there a way to do this in DB2 or does it have to be done via an external tool?
...
13
votes
2answers
772 views
Peculiar Oracle outer join syntax case
I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:
SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = ...
9
votes
3answers
178 views
Are there any database engines which will intuit the join condition based on the existing foreign keys?
It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...
25
votes
8answers
9k views
What is the difference between an INNER JOIN and an OUTER JOIN ?
I am new to SQL and wanted to know what is the difference between those two JOIN types?
SELECT *
FROM user u
INNER JOIN telephone t ON t.user_id = u.id
SELECT *
FROM user u
LEFT OUTER JOIN ...
5
votes
3answers
229 views
What does the output of a JOIN statement look like?
I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.
Let's say I have 2 tables:
CREATE TABLE Cities (
id INT UNSIGNED PRIMARY ...
