MYSQL :: Merging Rows?

Merging Rows Within The Same Table
Got this trouble that worries me a lot:
Say I have tables: "users" and "userDetails".

table "users":

Code:

ID --- userName
1 xxx
2 yyy

table "userDetails":

Code:

ID --- dept --- city --- date
1 1 NULL NULL
1 NULL 2 NULL
1 NULL NULL 3

Suppose the entries for the second table are entered at different time spans, or by different people.
I want to join this tables together and (ON ID), so that it fills the null values in the second table somehow merging them or by some special technique which is hidden to me
Running extra code (either PHP or MySQL) to sqeeze (alter) the second table and select afterwards is not acceptable. I'd like a single SELECT query which would do it, while keeping the original table entries.

Desired selection result:

Code:

ID --- userName --- dept --- city --- date
1 xxx 1 2 3

It would also be nice to learn whether I can select among the non-null values in case they match according to some criteria (like ORDER BY)... In this case there would be another non-null field in the second table (e.g. entryID), and would sort in a descending order according to it.

View Replies ! View Related

Stop UNION Merging Identical Rows
The first query returns 0 rows.

The second query returns 2 identical rows.

When the two queries get unioned together, the duplicated data is ignored.

View Replies ! View Related

Dumpfile From Mysqldump Is Merging Rows For Multiple Inserts.
I am able to use mysqldump to view all the individual insertions of a table on an individual row. I have seen this occur before. Below is the desired format:

INSERT INTO `database` VALUES (1,...,...);
INSERT INTO `database` VALUES (2,...,...);
INSERT INTO `database` VALUES (3,...,...);

My dump producing the following instead:

INSERT INTO `database` VALUES (1,...,...),(2,...,...),(3,...,...);

Is there a way that I can configure the dump?

View Replies ! View Related

The Merging
i want to know about of the data bases merging ,

that i have 2 SQL databases from my forums

i want to merg the old database with the new one ....

without any lose of the users or Topices or ,,,,etc.

is there any way to do so, ?

is there any Script can do it quikly ?

View Replies ! View Related

Merging Two Querys
I have two query that I I would like to be one, but I can't figure out
how to do it. (mysql 3.23)

$sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
login='%s' group by login",$usuario);

$sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
AND month='%s' group by login",$usuario,$getdate);

View Replies ! View Related

Merging Databases
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA, but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?

View Replies ! View Related

Sorting And Merging
I have a table in the following style :

Artnr Detail
----- ------
110391 grey and
100391 blue color
100392 Green
100393 black
100393 monitor
....

So sometimes one article has more details. My problem is, i have to
get rid of that and sort the table in this way :

Artnr Detail
----- ------
110391 grey and blue color
100392 Green
100393 black monitor

Is there a way to manage this with mysql or does it need php
scripting.

View Replies ! View Related

Merging INSERT
in my table I have an `id` column, which is set as the primary key as well as auto_increment. In my application I currently insert a new row into the database and then do a select query to find out what the `id` column of the newly inserted row is.
Would it be possible to merge these to queries into one, so I could insert the row and then query the `id` column all in one statement

View Replies ! View Related

Merging Two Db Fields
I have two different databases, and I need to merge two fields into one fields. I have fld_user_name in db1 and fld_userlogin. Is there anyway that I can do this with out having to reenter each user.

View Replies ! View Related

Merging 2 Tables
Alright, i'm have this database where it has 7 tables. All of these tables have identical columns. However it contains information of different categories. The reason for that is to reduce the mess of having all in one tables.

For one of my process, i need to combine all 7 tables together. Now, i have been searching high and low for a non-destructive method whereby i can stack all the tables' information into just one table. But what i see are things like JOIN that i can't use as i'm not joining tables through columns contain datas of the same domains. Code:

View Replies ! View Related

Merging Two Databases
I am working with 2 mysql databases for FamilyConnection CMS for my little family website.

First database has 15 tables that's from an older version of fcms and the second database for a recent fcms version, has the 15 original plus 5 more new tables.

How do I add the data in my old database into the new database?

View Replies ! View Related

Merging Two Tables
I have 2 tables.

table: test1
+-----+-----------+------------+------------+
| id | client_id | contact_id | strings |
+-----+-----------+------------+------------+
| 1 | 55 | 3 | A |
| 2 | 66 | 9 | B |
| 3 | 55 | 0 | C |
| 4 | 77 | 0 | D |
+-----+-----------+------------+------------+

table: test2
+-----+-----------+-----------+
| id | client_id | strings |
+-----+-----------+-----------+
| 3 | 55 | one |
| 20 | 55 | two |
| 9 | 77 | three |
| 11 | 33 | four |
+-----+-----------+-----------+

test1.contact_id is test2.id index
I need to get all one client rows from both tables. Is it possible? I have tried to use join with these tables, but it always leaves some rows out from result.
Lets take for example needed results for client 55
+-----------+-----------------+---------------+
| client_id | test1.strings | test2.strings |
+-----------+-----------------+---------------+
| 55 | A | one |
| 55 | C | NULL |
| 55 | NULL | two |
+-----------+-----------------+---------------+

View Replies ! View Related

Merging Data
I have two databases with identical structure, and I am wanting to merge the data from one table in one database with the data from the same table in the second database. I have the following code, and it seems to run without any errors but the data is not merged. Can anyone help me?

(SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank, level, theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok FROM first_database.xoops_users)

(SELECT name, uname, email, url, user_avatar, user_regdate, user_icq, user_from, user_sig, user_viewemail, actkey, user_aim, user_yim, user_msnm, pass, posts, attachsig, rank, level, theme, timezone_offset, last_login, umode, uorder, notify_method, notify_mode, user_occ, bio, user_intrest, user_mailok FROM second_database.xoops_users)

View Replies ! View Related

Merging Row Field To Another - Is It Possible
If possible I need to merge a row's field's data to an another row's field's data if possible.

Here is an example of the rows :

I need to, if possible when I do my .csv dump to append the order_item_id 3178's order_item_item_id to 3177's order_item_item_id . Is this possible using an advanced query?

View Replies ! View Related

Merging Like Fields
I have 2 tables that I would like to Join. Each table has a Date/Time stamp and logged mysqleratures from a specific room. I would like to Join these tables, but only have one Date/Time Table. This will be a full Join, sometimes one table will have a date/time, sometimes both will.

View Replies ! View Related

Joining/Merging Two Tables Into One?
INSERT INTO test select testing.products_id,testing.products_percase,testing2.products_name,
testing.products_weight,
testing.products_model,
testing.products_price,
testing.products_quantity,
testing2.products_description,
where testing.products_id = testing2.products_id;

This is what I have so far. Basically, I am trying to create one table with the values listed here from the two tables "testing" and "testing2". It says it is stopping near the where clause; does anyone know how to fix this issue so I can merge the two tables?

View Replies ! View Related

Question About Merging Big Tables?
In my applications, I need to merge three big tables(with different structures) into one big table, mySQL is:

=============================
DROP TABLE IF EXISTS `my_final_table`;
CREATE TABLE `my_final_table` (
...
UNIQUE KEY (`field1`,`field2`,`field3`,`source`),
);

INSERT INTO `my_final_table` SELECT ***** FROM `table1`;
REPLACE INTO `my_final_table` SELECT ***** FROM `table2`;
REPLACE INTO `my_final_table` SELECT ***** FROM `table3`;
==============================

View Replies ! View Related

Tool For Merging Tables
I want to merge these tables into one big table and have the original tables replaced with views. Of course I can hand hack this, but I would rather have a tool do the work for me. Is there a tool that can do this and if yes where can I get it? The tool must take care of table data, foreign keys, datatypes and allow me to select columns that are the same.

View Replies ! View Related

Merging Remote Db Into A Central Db
I'm wondering if there is native support for merging data automatically (periodically) from multiple identical mysql databases into a central database.

View Replies ! View Related

Merging Data From A Many-to-many Relationship
I have three tables in my database:
books {book_id*, title}
authors {author_id*, name}
book_author {book_id*, author_id*}

The book_author table joins books and authors in a many-to-many realtionship. I join the tables with the following MySQL statement:

SELECT book.title, author.name
FROM book, book_author, author
WHERE book.book_id = book_author.book_id
AND book_author.author_id = author.author_id;
The problem I have is that if a book has more than one author, the title appears multiple times for each instance of author as a seperate row in the table. eg.

PHP & MySQL Web Development Luke Welling
PHP & MySQL Web Development Laura Thompson
Is there anyway to have the single title with multiple authors? When I

GROUP BY book.title
only one author is shown. eg.

PHP & MySQL Web Development Luke Welling
What I would prefer is something like this:

PHP & MySQL Web Development Luke Welling, Laura Thompson

View Replies ! View Related

Merging Database Tables
I need advice on how to merge database tables. I have 3 tables that I want to compress into 1 table. All tables have the same columns, but different data within. Also, all tables have an auto-incremented primary key, so I need the id's to be changed, I guess - I'm not really sure about how that would be handled.

If anyone can point me in the right direction here, please do.

View Replies ! View Related

Merging Two Fields In A Table!
I have used a join to bring two tables together, which works fine, but what I now have is 2 fields with dates in which I would like to order by, and at no point are these two dates overlapping, is there a way I can merge the two together, or order by both so as they overlap. My current SQL is:

SELECT * FROM tblblog
LEFT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

UNION

SELECT * FROM tblblog
RIGHT JOIN tblfilmreview
ON tblblog.blog_date = tblfilmreview.date_watched

ORDER BY blog_date DESC, date_watched DESC
Thankyou for any advice you may be able to give
Dan Duke

View Replies ! View Related

Merging Multiple SELECT Queries
I have two tables, one storing the info of the users ( one user in each record ) and the other the ids of several users in a single record i.e. (4,2,9,6), (4,2,8,3) etc.

For convenience sake, i'll call it table1 and table2.

What is need is to retrieve the records from table two and from the user ids, get all the users first name from table1.

My problem is that i want to keep it within one query. I have tried looking up union and joins but they don't seem to meet my needs.

What i would like to achieve is maybe (user1_first_name, user1, user2_first_name, user2 etc.)

Is this possible at all?

View Replies ! View Related

Merging The Results Of Multiple Tables
I have two tables. The first table is "users", the second table is "songs". The idea is that each user (one record in the users table) can upload several songs (several records in the songs table).

I have it so that each new Song added, has a foreign key that points to a user record. So dozens of Songs could all point to the same user.

My question is, I want to run a query that returns information about a user, including all their songs. So, the return table would look like:

username alias songname1 songname2 songname3
username alias songname1 songname2
username alias songname1 songname2 songname3 songname4

etc.. The songnames come from the SONGS table, and username/alias come from the USERS table, but the final result should be one table. What I've managed to get working is returning all the songs of a user, but as different rows in the table:

username alias songname
Bob bob Happy Song
Bob bob Sad Song
Bob bob Super Song

But I really want that to be returned as:

username alias songname1 songname2 songname3
Bob bob Happy Song Sad Song Super Song

So I want to merge n number of sub records, into a single user record, and return the results as 1 per user. How do I perform this feat?

View Replies ! View Related

Merging Two Tables With Diff Structures
I had installed on my phpBB3 Forum a mod to see what users viewed each topic, but then it started to not work properly with my other mods, so I found another one and installed it.

But what happens is I want to put all entries that the first one had into the second one, but the thing is they have different table structures. One has one entry for every time the user viewed a topic, and the other has a view_count field, where it sums +1 every time the user visits the same topic again.

The structures are:

Old one:
topic_id, user_id, date

New one:
topic_id, user_id, date, view_count

Does anyone know how I could make a script in MySQL to go through the old table, and for each topic_id and user_id that are the same, make only ONE row and also count how many times forum_id and user_id are the same and throw them in the view_count field, and also for the date field, make it get always the date of the latest entry.

View Replies ! View Related

Forum Database Merging Question
I am very new to databases although I have backed up, and imported several forum db's using BigDump. That's as far as my knowledge goes i'm afraid.

I have two similar smf forum dumps which I would like to merge into one database. If you imagine and old forum added to a new forum, or two different forums that decide to merge all their boards, posts and members into one.

I have spent some time now reading up on sql, searching for scripts and creating databases in an attempt to get this to work. As I have said, I am a real novice at this kind of thing and would really appreciate some help.

Basically a script would be a dream come true, but I have yet to find one. I am prepaired to read, learn and experiment if somebody could just point me in the right direction. Lastly, if this has been achieved before, a link to a guide or howto would be amazing.

View Replies ! View Related

Help Needed Merging 2 Different But Similar Tables
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there?

What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are:

id - title- body

IN other words, just the bare essentials.

The target files has a table called jos_content with columns:
id - title - title_alias - introtext - fulltext in that order and others but are not important.

I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time!

It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.

View Replies ! View Related

Merging Unique Emails From 2 Tables
I have Table 1 and Table2 both having unique emails within them.

Table 1 being the main email Container, I want INTERSECTION of Table 1 and Table2 and the UNIQUE emails between the two should be moved to Table 1.

So now that Table 1 should have All the Unique Emails within itself and also between Table 1 and Table2

ex

Table 1
A
B
C
D

Table 2
F
G
I
B
C

So finally Table 1 should have
A
B
C
D
F
G
I

Note: values B and C already exist in Table 1 and should be ignored.

How to write this query?

View Replies ! View Related

Merging Commands And Trying To Do A Selective Table Merge
Ok, the first problem is this. I have a large section of SQL commands to do, and I wish to know if there's a more efficient solution to do this.

Background: I am working with multiple databases, each one is the same general format, but the data is dissimilar. It's incorrect in varying ways. The code below is intended to iron out some of those issues by pulling in fresh data from a second DB that is more accurate regarding aspect of the database as a whole.

[MYSQL]DELETE FROM vendors WHERE entry=30239;
INSERT INTO vendors SELECT * FROM ncdb_world.vendors WHERE entry=30239;[/MYSQL]

The second issue is more complicated. I need a script that goes through the given table (in this case creature_proto) and selectively pulls in fresh data from another table when mindamage and maxdamage are both 1 or are both 0. The workaround I'm doing right now is manually looking at each entry in a table 36k entries long, and repeating the process every time there's a mass update. The code I'm using when I find a bad line is:

[MYSQL]UPDATE creature_proto SET mindamage=11, maxdamage=14.19 WHERE entry=1721;[/MYSQL]

Any assistance on these matters would be greatly appreciated. Thank you.

- Aaron, Head Developer of MagicWoW

Edit: Forgot to mention, I'm using MySQL 5.0.51b (comes standard with the version wampserver I'm using for MagicWoW)

View Replies ! View Related

Merging Data On Remote MySql Server
I've been tasked with installing the table structure from the master database onto a remote MySQL server. The remote server will accept data and periodically, merge/dump the data to the master server.
I need help with the merge/dump issue. This doesn't sound like a replication (where two database servers are made to look identical) issue but 'merge' doesn't soud right either.

View Replies ! View Related

Tutorial On Back Up & Merging Tables And Database
I just want to make sure I don't end up throwing away some valuable data as I learn mySQL.

1. What is the best way to backup data? (copy the .frm files from the folder they are stored in? a backup function? other?)

2. How can I merge databases? (Basically I have two databases that are identical in format - same tables, same columns, but have different data in them) I'll be doing this on a regular basis - as during the week I'm collecting data in two locations and they syncing it up on the weekend. And the nature of it doesn't allow me to do everything at once.

3. Any suggestions on good books/resources to learn mySQL?

View Replies ! View Related

Merging Tables With Possibly Identical Data
Im looking to merge two tables in my db (or more specifically, insert one's values into the other). This post is pretty helpful for how to set up the merge, but my problem is my two tables will possibly contain identical data - and i dont want duplicate records

Let me explain in more detail - table A contains spots which may be interesting for travellers in a given city. table B has the same data, but from a different source. Both will have the same columns (id, name, subtitle, cat_id, loc_id, address, added, by), but its the data IN the tables that might be identical.

As i said before, i dont want duplicate records, so if the name of the listing is the same as one thats already already in the original table, how can i make it so it isnt inserted?

If it helps, i already have a script which uses fuzzy string matching (written in php) using levenstein and metaphone methods. i dont know if its practical at all, but is there maybe a way we can use that to check each item in table B against the records already in table A before its inserted?

View Replies ! View Related

Error In Table Definition :: Merging Tables
I am looking towards merging multiple table sets (one per user) to a single set of tables with an extra field in each table for identifying the user a particular record belongs to.

I'd prefer to make the abstraction layer on the database side to keep the changes in the application minimal. For that purpose, I am going to use triggers and create a set of views for each user.

As I understand, all indexes should be prepended by user_id field, so instead of:

create table username_posts
( id integer not null auto_increment
, title varchar(32)
, contents text
, primary key (id)
, key (title)
);
I would have to use:

create table global_posts
( id integer not null auto_increment
, title varchar(32)
, contents text
, user_id integer not null
, primary key (user_id, id)
, foreign key (user_id) references users(id)
, key (user_id, title)
);

But I get "Error in table definition", as I am using InnoDB and it requires auto_increment column to be first in the list. I was thinking about using trigger instead of auto_increment but am not sure if that's the best way.

View Replies ! View Related

Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table:

Column1 | Column2 | Column3
===========================
NULL |"Value1" | NULL
---------------------------
"Value2"| NULL | NULL
---------------------------
NULL | NULL |"Value3"

In each row only one column contains an actual value (a string in our
example), the other columns are set to NULL.

I would like to merge those columns into one single column, discarding
the NULL entries.

The desired result would look like this:

Merged_Column
=============
"Value1"
-------------
"Value2"
-------------
"Value3"

A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't
work unfortunately. Is there an easy way to accomplish this on the SQL
Server?

The next problem I am going to face is that every column is in fact
made of a different data type (ie. Date, Int). Is there a way to
modify the merge select (if that's possible) to have it cast
everything into a single specific type? Ie. make everything in the
merged column a string.

View Replies ! View Related

Next And Previous Buttons MySQL Rows Displaying 3 Rows Per Page
I have got a table "members" and the DB name is "jutland". I have got columns - id, name, surname & email. I want to display 100 records with 5 rows per page using PHP going back and forth with previous(previous 5 rows) and next (next 5 rows) links. I have the knowledge of the LIMIT x, y condition but still stuck on passing required parameters.

View Replies ! View Related

Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail.

I'm logging slow queries and this one is taking forever, as you can see. What can I do?

# Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....

View Replies ! View Related

Searching For Rows That Depend On Other Rows In Some Fashion
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.
I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.
For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...
Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

View Replies ! View Related

SELECT Rows With Field Different From Previous Rows
I have a table (sens_samples) with mysqlerature sensor samples taken every 5 minutes. Rows are: id (INT), sensor_id (INT), timestamp (DATETIME), mysql(FLOAT) and over_mysql(BOOL).

I want to make a query to get ONLY the rows when over_mysql field changed from "previous" row value. I know that database rows are UNORDERED, but ORDER BY can "place" them in a given order.

My query may be "syntactically" something like: SELECT timestamp, mysql FROM sens_samples WHERE "PREV_ROW".over_mysql <> over_mysql AND sens_id = 1 ORDER BY id;

How can I get it using MySQL?

View Replies ! View Related

Selecting Rows While Using Values From These Rows In The Where Clause
I have a table which includes 3 columns:

id: just an id
name: a name
parent: the id of the row which this row is a child of.

In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case:

SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5'))

However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.

View Replies ! View Related

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

View Replies ! View Related

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

View Replies ! View Related

Compare Rows With Previous Rows
I use MySQL 5.0 and am trying to solve following problem, for which I couldn't get any solution :(
I want to calculate the average time between two events.

PseudoCode:

Whenever action "b" occurs, calculate the time elapsed since the previous action "a" from the same session.

And Finally return me the average elapsed time between the "a"'s and "b"'s.

My table looks like this, simple view:
time(int), sessionId(int), event(varchar)
123450 1 a
123452 1 a
123455 1 b
123458 1 a
123459 1 b
123462 1 a
123465 1 b

The Table is very big, so i cannot use PERL or PHP or so to do the job.

I used a inner-join to compare the table to itself, but couldn't get it to work.

View Replies ! View Related

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

View Replies ! View Related

Count Rows For Each Hour The Last 24 Hours, Including The Hours With Zero Rows
I have a table for logging log-in's with these columns: id, datetime, name.

I want to see all the logins the last 24 hours for a specific name. I want mysql to return one row per hour (24 rows) and how many logins this name has done in these hours.

Example result:

2009-02-13 00:00 2
2009-02-13 01:00 0
2009-02-13 02:00 0
2009-02-13 03:00 1
2009-02-13 04:00 0
2009-02-13 05:00 3
2009-02-13 06:00 5
2009-02-13 07:00 0
etc...
2009-02-13 23:00 7

I made this query: ......

View Replies ! View Related

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

View Replies ! View Related

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

View Replies ! View Related

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

View Replies ! View Related