MYSQL :: Joining/Merging Two Tables Into One?

Merging Tables
After some research on the topic, 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.

Merging Tables
I have a database with various user tables in it. 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.

Merging Tables
I currently have 2 tables both with about 800,000 records.
the tables have the following fields.

Table 1:
ID, Num, Path, Date, ImportDate
Table 2:
Id, Num, Sender, Receiver, Date, ImportDate, Code

In both tables the Num field is the same.
Is there a QUICK way to make a new table as follows:

Table 3:
ID, Num, Path, Date, ImportDate1, ImportDate2, Sender, Receiver, Code

And have all data from Table1 and Table2 put into Table3.

Example:
Table1 Data:
1, 777, C:Windows, 01/01/1901, 01/02/1901

Table2 Data:
2, 777, Fred, John, 03/01/1901, 04/01/1901, CODE

Table3 Data would be:
1, 777, C:Windows, 01/01/1901, 01/02/1901, 04/01/1901, Fred, John, CODE

Merging Tables
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?

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:

Merging Tables
What's the correct syntax to create one single table by merging two, or more, tables with identical structure ?

Merging Two Tables
I am trying to figure out the SQL to take care of this table merge: I have two tables of identical structure (TABLE A & TABLE B) that I would like to merge into a new table (TABLE C). I want identical rows from each table to not show up as two separate rows in the final table (I do not want duplicates).

EX:

TABLE A
WORKER ID----SKILL ID----SKILL RATING
1000------------2001------------3
1001------------2459------------1

TABLE B
WORKER ID----SKILL ID----SKILL RATING
1000------------2001------------3
1003------------2923------------2

TABLE C
WORKER ID----SKILL ID----SKILL RATING
1000------------2001------------3
1003------------2923------------2
1001------------2459------------1

NOTE: the row containing a WORKER ID of 1000 was not duplicated in TABLE C.

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.

Merging Results From Two Tables
I would like to ask for some help fixing this query:

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.

Merging Two Tables With Some Duplicate Data
I have written a perl script that outputs a SQL script. I need to modify the outputted SQL so that if there is a duplicate Company Name, their info dumped somewhere for an audit.

One of my current dumped lines looks like this:

INSERT INTO companies (Company, Profession, Town, County, Postcode, Country, Telephone, Fax, `Email Address`, Website, `Rep Override`) VALUES ('!!!! TEST BUILDER', 'Builder', 'b', '', '', 'England', '01234 555666', '01234 666777', 'me@here.com', '', 'John Smith');

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?

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?

Joining 5 Tables
firstly I am using MySQL 4.0.25.

I have a database storing availability of rooms for an accommodation business.

The following tables are relevant to my question:

room:
- room_id
- room_name
- accom_type
- thumb_id
- room_comments

accom_type:
- type_id
- accom_type (the name)
- desc_short
- features
- max_slept
- num_queen
- num_single
- num_bunks
- main_thumb_url

accom_type_pics:
- image_id
- small_url
- large_url
- caption

calendar_date:
- date_id
- calendar_date
- season_id

room_availability:
- date_id
- room_id
- availability_status
- booking_id

There are 4 accom_type - each with more than 1 room - all in all there are 9 rooms.

Each accom_type has a main_image which has its url stored in the accom_type_pics tbl.

The room_availability is used to store unique pairs of room_id and date_id - and the status of the room for that date (and booking_id if it is booked).

I am attempting to select all of the accom_types that have availability for a certain range and number of guests.

I am just not sure which table I "start" with - I have a fair idea of how to write the query, I am just not sure which order the joins need to be in.

SELECT at.accom_type, atp.small_url
FROM accom_type at
LEFT OUTER JOIN accom_type_pics atp ON
atp.image_id = at.main_thumb_url
LEFT OUTER JOIN

Joining Tables!
i'm making a simple online shop with products that can have any number of attributes..e.g a shirt mite be able to be color: red, blue, yellow, size: medium, large...then a pair of shoes mite be color:white, cream, size, 9, 10, 11 etc.

So far I have the following tables...

products:
id: name : price: description

product_attributes:
id: productid: attributeid

product_attribute_names:
id: name

product_attribute_values:
id: attributenameid: value

so, the product_attributes tables stores what attributes each product has (each product can have any number of attributes), in this table, attributeid points to the id in the product_attribute_names table.
product_attribute_names simply names and identifies each attribute (eg. size of shoe A, colour of t-shirt B).
then product_attribute_values lists all the possible values for each attribute.

Now, i think this is a pretty good, normalised way to store products and attributes, but I'm having difficulty joining the tables in a sensible manner.

The product table is good because when listing products you can just list product names, price and description. But when you click on the product I have a more detailed product page where you can select what attributes you want before you buy. But I'm slightly baffled as to the best way to use JOIN to extract all the attributes. Ideally for each product I'd want to return a table like this:

for product #3762:
attributename : attribute value
color : red
color : blue
size : large
size : small

so i cud access the returned array as $product['color'][0] or $product['color'][1] or $product['size][0] (using php). How could I acheive this using JOINS?

Joining 3 Tables
I am using MySQL 4.0.25.

I have the following tables:

Gallery
gallery_id
gallery_name
gallery_desc
feature_image
archived

Album
album_id
album_name
gallery_id
feature_image
archived

Image
image_id
album_id
sml_url
image_title
image_orientation
...

I am using the sql on a page that displays all of the albums that belong to a selected gallery. I need to select the count of number of images in that album, the album_id, album_name, the url of the feature image, the title of the feature image, and the orientation of that image (wide or high).

I am able to achieve this no probs with the following sql:

SELECT count(i.image_id) as 'image_count', a.album_id,
a.album_name, i.small_url, i.image_title, i.image_orientation
FROM album a
LEFT OUTER JOIN image i ON a.album_id = i.album_id
WHERE a.gallery_id =
1 AND a.feature_image = i.image_id AND a.archived = Ɔ' GROUP by
a.album_id, a.album_name ORDER by a.album_name ASC

I also need to get the name of the gallery that these belong to. I have tried the following sql:

SELECT g.gallery_name, count(i.image_id) as 'image_count', a.album_id,
a.album_name, i.small_url, i.image_title, i.image_orientation FROM album a
LEFT OUTER JOIN image i ON a.album_id = i.album_id AND a.gallery_id = 1
AND a.feature_image = i.image_id AND a.archived = Ɔ'
LEFT OUTER JOIN
gallery g ON a.gallery_id = g.gallery_id GROUP by a.album_id, a.album_name ORDER by a.album_name ASC

which works fine for gallery_id = 1, but when i display any other gallery, it displays the albums that belong to gallery 1..

I think I am not far off here! - just a minor tweak with my 2nd Left join?

Joining Tables
I have two tables (table1, table2) that share common data.

Table1 has 50 distinct entries while table2 has 10 distinct entries. I want to select all the data from table1 that IS NOT in table2. So, if the data is in table2, don't show it.

So I should get 40 results.

Joining Three Tables
I have three tables; USERS, COMMENTS, and IMAGES. USERS is a complete list of all my users with a unique id called USERID which is the primary key. COMMENTS is a list of comments with the USERID of the user who posted them. IMAGES is a list of images with a corresponding USERID for every user who has posted an image. My problem is that I want to join all three tables so that I may display the results of COMMENTS with all the details from USERS and IMAGES. I am joining on USERID, but the problem is that there is not necessarily an image for every user so when I join the tables all the comments for which the users do not have an image are not displayed. Is it possible to join the tables such that MySQL puts in NULL values when it can't find an image in IMAGES?

The command I am currently using is:
SELECT * FROM COMMENTS LEFT JOIN ON (USERS.USERID=COMMENTS.USERID AND USERS.USERID=IMAGES.USERID)

Joining Tables
I'm trying to get the results of a query to be <= users input where two tables are required for getting the results. When I test the query I get every row in my database even if > users input.

The query I'm using is:
$get_prods = "select * from products,prod_items WHERE products.prod_name = prod_items.prod_name and prod_items.item_points <= '$num_points' order by products.city, products.prod_name";

Can anyone see anything obviously wrong with this? is there perhaps a better way to do this? I'm somewhat (okay totally) confused about whether to use inner, outer, left, right joins and so I used this generic join.

Joining Tables
I have a userdatabase and a confenquiry table. Userdatabase has a Userid which is imputed into the confenquiry along with other information when they enter a new enquiry. What i want to do is for each user to be able to display all their enquiries.

I have a session in place so when i enter <? echo $userid; ?> it will come up with their userid. Therefore i thought i could write...

SELECT enquirynum, startdate, enddate
FROM confenquiry, userdatabase
WHERE <? echo $userid;>? = useridconf

However this does not work, can anyone suggest a different way for this to work?

Joining Tables
i'm experimenting a simple project on a student attendance system. Here's a brief discription of the tables:

Table 1: (Contains the student id and name of all 400 students)
name: student_id
fields: student_number (primary key)
student_name

Table 2: (Contains the student number of those who attended the morning assembly (which not everyone attended! :p) )
name: morning_assembly_22febuary
fields: student_number (foreign key)

My question is: How can I join the to tables to search for the student number & name of those who didn't attend the morning assembly?

JOINing Three Tables.
Suppose I have these tables:

news (content & author_id)
comment (comment content & author_id)
authors (id and name)

I want to create a query that gives me, in one go:

one news item
it's author (by name, by ref'ing to the authors table)
all comments attached to it
and the comments' authors (by name, ref'ing to the authors table)

Is this possible? I got as far as listing everything but the commenter's name (I got his ID ofcourse).

Joining Two Tables
I have 2 tables. one with 5 columns and the other with 6

The tables have an accountid column in commom

I would like to join the tables such that the accountids are not duplicated and the new table now has 10 columns.

Joining 2 Tables
I am having a problem with a join statement. I have 2 tables.

table one
- id
- name
- body

table two
- id
- body
- pdate

The relationship is one to many (table one -> table two)

I want to select all the records from table one, and only get the latest row for each record from table two which is related to table one. (pdate is a date field)

Just now if I have 2 rows in table one and 3 rows in table two, 2 of which point towards a single recond in table one, i get 3 rows returned whereas I just want two.

Joining Tables In Sql
I have built an sql script that connects to one tables. however I want to connect now to 2, this is what I had:

$query = "SELECT * FROM artists WHERE ID = ".stripslashes($ID);

how do I connect to 2 tables, I know about foreign fields, its just the sql Im not sure about, if someone could post some example ive been googling it for ages, with no success.

Joining Two Tables
I've got two tables.

Table 1 (ID, DATE, TOY)
Table 2 (ID, DATE, TOY, DESCRIPTION)

I'd like to join them so the results are ID, DATE, TOY, DESCRIPTION meaning I want table 1 to look like table 2.

All my attempts have resulted in millions of rows being returned, not the 77k there are. I'm assuming this a left join job? I've read around the forumn and only encountered answers that go up above what I'm trying.

Joining 2 Tables
I have two tables with that data...truck_id and id in TRUCKS_NAME are reffering to the same thing... how do I create a select query to join both?

TRUCKS_ENTRY
id = 89
truck_id =3
create_by =61713
description = Test
name = John

TRUCKS_NAME
id = 3
truck_name = Mercury
truck_vin = LIIVLKSJOIKES
truck_plate = 2H1037

Joining Tables
I have two tables:

table A
id cause1
1 10
3 11

table B
id cause2
2 12
4 13

I want table C
id cause1 cause2
1 10 null
2 null 12
3 11 null
4 null 13

How can I do this?

Joining Tables....
I'm having some trouble joining tables. My two tables are: aws_event (with hundreds of thousands of events) and awu_user (with lots of users). I need to retrieve all data from aws_event where the id field in aws_event is equal to all user IDs with a certain teacher ID in the awu_user table.

So: in the example below, in need to fetch all data from aws_event for users who are students of teacher 1.

aws_event
------------
user | event | more data
----------------
1 | blah | more
1 | blah | more
2 | blah | more
3 | blah | mpre

aws_user
----------
userid | teacher
1 | 1
2 | 1
3 | 2

Normally, I'd write this in PHP, but I have no PHP access to the server... only SQL query access.

Joining Tables
Table 1            Table2
--------           --------
Ability Bonus      Ability Bonus, 1, 2, 3
AC Bonus           AC Bonus, 4,5,6

What I want the result to be
-----------------------------
Ability Bonus, 1
Ability Bonus, 2
Ability Bonus, 3
AC Bonus,4
AC Bonus,5
AC Bonus,6

I find figure out the right syntax to use, ive tried different combinations of JOIN and UNION but I can't seem to get a solution.

Joining Two Tables
if this is possible:

SHOW COLUMNS FROM table1
ie.

table1_a
table1_b
table1_c
table1_d

and then join another table to this result so it would end up looking like this

table1_a | table2_rowa
table1_b | table2_rowb
table1_c | table2_rowc
table1_d | table2_rowd .

Joining Tables To Themselves
im in the design stage of a very large project at the moment (as you may know from some of my other questions).

im pretty pleased with what i have at this stage but there is one subject that id like to seek expert opinion on before going any further.

in an instance where you had a table of data with millions of records with data such as

id date grade
1 2006-01-01 2
2 2006-01-01 1
1 2006-05-01 3

and you had an extremely common query being (in english)

show the grades of id's who graded 2 on their most recent grading date

what is better practice

creating a second table that has an extra column

id date grade prevgrade
1 2006-01-01 2 null
2 2006-01-01 1 null
1 2006-05-01 3 2

so you can simply say

select id from gradingresults where prevgrade=2

or joining table to itself

i am planning the extra column version but am being advised by a colleague that this is against table normalization procedure.

i imagine that running a query the first way would be considerably faster than the second. any opinions?

Joining Tables
The below statement selects all from orders table and joins the customers table where id=id. The problem is some rows in the orders table will not have a match. Now when I go print the results the $line[id] becomes "NULL" because there was no match. How can I keep the id field from the orders table even if there was no join for that row. Hope that is not to confusing.

Joining 2 Tables
I have two tables with the following fields:

table 1 ("f"):
forum_id (and other fields)

table 2 ("ft"):
forum_id, user_id and some other fields

I want to join the tables on (f.forum_id = ft.forum_id and ft.user_id = 2) but the trick is that ft has an entry for forum_id = 0 that f does not have and I want the join to somehow include a result for that as well. Is there a solution other than to insert another row in table f that has forum_id of 0?

Joining Tables
im developing a b2b portal like alibaba.com or pzplaza.net. Product can be posted in different categories. so the relation b/w products & categories is m-to-m. i developed extra table like 'product_has_category' with both category & product primary keys. Also when there are two level of categories.. main & sub. products are posted into subcategories only. now the problem is user selects MAIN CATEGORY while searching for a product, query always comes up with many records of same product due to m-2-m relation.
though i could not make it clear to u, i suppose, can you please help me ???
one solution is in my mind is: i should allow user to select subcategories bcuz relation is between product & subcategories.( only main categories given in search)

Joining Tables
I have three tables in the following format:

table1.primary_key
table1.data

table2.primary_key
table2.table1_keys

table3.primary_key
table3.table2_keys
table3.data

I want a query that will display
table1.data table3.data

When I try joining the tables I lose rows

Joining Tables
I'm not getting an SQL query to work as designed, and I would love if someone could point me where I'm going wrong.
I have two tables, one is rankings, the other is data. I rank people in the rankings table and all their data is stored in the data table (like height, weight, etc).
Right now, I have two columns in rankings, the id auto increment field and the id for the person's id.

SELECT d.name,d.id FROM data AS d,rankings AS r where r.user_id = d.user_id;

Basically, I want it to select all the users that are in the ranking table (in the order they're ranked, which is specified by the auto_increment value) and return their name. For some reason, this query executes with an empty set.

Joining 3 Tables! Mysql Help...
Here is my current setup:

featuredvideos: videoid
videos: videoid, userid, videotitle, videodescription
members: userid, username, email

So I have a page where I want to list all the featured videos, and I want to grab all the video details as well as details about the user. So I need to somehow join all 3 of these tables.

Currently I joined 2 tables:

PHP

$sql = "SELECT * FROM featuredvideos JOIN videos ON featuredvideos.videoid=videos.videoid";

but I also need information about the user, how would I go about doing this? Also, would it be advantageous to also store the userid in the featuredvideos table as well?

Joining On Unionized Tables
I'm working on a CMS with several database tables featuring animal names and information. My pages begin with two multi-table queries using the UNION command, similar to this:

PHP

SELECT * FROM
(SELECT GZA.Name FROM gz_animals GZA
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
  SELECT GZM.Name FROM gz_mammals GZM
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
  SELECT GZB.Name FROM gz_birds GZB
  WHERE Name LIKE 'Canis_lupus'
  UNION ALL
   )
  as Animalia

SELECT * FROM
(
  SELECT GZA.Name, GZA.MyCla FROM gz_animals GZA
  UNION ALL
  SELECT GZM.Name, GZM.MyCla FROM gz_mammals GZM
  UNION ALL
  SELECT GZB.Name, GZB.MyCla FROM gz_birds GZB
)
  as TClass
  WHERE Name LIKE 'Canis_lupus'

I actually use variables instead of "Canis lupus" so it can display whatever database value matches my URL. Anyway, the above tables focus on scientific names. I'm now ready to add common names, but I'm not sure how to do it.

First, not every animal species has a common name, so I created a separate series of tables for common names. Actually, I've only created the birds' common names table so far. I'm trying to figure out how to join it into my query, but nothing works.

I assume that if I add a join to one table, I have to add the same type of join to every table in the series, right? But if the only nams table is bird_names, then shouldn't I be able to join every other table (gz_mammals, gz_birds, etc.) to bird_names?

If that should work, then I'm probably messing up on this section:

PHP

WHERE Name LIKE 'Canis_lupus'

I've tried variations like these...

PHP

WHERE Name LIKE 'Canis_lupus' OR GZBN.NameCommon LIKE 'mandarin_duck'
WHERE Name LIKE 'Canis_lupus' OR WHERE GZBN.NameCommon LIKE 'mandarin_duck'

So is there a way to do this join if I have just one common names table? Also, note that the first series of tables form a super table named "Animalia," while the second series form a table named "TClass."

Instead of joining bird_names to every individual table, is it possible to simply join it to Animalia, then to TClass? How could I do that?

*Question #2*

Is it possible to create a series of tables joined by UNION to form the super table Animalia, then create a second series of tables joined by UNION to form the super table CommonNames, then join CommonNames to Animalia?

I envision a scheme where I could join the tables even if the only common names table was birds_names. When I finished mammals_names, I'd simply add it to super table CommonNames.

Query Joining 26 Tables
Is there any way a MySQL Query joining 26 tables wont become absurdly long?

Problem JOINing Two Tables
I have two tables, for simplicity lets call them Table AAA and Table BBB, I have a query as follows
SELECT count(DISTINCT AAA.ID ) AS post_count, count( DISTINCT BBB.ID ) AS vote_count
FROM AAA JOIN BBB ON BBB.user_id = 'John'
WHERE AAA.user_id = 'john'

Now this query works fine if both the tables contain records with user_id as 'John'. The problem arises when there's no 'John' in any of these two tables.
There could be situation in my program where john has a entry in only table AAA or only BBB. In case he has no entries in the any table that corresponding DISTINCT COUNT should be returned as zero.

Joining Tables With A Limit
I have two tables, they are

ARTICLES and ARTICLE_IMAGES

Each article can have up to 5 images, the images are displayed on the "VIEW ARTICLE" page. This works fine.

The problem is the "ARTICLE LISTINGS" page. The page displays a summary of each article, the issue i am having is that i also needs to display the first image (image with the lowest id) alongside each summarised article. Is this possible? At the moment it is displaying ALL images, i only want it to display jsut the one, i.e. the one with the lowest id.

Trouble With Joining Tables
I'm having some trouble running the following query:

SELECT DISTINCT(im.id) FROM item_master im LEFT JOIN xref xr ON im.id = xr.item_master_id WHERE im.item_number LIKE '%100%' OR xr.oem LIKE '%100%'

For some reason, the query takes 2 minutes to execute. If I remove the bit that says "im.item_number LIKE '%100%'", or change it to compare data from the joined table (eg. "xr.item_number LIKE '%100%'"), the query is executed in 31ms.

Joining Two Tables To Get Value Of Unique ID
trying to select a value (PK) from one table and join to another table, getting the value of the item with that ID. I seem only to be gettinbg the unique ID value and not the other value in the record.

("SELECT Business_Type_ID From tbl_BusinessDetails table inner join tbl_businessType
on Business_Type_ID = Type_ID");

here's the table structure

tbl_BusinessType
| Type_ID | Business_Type |

tbl_BusinessDetails
| Business_ID | Business_Name| Business_Type_ID |

I am trying to get the value of Business_Type (from tbl_BusinessType) using the PK (Business_Type_ID) in the tbl_BusinessDetails.

I also do not want any matches.

Joining Tables That Are In Different Databases
How do I join two table that are in two different databases? In MSSQL or Oracle, I would have done something like "SELECT * FROM db1..table1 t1, db2..table2 t2 WHERE t1.id = t2.id". Unless my memory failed me anyway. Is there an equivilant syntax for MySQL?

Joining 4 Tables - Performance
The Tables I have are...

Types        Styles        Contents
-----        ------        --------
ID        ID        ID
Name        Name        Name

Items        Types_Are    Styles_Are    Contents_Are
--------    ---------    ----------    ------------
ID        Item_ID        Item_ID        Item_ID
Name        Type_ID        Style_ID    Content_ID
Status
Created
...
...

Items can obviously have more than 1 Type, Style, and Content, or even non of them. Users will select Type(s), Style(s), and Content(s), sometimes they may not select any types, or styles, or contents. I want a list of Item IDs to be returned that match these conditions. Currently I run the select query as....

select distinct t1.id from items t1, types_are t2, styles_are t3, contents_are t4
where  t1.status = 2
and    t2.item_id = t1.id and (t2.type_id = 3)
and    t3.item_id = t1.id and (t3.style_id = 3)
and    t4.item_id = t1.id and (t4.content_id = 2 and t4.content_id = 4 and t4.content_id = 5)
order by rand().

But sometimes this takes around 5 minutes to run, and with only around 50 Items on the database. Obviously this is a mega basic join, so how could performance be improved, what sort of join whould i be using?

MySQL Joining Tables
I have 2 tables, one that has

$query="CREATE TABLE products (id int(6) NOT NULL auto_increment,fname varchar(15) NOT NULL,lname varchar(15) NOT NULL,stage2 varchar(20) NOT NULL,itemnumber varchar(20) NOT NULL,quanity varchar(20) NOT NULL,email varchar(30) NOT NULL,location varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";

In the 2nd table I want to have 3 items, itemnumber, quanity, and descrition.
I want itemnumber from the 1st table to refer to the 2nd table and update the quanity. For example, If I order 3 of item #12345 then i want it to update the #12345 to 3.

And then say in a week someone else orders 4 of #12345 then i want it to change to 7. How would I go about linking these tables together,