An SQL join clause combines records from two or more tables or views.
0
votes
1answer
115 views
oracle huge hash_area_size
I am wondering what is the maximum setting for hash_area_size parameter?
I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour. 10Gb is not that much ...
1
vote
1answer
76 views
Join Calender Table
I have a Datatable and a Calendartable.
I do a join on this tables to get sequential dates if there is no data for this day.
My problem now is that there a several id's and i need a count for each ...
0
votes
1answer
44 views
Mysql Join with filters
I have two tables: MAINCOLOR and SUBCOLOR with the data as:
MAINCOLOR :
MAINID COLOR
1 RED
2 BLUE
3 GREEN
SUBCOLOR:
SUBID MAINID SUBCOLOR STATUS
1 1 PINK 0
2 1 ...
1
vote
3answers
131 views
Complex query with multiple normalized fields
We have a fairly simple table structure, but with a LOT of fields per table (talking 40+). This data is initially produced in plain-text, user-readable tables, but then it is translated into ...
0
votes
0answers
70 views
How can I get multiple rows instead of columns after self-joins?
This is my situation (the real table isn't about people but about paths):
[Table: People]
id | name | type | related_to
----------------------------
1 | | | 0
2 | Nico | Friend | ...
1
vote
2answers
76 views
How to get non repeat results
I've two tables default_tb_persona and default_tb_envio, this are the DDL for those tables:
CREATE TABLE `default_tb_envio` (
`codigo` varchar(7) NOT NULL,
`fecha` date NOT NULL,
`id_modalidad` ...
-1
votes
1answer
104 views
Want to show 3 tables in the same gridview and want to update/delete in parallel [closed]
Suppose I have 3 tables. But I want to show them it in same gridview and want to update and delete operations in parallel. How can I do that?
table 1: workshop
id contents demonstration ...
1
vote
1answer
109 views
How and what is the most efficient way to join two tables, retaining a particular field from both?
I have two tables, with the following columns:
Table 1
ID
Component
Data1
Data2
Table 2
ID
Component
Data3
Data4
If I have rows in these tables, with items Component1, Component2, Component3, ...
3
votes
3answers
234 views
Is it better to seperate a big query into multiple smaller queries?
There are situations which require to have really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using ...
1
vote
1answer
115 views
How does mysql deal with queries that touches myisam and innodb tables?
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
If a query involves both myisam and innodb tables, will the query use table lock or row ...
3
votes
2answers
218 views
Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode
We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.
We need ...
1
vote
1answer
417 views
Select multiple columns in subquery
I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement.
http://sqlfiddle.com/#!2/2481e/1
The link is to the schema with my current ...
3
votes
1answer
78 views
Is there a standard way to replace codes with values from a lookup table for reporting or analytics?
Suppose I have a "main" table that was probably heavily normalized and consists largely of columns that merely contain codes that are lookups into other tables (they are probably foreign keys but feel ...
1
vote
1answer
213 views
Retrieving data from inner join using LIMIT and OFFSET
These are my two tables:
table1
qid[PK] |gid[PK] |abcd | xyz | date
---------------+---------+---------+------+------------
00001 | qwe | 54 | a | 1994-11-29
00002 ...
3
votes
1answer
411 views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
0
votes
1answer
183 views
Group By on column from joined table
its about the following query.
EXPLAIN SELECT phrase, infinitiv
FROM `dict_verbs_all` `t`
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN ...
0
votes
2answers
175 views
Tuning query to remove joins for reporting
This is the query I was sent. There is currently no data in the system as I was converting this query from teradata to SQL server.
SELECT user_id FROM user_table
LEFT OUTER JOIN
(
SELECT user_id , ...
0
votes
2answers
71 views
Counting results for cross join
How I can get count results with cross join?
Example cross join:
SELECT t1.firstname, t2.lastname
FROM table1 t1 CROSS JOIN table1 t2
4
votes
1answer
213 views
NEWID() In Joined Virtual Table Causes Unintended Cross Apply Behavior
My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem.
SELECT *
FROM (
SELECT 1 UNION ALL
SELECT 2
) AA ( A )
CROSS JOIN ...
2
votes
2answers
88 views
Joining 4 tables and 2 counts
I have four tables.
UserMaster
----------
UserKey - PK
UserName
UserID - UNIQUE
UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
LoginTime
LogoutTime
CallCenter
----------
...
0
votes
2answers
216 views
Slow query with straight_join for small result
I have a performance-problem with a query in my application and I don’t understand the behavior of mysql.
The query consists of different joins, especially the join to the mentioncache-table. If the ...
-3
votes
3answers
478 views
Join tables on a partial string match
input: I have a table containing (any number of) user-supplied strings:
╔═══════╗
║ Value ║
╠═══════╣
║ To ║
║ An ║
╚═══════╝
And a table of user details:
╔════════╦══════════╗
║ UserID ║ ...
2
votes
1answer
78 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 ...
3
votes
1answer
159 views
Complex SQL Query
I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing:
host_snmp_cache:
...
0
votes
0answers
118 views
Which update is faster using join or sequential?
My this question is in sequence of my previous question I asked.
I could write two solutions using Stored Procedure instead of trigger or nested-query .
Both use a helper function ...
2
votes
1answer
131 views
Update ranking on table
I have two tables:
mysql> desc rank ;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
1answer
252 views
inner join on PK with extra criteria slow despite indices?
Given the two tables below I am struggling to understand:
why is the third query slow even though first two queries are fast
what exactly is EXPLAIN saying
can I do anything to significantly speed ...
3
votes
2answers
235 views
SQL ANSI JOIN precedence
I have a query that looks like this:
SELECT *
FROM TBLA A
LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
JOIN TBLD D ON C.Col5 = D.Col6
In which order ...
4
votes
3answers
184 views
Many-to-Too-Many Relationship
I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question):
tblBook: ID, Title
tblPerson: ID, Name
Note that the tblPerson table contains ...
1
vote
0answers
340 views
MySQL join for multiple child records in a single joined row
I have one master table (teacher) structured like
teacherId Name Class
1 Praveen 10
2 John 9
and having a child table (student) structured like
studentId ...
1
vote
0answers
136 views
Multi-Table Join to Single Destination
I am having a serious performance issue with join together four tables and placing the results (in a certain permutation) into one destination table. I am using MSSQL 2008 and good ole T-SQL.
To ...
2
votes
2answers
303 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 ...
5
votes
2answers
100 views
mysql join by 2 ways - how it works? - which gives performance difference
I had 2 different ways to query the content which showed performance difference when executed. The 1st way is
EXPLAIN select SOME_COLUMNS
from
( select *
from A
where CONDITION
) p
inner join
( ...
1
vote
2answers
265 views
Count consecutive null rows from a joined table
I have the following tables:
members:
----------------------------------------------
| member_id | member_name | member_join_date |
----------------------------------------------
events:
...
1
vote
2answers
74 views
Efficiency for contact-contact relationships schema
I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the ...
2
votes
3answers
713 views
Alternative query to this (avoid DISTINCT)
Note: I work with MSSQL 2008, but I guess it's valid for many others DB engines
I have this table "Users":
UserID User CountryID
1 user 1 1
2 user 2 2
3 user 3 3
4 user ...
2
votes
1answer
306 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
1
vote
1answer
129 views
Querying multiple tables
I am working on a query from our ticketing system, and I am having problems figuring out the best way to join these tables together.
I have the following tables/fields:
SR_service: stores ticket ...
1
vote
1answer
411 views
Optimizing multi-table left joins
I have four table which I am trying to join together.
Table event
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
5answers
565 views
Does 'Cities' deserve a separate table?
I have the following Customers table:
customer_id - int
company_name - nvarchar
street - nvarchar
city - nvarchar
comments - nvarchar
The app will only be used in part of one small country ...
3
votes
1answer
241 views
Build a three table join with a recusive table in the middle?
I have three relevent tables: Parts, PartGroup, and MarkupGroup.
Parts is simple.
PartID artificial primary key
Part part number
PartGroupID Foreign key
sample data:
1 ...
3
votes
1answer
1k 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:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
0
votes
3answers
477 views
INNER JOIN making COUNT(*) slow
I have a very simple query:
SELECT COUNT(*)
FROM messages
INNER JOIN users ON messages.user_id = users.user_id
With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
3
votes
3answers
448 views
CROSS JOIN with temp table and CASE statement
I'm trying to CROSS JOIN a table with a view. Here is my code:
SELECT *
FROM ( SELECT * ,
CASE TypeId
WHEN 1 THEN InGs828S
WHEN ...
9
votes
2answers
1k views
SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
1
vote
1answer
252 views
Mysql Join to get multiple row value from
I am new to my Mysql and need help regarding join
I have a table called employee and this table contain some foreign key value like phone, address, sex. Table phone and address have 4 rows in it, I ...
0
votes
3answers
165 views
Is adding rows better then adding columns in case their value is not consistent?
In my project I have a table of customers, each customer has his own special price for each product, It's a small business and there are currently about 5 products. The manager says there might be ...
1
vote
2answers
267 views
LIMIT a query Postgresql
im doing this query and it gives me multiple hits because of each assetid may contain many vb.title, how can i limit the result to only show 1 hit for each assetid?
'select DISTINCT v.id,
...
2
votes
2answers
107 views
Long JOINS Returns No result all tables have one to many relationship with a particular table [closed]
I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is ...
2
votes
1answer
132 views
MySQL: Very slow query, not using index, no joins involved
I am running what I think is a very simple query:
SELECT SQL_NO_CACHE m.mbr_nbr
FROM tran.member m
WHERE m.cardnbr = 000099999930
The table member is a poorly written one, with 133 columns and ...
