search across multiple records?

Programming and Web Development Forums - mysql - MySQL RDBMS technical discussions.

Skip to content

search across multiple records?

mysql
Post a reply

    Next

  • 1. how to prep database schema
    Hi I ve a huge database created in MySQL by someone else. now i want to view all the tables and their relationships so tht i can design the queries. Can you plitz tell me how can i view the tables n their realtionships. thanx pallavi
  • 2. Suggestions needed
    Given two tables, I need to insert each record in the first into the second that isn't already in the second, and I need to insert the CURRENT_TIMESTAMP in a specific field in the second if it is not in the first. (in fact, the first table is temporary) If I am not mistaken, the following statement, or something like it, will meet the first requirement. INSERT INTO tbl_2_name (id) SELECT id FROM tbl_1_name WHERE tbl_1_name.id NOT IN (SELECT id FROM tbl_2_name); I am not quite sure how best to structure the statement that will accomplish the second. Perhaps something like: INSERT INTO tbl_2_name (some_time) SELECT NOW() WHERE tbl_2_name.id NOT IN (SELECT id FROM tbl_1_name); But alas, I recall reading somewhere that the statement above isn't supported in MySQL. If that is true of some versions of MySQL, please tell me it is no longer true. :-( Is there a better way? Thanks, Ted
  • 3. date arithmatic
    Hey all. I just started usng mysql, and need a bit of help arriving at the following select date, datefunctionhere from table; DATE | DATE_ADD_FUNCTION_HERE 28/02/2006 | 31/03/2006 31/03/2006 | 30/04/2006 30/04/2006 | 31/05/2006 As you can see from the above pretend sql and result-set, i basically want to arrive (using the date functions) at the end of the month of the following month given a current date. The problem is when you use the date_add() function on 28 february you will only get to the 28th of march instead of 31st of march etc. sorry if this doesnt make sense, but any help would be appreciated. cheers guys Jared
  • 4. Combine veiw with query
    Hi, I have a View which I woulk like to combine with a query VIEW --------- (SELECT `idguest` AS `idguest`, SUM(price) AS Expr1, 0 AS Expr2, 0 AS Expr3 FROM `sale-rooms` WHERE (`idevent` = 2) GROUP BY idguest) UNION (SELECT `idguest` AS `idguest`, 0 AS Expr1, SUM(price) As Expr2, 0 AS Expr3 FROM `sale-meals` WHERE (`idevent` = 2) GROUP BY idguest) UNION (SELECT `idguest` AS `idguest`, 0 AS Expr1, 0 AS Expr2, SUM(price) AS Expr3 FROM `sale-misc` WHERE (`idevent` = 2) GROUP BY idguest) QUERY ---------- SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM FROM <---VIEW---> GROUP BY idguest

search across multiple records?

Postby Geoff » Mon, 12 Mar 2007 00:40:50 GMT 

Hello,

I have an HTML file with <form etc in which the names of composers are
listed. Say Beethoven, Mozart,  etc.

In the MySQL database I have records

composer1     Beethoven
composer1work1            Symphony No.4

composer2     Mozart
composer2work1            Piano Sonata No.2

etc etc.

How can I form the SQL statement so that the search will find
composers and their works in different records?

Any pointers please?!

Cheers,

Geoff
Top

Re: search across multiple records?

Postby Rik » Mon, 12 Mar 2007 01:31:32 GMT 




Set it up like this:

TABLE composers
	composer_id	int
	composer_name	varchar

TABLE works
	work_id		int
	work_name	varchar
	composer_id	int

Or possibly, for allowing several composers for a work, move composer_id  
to a relational table:

TABLE work_composers
	work_id
	composer_id

List composers:
SELECT composer_id,composer_name FROM composers

List works of a particular composer:
SELECT work_id, work,name FROM works WHERE composer_id = <id of the  
composer>

Or, if you went with the relational table:
SELECT wc.work_id, w.work_name
 FROM work_composers wc
JOIN works w
ON w.work_id = wc.work_id
WHERE wc.composer_id = <id of the composer>

-- 
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions:  http://www.**--****.com/
Top

Re: search across multiple records?

Postby Geoff » Mon, 12 Mar 2007 01:37:43 GMT 

On Sat, 10 Mar 2007 17:31:32 +0100, Rik < XXXX@XXXXX.COM >






Food for thought! Thanks Rik.

Cheers

Geoff
Top

Re: search across multiple records?

Postby Geoff » Mon, 12 Mar 2007 02:59:21 GMT 

Rik,

Just thought of another, simpler way,

I have added a field to each record in which I have added the names of
the composers so I can now make a "Like" select on this field and get
all the records for each particular composer.

Seems to work OK - see any problems?!

Cheers

Geoff
Top

Re: search across multiple records?

Postby Rik » Mon, 12 Mar 2007 03:37:24 GMT 




Yes, consistency:

J.S. Bach
Johan Sebastian Bach
JS Bach
Bach
j.s. bach

Seems the same composer to me, not to your current database setup. Look up  
normalisation among others. Also, you idea is not necessarily a 'simpler'  
way. True, you have only ont table, but getting good results from it is  
tricky. What if you want a list of composers and the number of works for  
instance? Would be much simpler, and above all blindingly faster with the  
proposed setup.
-- 
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions:  http://www.**--****.com/
Top

Re: search across multiple records?

Postby Geoff » Mon, 12 Mar 2007 04:41:30 GMT 

On Sat, 10 Mar 2007 19:37:24 +0100, Rik < XXXX@XXXXX.COM >






OK! Had better have a go with your approach.

Cheers

Geoff
Top

Similar Threads:

1.searching across multiple fields 

I have a query designed to search multiple fields for any word that
starts with the characters entered in a search. Each field will
typically contain more than one word. The query takes the form:

SELECT
field1, field2
FROM table1 WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
field2 LIKE "a%") OR (field2 LIKE "% a%"))

Is there a more efficient way to search than this?

Michael

2.Best way to search across multiple tables 

I have a horizontally-partitioned database which contains multiple tables of
the same schema which contain messages. Each table has a FULLTEXT index on
(subject, body). Currently, the search script only allows searching one
table at a time, but I'd like to allow searching of all tables at once, or
at least more than one at once. Is there a good way to allow for this? Would
it involve using a view and/or union?

3.Search Multiple keywords across multiple fields 

Hi,

I'm about halfway through building a search engine using ASP, SQL and
Access.
As part of that search engine I need to search multiple keywords across
multiple fields
So far I've figured out how to split a string into words. From here I could:

Construct an SQL statement of varying length that, using the LIKE and AND
operators. This seems damn hard!

Alternately I could concatenate the multiple fields into a variable or array
then search.

What is best - can anyone provide examples ?

Darren

4.Multiple Inserts across multiple tables 

On 3 June, 15:34, David < XXXX@XXXXX.COM > wrote:
> Hi Guys,
>
> After a bit of advice really.
>
> We have a number of tables in our database, that when a new account is
> created it should insert into all of them (different information) get
> the insert ids from all and the update the first table with all the
> ids (probably not the best way to do it, so if anyone can point me in
> the right direction, feel free).

I would insert into 1 table and then use the ID from that one as the
primary key to use on inserting to all the rest.

And yes, if you want Commit/Syncpoint/Rollback functionality you need
to use InnoDB.

5.Full-text search across multiple tables 

Hello,

We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables.  The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000.  And we can
find no way to implement this as the catalogs that are generated are
table specific.

As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog.  We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used.  Please note that upgrading to SQL Server 2005 is not an
option.

Thanks.

John Fleming
 XXXX@XXXXX.COM

6. Full Text Search across multiple tabels in SQL 2005/2008

7. Full Text Search across multiple tables (Parent-Child)

8. Search across multiple columns

Post a reply

Return to mysql

 

Who is online

Users browsing this forum: No registered users and 33 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group