Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with query
Search options questions only
not deleted
user 3787
Writing queries to request or change information stored in a database or other information management system. Questions should include table and index definitions, sample data, expected output, and a tag for the specific system (e.g. MySQL, PostgreSQL, Oracle, SQL Server, MongoDB).
0
votes
1
answer
339
views
How to JOIN with missing rows?
I need to JOIN two tables to correct the price with exchange rates as
SELECT date, price/rate AS adjusted FROM
table1 a JOIN table2 b ON a.date=b.date
The problem is that table2 does not have the e …
- 4,531
1
vote
1
answer
350
views
How to INSERT all entries to a map table
I have a map table as
CREATE TABLE keymap
(
col1 int(11) unsigned NOT NULL,
col2 int(11) unsigned NOT NULL,
col3 int(11) unsigned NOT NULL,
FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE …
- 4,531
0
votes
1
answer
86
views
JOIN by part of two column names
Consider a table with the structure of
id col1
WqDs.first
WqDs.second
eeRT.first
eeRT.second
How can I JOIN the table to itself using the first part of the id
SELECT idxx, a.col1 AS Fir …
- 4,531
4
votes
2
answers
8k
views
Get the highest parent id recursively in MySQL
COLLATE utf8_general_ci;
INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);
I intent to get
ID Highest ParentID
1 NULL
2 1
3 1
4 1
I planned to create a recursive query …
- 4,531
0
votes
1
answer
650
views
Creating a parent table from child relationships
I have a child relationship table as
CREATE TABLE ItemRelationships
(
Item1 int(11) unsigned NOT NULL,
Item2 int(11) unsigned NOT NULL,
INDEX(Item2),
FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON DEL …
- 4,531
4
votes
1
answer
1k
views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables posts … In this query, mysql will find all posts tagged by any of these words, and order them by PRIMARY KEY. …
- 4,531
1
vote
2
answers
31
views
How do you check if two tags exists in a tag_map GROUP By article_id?
In a fairly simple tag_map,
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB;
How can I che …
- 4,531
0
votes
2
answers
45
views
How to SELECT multi-columns from a column?
How can I make a SELECT query to get text for item_number 1 and 2 together for each book_id,section_id,note_id
SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2
TEXT_number_1 = text (WHERE … item_number=1)
TEXT_number_2 = text (WHERE item_number=2)
I considered two options:
GROUP BY, which can give me a CONCAT(TEXT_number_1,TEXT_number_2)
JOIN, which I am not sure what is the best query …
- 4,531
1
vote
3
answers
5k
views
How can I use LOAD DATA LOCAL INFILE for specific columns?
I have a directory of text files, as the filename is the id, and the content text for this table:
CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB …
- 4,531
1
vote
2
answers
6k
views
LIMIT the number of rows in GROUP BY and GROUP_CONCAT with ORDER
In this sqlfiddle,
CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
val int(11) unsigned NOT NULL,
rank mediumint(7) unsigned,
INDEX(id),
INDEX(rank),
PRIMARY KEY(id,val)
) ENGINE=InnoDB DEFAULT CHARSE …
- 4,531
1
vote
2
answers
149
views
How to order by COUNT(*) of unique entries in GROUP BY?
The above query returns in order of the number (e.g., if word1 appears 100 times).
NOTE: WordMap table is huge, and this is the reason I went for sub-query rather than JOIN. …
- 4,531
0
votes
3
answers
1k
views
Difference of two columns with possible NULL cells
I need to sort a table by the difference of two columns as
SELECT (first - second) AS dif
FROM table1
ORDER BY dif DESC
but the two columns can be NULL. In this case, it returns the error:
BIG …
- 4,531
7
votes
3
answers
45k
views
How to UPDATE from JOIN with GROUP BY
In a SELECT query of
SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0
How can I turn this query to UPDATE as
UPDATE b.number = …
- 4,531
-1
votes
2
answers
551
views
A quick way to ORDER all IDs randomly in MySQL [closed]
Can you think of an approach to do query faster? …
- 4,531
1
vote
0
answers
49
views
JOINing two mapping tables
ProjectDevelopers (DeveloperID,ProjectID) values
(1,1), (2,1), (3,1), (1,2), (2,3), (5,4), (6,5)
INSERT INTO SimilarProjects (ProjectID,SimilarProjectID) values
(1,2), (1,3), (1,4), (1,5)
In the above query …
- 4,531