Pull Similar Data From Two Tables
I need a query to search two seperate tables for similar data and return them in one result.
I know you can do multiple select statements in one query but I dont know how to exactly organize them into one result. Does anyone know how to accomplish this.
View Complete Forum Thread with Replies
Related Forum Messages:
How To Pull Data From Multiple Tables
I have a db, with several tables in it, I have now reached to the point where I need to pull data from several tables. What is the best way to do this? Should I put a column in each table, and then insert a unique key in all of them at the time the record(row) is being created? If this is the way to do it, when I am putting the unique key in each table would I just do an insert in each of the tables?
View Replies !
Pull Data From Multiple Tables
Can you pull data from 2 tables in the same database onto the same page? What about more than 2 tables? Here is my dilema: I currently have a database with 1 table, and a ton of rows. The information is property information, and each property has multiple empty suites. As of now, I have columns for Building Name, Address, and then columns for Unit Type A (for the first unit), Rent A, Type B, Rent B, etc.... I was wondering if I would be able to put the listings we have into thier own property table, and then one bigger table of all the properties individual information. Keep in mind I need to call all tables together on my portfolio pages, so I would be connecting to quite a few tables at once. Or my other option I was thinking about was to just have 2 tables: 1 Buildings info, and 1 units info. Which of these would you do, or how would you organize this data.
View Replies !
Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site. Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table. The common column to both tables is the 'taskid' column. What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?
View Replies !
Querying Two Tables With Similar Data
I have two tables in my database "bannerclicks" and "bannerviews". They list the times a banner is clicked or viewed, respectively. They contain similar data, namely: bannerclicks -------------- id INT clickdate DATE bannerid INT bannerviews -------------- id INT viewdate DATE bannerid INT What would my query be if I wanted to display the months in which ANY activity took place (either a banner was clicked, or a banner viewed)? I've been playing around with queries like this: SELECT DISTINCT MONTH(bannerclicks.clickdate) AS clickmonth, MONTH(bannerviews.viewdate) AS viewmonth FROM bannerclicks, bannerviews WHERE bannerclicks.bannerid=2 AND bannerviews.bannerid=2 ... but it was all no good. In the end I decided to create one table "Banneractivity" with an additional "action" field to contain either "view" or "click". (in retrospect this is a much cleaner approach anyway) But I'm still curious, how would I solve this problem in the future?
View Replies !
Retrieve Data Accross Multiple Similar Tables
I have a number of tables that all have the same table format. I want to query accross all the tables as if they where one large table. I am looking to retrieve data base on one of the columns like: select * from (data_readings.lts4a_line,data_readings.lts12_line,data_readings.lts14_line) where trigger_type='time of day' the error I get is 'column "trigger_type" in where clause is ambiguous' is there a way to do all this in one statment?
View Replies !
Merge Sort-of Similar Data From 2 Tables In Query
A bit of background. I'm not a web person, and I know enough about databases to scrape by. Most of my SQL is generated using templates or handy things (like PHPMyAdmin etc). I'm also not a PHP coder. I am a C/C++ coder so I've found I can hack some PHP together, and I've got roped into helping a friend with a problem Here's the issue. I have a MySQL database with 2 tables of interest. Let's call these tables categorydata and itemdata. Both of these tables contain records that describe individual items - the item name, its ID in the system, the date it was added, some free text about it etc. To be absolutely clear, the purpose of the categorydata table is for a record to define a category (think "puzzles", "board games", "vegetables", whatever), but the record row also includes data on the FIRST item in the category ("banana", "cluedo" etc) along with all its data. The purpose of the itemdata table is to list all the OTHER items in the category ("grapes", "twister" etc). Here's an example record in the categorydata table: categoryname vegetables categoryid 1234 categorycreated 10th Jan 2000 categoryowner Bob firstitemid 4567 firstitemname cucumber firstitemcreated 12th Jan 2000 firstitemdesc It's long and green Here's an example record in the itemdata table: itemid 4568 itemname orange itemcreated 13th Jan 2000 itemdesc It's round and, er, yeah. This seems really bad design to me but what do I know. Here's what I need to do. I need to know how to merge the data in these 2 tables in a single query, ordered by the itemid and firstitemid fields. In other words, I need the query to merge the data, ordering it so that regardless of the table the record came from the itemids are all in correct numerical order. I need to do this so that I can retrieve each item's data in order. itemid in the itemsdata table and firstitemid in the categorydata table are unique, so when merged there won't be any colliding ids. I need to put this in a PHP script (in a loop that dumps out all the items in order), but if someone can just help me with the SQL query syntax I'd appreciate it, I can do the rest. I think this has something to do with inner joins but I really don't understand how to make it work, or how to retrieve and merge data from 2 tables at once bearing in mind the fields (columns? what do you call them) have different names even though they have the same purpose (ie: firstitemid -v- itemid, firstitemname -v- itemname etc).
View Replies !
Finding Similar Tables
I have developed a site where I can add client profiles with a small amount of information including the project and company info. Each group of company and project information is saved to it's own table with the company name as the table name. I am wondering if there is a way for the code to be able to provide you with the closest existing project in the database. For example if I create a new project with a company that has 50-300 employees (drop down variable), I would like the script to be able to show any other projects with that same characteristic.
View Replies !
Distinct Field From Two Similar Tables
I have two 'similar' tables a) open_works_table b) closed_works_table now, closed_works_table have 2 extra fields compared with open_works_table all other fields are same(name and type). The reason for two similar tables is because open works is quite used and contain only few rows compared to the huge number of closed works(rarely used). I want to get the DISTINCT list of all job_types(a field in both table) from the two tables. How to get it? these two tables are not related, just similar. I think, I can just pass a query SELECT DISTINCT(job_type) FROM {concatenated openworkstable and closedworks table} But how to join two tables one below the other? or is there is some other solution?
View Replies !
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 !
Comparing Two Tables :: Find Similar Values
I was wondering if there was a SQL command I could use that would find similar values from two specific columns in two different tables.. possibly an intersect? heres what I mean Table 1 blogTitle="I love soccer" Table 2 articleTitle="Soccer is fun" Is there a SQL command that would be able to find the similarities between each column in each different table if, lets say, the user entered "soccer" as a query?
View Replies !
Joining Similar Tables, With NULL At Both Sides
I'm looking for a way to join similar tables. Some columns have the same meaning and should appear as one column in the result, other columns don't and should contain NULL values when they are not applicable. I made a fictional example to make it clear. I have a table 'cars' and a 'bikes', which I would like to join: ......
View Replies !
Pull All Records
if it were possible to write a statement in SQL where one could select several fields from a table and then limit the records returned by only displaying those where, say, FieldA (a varchar) contains a given short string which could be only a part of the field's contents. As long as a record has this short string in Field A, it should be included in the resulting set of records.Something like this: SELECT FieldA, FieldB, FieldC FROM TABLE Table1 WHERE FieldA *contains string* Is there anything in SQL that could examine the contents of a field for a partial string, and then return the records that have that partial string in that field?
View Replies !
Multiple DB Pull
Lets say there is a database of special users and a database of favorite movies SPECIAL USERS (username) bob jim FAVORITES (username - movie_title) bob - movie 1 kim - movie 3 jim - movie 1 larry - movie 2 jane - movie 2 How would I write a query that lists the most popular movies of special users without explicitly doing: SELECT * FROM FAVORITES WHERE username = 'bob' or username = 'jim' GROUP BY movie_title I want something like this: SELECT * FROM FAVORITES WHERE username = [all the usernames from the SPECIAL USERS DB] GROUP BY movie_title
View Replies !
Mysql Pull
I got this in my mysql databe table. a:1:{i:0;O:19:"test_Enclosure":4:{s:4:"link";s:36:"http://grabber/v/CAcpLiw4qt4.swf";s:4:"type";s:29:"application/x-shockwave-flash";s:6:"length";N;s:10:"javascript";s:2:"js";}} I want to pull out only the http://grabber/v/CAcpLiw4qt4.swf part?
View Replies !
Can Input Japanese, Can't Pull It Out
I figured out how to store Japanese characters by setting the collation of a particular field to utf8_bin. I can input Japanese characters, and I can see them just fine under PHPMyAdmin. The problem is that when I execute a query and print them to a web page, all I get is question marks instead of Japanese characters. This is while using the same browser that allows PHPmyAdmin to display the characters correctly. I added the following metadata to the <head> portion of the document but it didn't help: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
View Replies !
Populate A Pull Down Menu From Db
how do I populate a pull down menu from a MySQL table? Using PHP/MySQL 4.0 Have table1 with names and ID and tableb with ID and statistical info.wnt to be able to scroll names and have users able to click on the name to follow a link to a page created using tableb data for each name listed. Table examples: tablea ID l Name --------------------- 1 Person, Joe 2 Smith, Joe tableb ID l units l margin -------------------------------- 1 5023 123 2 2536 72
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Pull Hosting Plans
I am currently creating a web hosting website for a client of mine, for the packages and the order process we are using a script called "Auto-Host (http://www.idevspot.com/AutoHost.php)", which stores all the hosting plans into a mysql database. This is a great script and it does exactly what we want it too, the only thing missing is it doesnt create a page where people can view the hosting plans and click a sign-up button. Can someone please give me guidance on how i can get a page with all the hosting plans on please?, alternatively i am willing to pay somebody for doing this on my behalf
View Replies !
Pull The Last 10 Records From A Transactions From A Table
I'm trying to pull the last 10 records from a transactions from a table using this query: SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10 But I want to display the rows in ascending order by timestamp. I can't get the subquery below to work and not sure why: SELECT * FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) ORDER BY timestamp ASC;
View Replies !
Pull Related Info Using Subqueries
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are my tables: movies directors comments movies_directors movies_comments etc... (primary key is movies.id) But here's where I'm stuck: for each film, I need to pull the movie info in 'movies', plus any related data from other tables, like this: The Lord of the Rings | Peter Jackson | 3 comments Episode II | George Lucas | 0 comments Indiana Jones | Steven Spielberg | 15 comments I seem to run into problems when I try to join info from all of these related tables. If there's a match, great. But, if not (like a movie with no comments), the movie is excluded from the result set. I've tried all sorts of SQL queries to make this work, but nothing seems to do the trick. SO, HERE'S MY QUESTION, IS THERE ANY WAY TO DO THIS QUERY WITHOUT SUBQUERIES/MYSQL 4.0? Or would the best approach be to use PHP to do all the subquery lookups?
View Replies !
Result Pull And Rating Sort
I have two databases. What I would like to happen is when I have a summary of products, I would like the query to pull the info from the product table and based on the id from that table cross reference with the reviews for that particular product and calculates all the reviews for that product into a single percentage. So when I access the assoc array from PHP I would like to see : Array(Array(['id'] => '2', ['prod_name'] => 'gummy bears', ['rating'] => '72'), Array(['id'] => '57', ['prod_name'] => 'socks', ['rating'] => '52')).
View Replies !
Regular Expression :: Pull 3 Or 4 Digit Numbers
I would like to pull all the model number from my products table that are strictly only a 3 or 4 digit number ie: 034 or 3542 or 0243 or 934 etc Can someone advise me on how to construct a regular expression to give me the data I need?I have tried the following but it wasnt 100% accurate: Code: products_model REGEXP "[[:<:]][0-9][0-9][0-9][0-9][[:>:]]" or products_model REGEXP "[[:<:]][0-9][0-9][0-9][[:>:]]"
View Replies !
Pull Info From One Database To Save In A Session To Push To A Different DB
I'm building a dynamic site that has 3 data bases. One DB contains all of my store info ID#, Name, Address, Phone. Another DB contains member info ID, Name, address, email,phone,etc... The 3rd DB Tracks when a user logs in and logs out at a store. This DB has the following columns Store ID, Member ID, log in time,log out time, time in store. The stores log in in the morning that starts a session-I need the session to pull the store's info ID#, Name,etc... so when a user logs in, in the store. The login page inserts the store ID, Store name, and user ID into the 3rd DB. There are a lot of stores how do I get the store variables to be inserted into the DB?
View Replies !
Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being PHP SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT FROM grps RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid) GROUP BY grps_c.catid ORDER BY grps_c.title which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add PHP LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ') however adding that removes the rows that have a 'count' or NULL or Zero.
View Replies !
Similar To IN
okay so like, if i do blah blah blah IN ('x','y','z') more blah i get results if any of the 3 values x,y or z match, right? so is there an easier way to return data from somewhere, only when all 3 match, sort of like that IN statement, like... blah blah blah ONLY IN ('x','y','z') more blah ? or do i have to do all 3 individually like where blah = blah AND blah = blah AND blah = blah ?
View Replies !
Most Similar
I am working on a PHP script, and I want it so it searches a database for something (such as "MySQL is very fun to use"). Now I want it so that if there was no result called "MySQL is very fun to use", then it searches for the most similar entry (such as "MySQL is fun to use" or MySQL is easy to use".) Is that at all possible, and if it is, may somebody explain it to me, or point me to a place that does? Also I understand basic MySQL (how to do searches, and other simple stuff like that). Also if this isn't possible, is there any way to reverse wild card (wild card the results instead of the search). This would mean that if you searched for "MySQL is fun and easy to use", then "MySQL is fun" could show up.
View Replies !
Similar Entries
What is the best way to find similar entries? If I have some data like "What processor company is Apple going to be using in the future? Intel." I then want to be able to scan other entries with other data and find entries that are similar. One way would be to take words greater than 3 letters and then try to find those, but I'm not quite sure how to do it.
View Replies !
Similar Names
I'm trying to retrieve a list of similar customer names from a database table (e.g. Inca, Inca Ltd and Inca 12 etc). I've created a new table called 'dups' with all 20000 cust names but with the last 4 characters removed from each. I've tried the statement below but obviously this isn't correct. Anyone with any ideas? SELECT c.cust_name FROM customer c, dups d WHERE c.cust_name LIKE "(d.cust_name)%";
View Replies !
Two Similar Fields One Table
I am trying to figure how to make a single select query in one table between two fields to see if they are similiar. I need the second one to have a wildcard. I can easily do this with no wildcard to see if they are the same but I can't get anything to work when it should be LIKE. Example: Table: test Field1 = "testing 123" Field2="testing" I need something that selects that row because the word "testing" is in both.
View Replies !
Count Columns With Similar Name
I have a table that has several columns named media1, media2, etc all the way to media10. What I'm trying to do is count how many of those media* columns in a specific row defined by a primary key (tutID) aren't NULL and return the integer.
View Replies !
Query For SIMILAR Terms
does anyone know wether there exists a mySQL syntax that is about the same as e.g. metaphone() or soundex() is in php?So to say that one does a query that gives back all entries that are SIMILAR only? I mean I know the "LIKE" statement, but that gives only back the entries that consist somewhere in the entry of the query word, but I would need more overall-results. Does there exist something else except LIKE - statement?
View Replies !
How To Set Similar Character Set In Ad And Mysql
I am connecting users from AD to Sun Identity Manager (IDM), and the IDM uses MySQL. The strange thing is that Norwegian characters keep showing up as ? or other strange characters like a Y with double line over or A with ~ on top and some other character next to it... We have set the character set to utf8 and collation to utf8_bin, but we get the same strange characters as we are when running latin1. AD is running a character set called ISO-10646 (similar til unicode), and our theory is that it might be a conflict with the character sets between the different resources. Anyone had similiar problems?
View Replies !
Search: Select Similar, Not Like
I want to make a search feature to the content of my website. I suppose, the classic way to do that is by making a form where the typed value $typed becomes a part of a select statement like this: select from mytable where field like '%$typed%' I would like to make a statement which allows for e.g. mis-spelling or different forms of a word. Say that the user searches for "woman" - and there happens to be content containing the word "women" instead ... Is there perhaps also a way of ordering the results by similarity? E.g.: select from mytable where field like '%$typed%' order by similarity
View Replies !
Similar To SQLyog For MySQL On MAC OS X 10.3
I've used SQLyog and MySQL a whole lot on Windows platforms, but now I'm trying to do the same on the Mac for a little demonstration. where I should look for some free software which provides the same stuff as SQLyog (GUI-driven table and data manaagement)?
View Replies !
Searching Similar Keywords
I have an interface for this table setup to allow people to search the table based on several fields. My question is this, I can find exact matches of, for example, last name. But how can I return a partial match? If I search for "smit" I would like it to return "smith" as a match. Also, if it's possible, "smtih" would match "smith." Is this possible? I've tried googling and searching here for something like this, but feel that I'm not entering the right words to pull up what I want.
View Replies !
Count Similar Rows
I have a table where every search made on a site is logged. The table columns are id, text, uid, time and ip, text being the string of text that was searched. I need to take the text column and somehow conclude what the most popular searches are. Does anyone know an efficient way to do this? Obviously, I could just select all the rows and use php to figure out the most popular searches, but this doesn't seem very efficient.
View Replies !
Showing Similar Items Only
My db is full of books and is community-driven. I have the book title, the author, publisher, etc.. Because it's community-driven, there is a bit of duplicate data. However, it's not an *exact* duplicate, just similar. For instance, the book titles may be the same, but the author is misspelled, or the publishing date is incorrect. I'm looking for a way to display and group similar book titles together so they can be compared by an administrator. It would be easy enough using LIKE, but I want this list to be automatic. No searching involved, because, well, they won't know what to search for. Just click on a "show duplicates' link and presto! Instant list of duplicates.
View Replies !
Duplicate/Similar Rows Detection
so we are scraping multiple sites to get information about events. And sometimes multiple sites contain the same info. Obviously, the system takes it thinking its not duplicate because its not really an exact duplicate. Example below. row 1: Roger Waters, Air Canada Center, Toronto, ON row 2: An Evening with Roger Waters, Air Canada, Toronto, ON Is there a way in MySQL to go through all the rows and find similar rows so to speak, Can I use full text indexing somehow to figure out a percentage match on the rows and create a threshold( if % match on 2 rows is greater than 90, its considered a duplicate)?
View Replies !
ROWID, Identity Or Similar Id In A Select
I already have an AUTO INCREMENT column for my table but I want to do subselects for subsets from the table and generate a unique sequential id for that subselcted result. e..g select ID(identity,1,1), col1, col2 from table1 where etc. Is there a good way to accomplish this in MySQL 5.1
View Replies !
INSERT And UPDATE Similar Statements?
At least as I know it, INSERT goes like: INSERT INTO table (Col1, Col2) VALUES('Hello', 'Goodbye') While UPDATE goes like: UPDATE table SET Col1 = 'Hello', Col2 = 'Goodbye' Is there any way I could make them the same format, such as: UPDATE table (Col1, Col2) VALUES('Hello', 'Goodbye') ?
View Replies !
|
TRACKER
