MYSQL :: Self Join (?) To Get Data For Multiple Users From One Table

Preventing Multiple Users From Corrupting Your Data
I'm looking for some advice on how to best prevent my data from getting confused in the event that I have multiple people using my website at the same time.

I'm using PHP 5 to interface with MYSQL 5.

I need to have the webguest account perform an insert on two tables, in two separate queries that occur in the same batch of code (same mysql connection). I need to be able to identify entries that came from the same user in the two different tables (last_insert_id() written to the second table). I don't want the data getting crossed if two users are performing inserts at nearly the same time.

My question is, what is the common practice to solve this problem? If I use LOCK TABLES, are there problems with the table getting left locked by the users (computer crash, network outage etc)? If the table is locked and another user tries to access it how can I have the PHP code wait and try to access again after a couple seconds? Or can I just limit the number of consecutive logins with the webguest account to 1?

Getting Data Out Of Multiple Tables And Join
I have two separate querys, but what I want to do is make one query from it. To get data from different tables. Code:

Multiple Table Join
I am using MYSQL version 3.23.58-16.FC2.1 which has caused me issues in the past. However everything is working so its the old addage if it aint broke...dont fix it.

anyway heres the situation,

I have three tables clients that have multiple properties and those multiple properties have multiple payments against them. I am trying to list a monthly statement per client, per property, of payments to look something like this: -

Client Property Transactions
Name prop_id --> prop_id
Client_id -> client_id Payment
address
I know in the past i have 'nested select statements' which have not worked on my version of Mysql so I need to find a way round this. I have done this so far but it doesnt work

SELECT *
FROM clients c RIGHT JOIN properties p ON c.LeadIDNumber=p.Clientid AND (p.Status<>'Under Offer' OR p.Status <>'Fall Through') RIGHT JOIN transactions t ON p.id=t.transPropid
GROUP BY p.id
ORDER BY c.LeadIDNumber, p.StreetName
Im pretty sure its all going wrong due to the fact im trying to do the second right join on a table on the criteria of the first right join (if that akes sense) but i dont know enough to restructure the query.

Multiple Table Join
What is the syntax for joining multiple talbes?

Create Users Table For Users To Log On Or ...?
I'm pretty new to MySQL, what I was wondering is, for web systems that you need to login to, do developers generally create a users table and store all their users in there, or do they create users such as the root user in mysql ? If so, how do you add extra fields such as firstname last name etc and how would you go about putting them into groups?

Join Table With Empty Data
I'm a novice, and am having issues get a JOIN to work properly. The following code is correct for pulling field values that HAVE a value, but when a field i am requesting has a value like Null, Space, or just empty, the whole query comes back empty.

It was suggested i use TRIM in my call, but i'm not exactly sure how to use properly.

As far as each table, the fields that are empty are set to ALLOW NULL.
PHP

Select
company.company_name,
company.company_username,
company.company_clients_served,
company.company_status,
note.note_text,
address.address_street1,
address.address_street2,
address.address_zip,
address.address_zip_ext,
county.county_id,
city.city_name,
taxes_states.state_id,
email.email_address,
url.url_path,
url.url_name,
company_type.company_type_id
From
company
Inner Join note ON company.note_id = note.note_id
,
address
Inner Join connector_address ON company.company_id = connector_address.ref_id AND connector_address.address_id = address.address_id
Inner Join county ON address.county_id = county.county_id
Inner Join city ON address.city_id = city.city_id
Inner Join taxes_states ON address.state_id = taxes_states.state_id
Inner Join connector_email ON company.company_id = connector_email.ref_id
Inner Join email ON connector_email.email_id = email.email_id
Inner Join connector_url ON company.company_id = connector_url.ref_id
Inner Join url ON connector_url.url_id = url.url_id
,
company_type
Inner Join connector_company ON company_type.company_type_id = connector_company.company_type AND company.company_id = connector_company.company_id
Where company.company_id = ཋ'

Selecting Data From More Than One Table (not Join)
I have a database with a bunch of tables, including two tables that store messages. These two tables have different names, but are identical in structure.

In my app, a user can run a report that selects messages from both of these tables. This is currently achieved by running two selects and printing out the details.

However I would now like to sort and limit (i.e. show messages 1-25 and have a link to show the next 25 etc).
I can do this in the code by dumping the combined results in an array and then sorting/limiting the output, but this will cause problems if the number of messages gets too big and the array size becomes too large for the memory in the server.
Also it doesn't help any with optimization and won't speed up the query time as I won't be able to use the LIMIT clause in the SQL!

So what I want to know is whether it is possible in SQL to select data from more than one table, given that the two (or more) tables to be joined will be identical in structure? If so, can I perform the normal ORDER BY and LIMIT functions?

Comparing Data In Multiple Table
In my db I have multiple tables, which are versions of the same table from different days. I import the data daily by renaming the old table to table_name_date and then importing the new data into a freshly created empty table. This gives me the following tables:

mysql> show tables;
+-------------------+
| Tables_in_mydb |
+-------------------+
| custinfo |
| custinfo_0206 |
| custinfo_0207 |
+-------------------+

The columns are obviously the same in each table:
mysql> describe custinfo;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| x | int(11) | NO | | | |
| y | int(11) | NO | | | |
+-------------+---------------+------+-----+---------+-------+

what I woud like to do is to query all "x" from each of the tables and compare them. I can obviously query for "x" in custinfo and then for "x" in custinfo_0207 and compare them, but can I do this in one query?

Ideally what I would get is something like that
+-------------+---------------+--------------+-----------------+
| Field | custinfo_0206 | custinfo_0206 | custinfo_0207
+-------------+---------------+--------------+-----------------+
| id | 1 | 1 | 1 |
| x | 5 | 4 | 2 |
| y | 150 | 150 | 135 |
+-------------+---------------+------+-----+---------+-------+

Is that possible?

Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row
SQL
SELECT o.title, o.quantity, o.price, o.product, i.supplierFROM store_order_inv o, store_inventory iWHERE o.product = i.productAND o.cart_order_id = ?-195509-3867'

my only problem is if a row with Product ID doesnt exist in inventory, no result is returned, EVEN if a row exists in order_inve table with that product.

So my question is, is it possible to select data from 2 tables, but where the presence of a row with matching PRODUCT field in the inventory table is OPTIONAL

FOUND_ROWS() In Multiple Users Environmnent
Suppose many users will issue SELECT statement to the server, e.g.

1. UserA issued a SELECT statement
2. UserA issued FOUND_ROWS() and get the correct result

The above is okay, but problems might occur when there is more than one
users

1. UserA issued a SELECT statement
2. UserB issued another SELECT statement
3. UserA issued FOUND_ROWS() and will get the wrong result?

How To Use Database For Multiple Users/states
I want to implement a shopping cart based on mySQL and CGI Sessions. I want to store the sessions in mySQL with a uniques session id for state maintanence. Here's my question, and I hope I'm not being too thick, though Im sure I am. I'm going to have multiple users (ie web stores) using the cart through my server. In other words, they'll have forms on their side sent to my servers shopping cart script.

Do you think its okay to have all the users (web stores, their customers) share the same session table in one broad database, or should I create a separate database for each web store? I will have other tables like state names, etc... Can these be shared or again should I duplicate for each web store.

Selecting Only The First Message From Each Of Multiple Users
I have a table of cellphone text messages from users. Columns include (among others) TimeStamp (DATETIME), MessageText (VARCHAR(140)), and PhoneNumber (CHAR(10)). I want to select only the first message received from each number in a given week. What query would I use to do this? I tried SELECT DISTINCT, but that seem to only apply to an entire row, not individual columns.

Query Returns Unexpected Users. Not Using Join.
Having a bit of a problem trying to get the correct results. Basically I have three tables I need to select from; user, platform and game.

I'm running the following query

select distinct
u.user_name,
u.user_avatar,
u.user_rating
from
user_game ug,
platform p,
user u
where
ug.game_id = 1
and p.platform_id = 1
and u.user_country = 1
Basically I want it to return users who own the game "1", have it on platform "1" and live in country "1".

Unfortunately does not return users who meet ALL WHERE criteria.

Any ideas where I am going wrong? I know an alternative is to use JOINS but I don't want the performance to be hit by doing that.

Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something...

(I'm using PHP and MySQL)

When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site.

However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?

Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address
AND localhost@my_field_db FROM my_field_tablet_pc to affect
a custom sycnchronization that SQLyog will not allow.

Both remotehost and localhost are built on WAMP stacks.

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Limiting Users' Access To Only Their Data
I'm working on a single database with a dozen or so tables and fifty or so users. Essentially every record has a field defining the user who generated the data; think scientists' measurements. Up until now we have not been concerned with restricting access to the data.

Now we would like to implement some security in the sense that a particular user would only be able to interact with his or her data; that is I would like to restrict a user's privileges based on the value of a particular field for all of the records in a given table. Is this possible?

One idea I had was to generate a database for each user with only his/her data, and grant the user privileges to only this database. This seems a bit clumsy though. Other than that, I can imagine implementing something through the front end when the data is selected, but this seems problematic because if someone knew SQL and their username and password they would be able to access everyone else's data with another frontend.

Grab All Users Not In Second Table.
I have two tables. I want to grab all the users from the users table who are not found in the second table. It's just seems to be one of those days where I can't perform the simple tasks, I think I need to go back to bed.

[table] users
[field] userid

[table] user_details
[field] userid

Select userid from users where userid not in user details

Best Practices For A 'Users' Table
I am wondering what would be the best way to design the table(s) for users in a database, or at least get some opionions of how people would go about it.

Say I have the normal stuff about a user such as name, email, phone etc, a good number of user attributes. Then I also have profile kind of attributes such as photo, more personal details, various preferences etc, again pleanty of attibutes. Then I also have a bunch of access or priviledges attributes such as status and whether they can access/modify certain modules.

I could get all these in a single table since a user is going to potentially have all of these attributes once (though a lot could initially be null) but this is going to be one huge (wide) table maybe 30 columns or more.

Would it make sense to split it into 3 tables like 'users', 'profiles', 'priviledges'? If so, should all these be created at the same time when a user signs up or as needed when a user decides to add a profile or get assigned special proviledges. If I create then on demand I will probably be saving a lot of unnecessary rows but I will have to be dealing with outer joins and defaults most of the time, ie more complex code. On the other hand if I depend on all three existing (besides the waste issue) I might get the wrong results if, for any reason, a row for each user fails to get created in all tables (or is accidentally removed).

Table Of Users Having Many To Many Relationship With Itself?
i hope to implement a user subscription feature on my webdatabase, where users can subscribe to each other's postings.

Can i achieve this by having one table to map the USERIDs to each other, and then relate this to the main USER table?

Currently I have two user tables that are exact mirrors of each other, and then a junction table to relate the two, but im beginning to think this is kinda dumb.

Fields In Users Db Table For Authentication
which fields do you place in your db for user authenication?

for example..:
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(40) NOT NULL default '',
`Password` VARCHAR(40) NOT NULL default '',
`Lastlogin` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

i wonder for some more useful fields for software logging/management people sometimes (or seldom :-)) use like:
lastip
createdon
lastlogin
active ('yes', 'no', 'banned') -

Can't Add New Users & Passwords To Grant Table
I am running MySQL 4.1.7 on my Windows XP system. I can create databases, ect. on the command line, but when I try to run a PHP script I receive the 'Access denied' error message. I have tried using the Grant command to add a new user but nothing comes up when I show SHOW GRANT except the root user. How do I add the new users for running the PHP script?

Replace Some Username Values With The Users ID In A Different Table
I really can't figure this out. I want to replace some username values with the users ID in a different table.

The tables are

pictures
user (Example FRED)
picture (Example pic.gif)

users
id (Example 5233)
user (Example FRED)

What I want to do is replace the user column in pictures with the correct id from the users table

So in the pictures table, it would no longer be FRED it would be 5233

Is there a query I can run to do this?

I'd really appreciate some feedback

Obtain Most Popular Cities From My Users Table
I've got a 'users' table:

USER_ID --- CITY_ID
1 ---- 32
2 ---- 20
3----- 32
4 ---- 32
5 ----- 17
....

I would like to obtain the most popular cities from this table, and the number of occurrences. Is this possible to make it easy with an SQL statement?

Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID.

For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.

Making Site Login Add Users To Phpbb Database Table
I have a login script for my site that I got off the net and was planning to use, but, I also wanted to make it so that when a user signed up thru the registration form it would also add them to the phpbb database's table. It wasn't discussed heavily from where i got the script but from what was mentioned the encryptions differ which results in a rejected login. Code:

Multiple Row Join?
I have two tables - one called parent, the other called group.

table group is made up of ID,parentID,groupHash and groupName.

table parent is made up of ID,title,group1Hash,group2Hash,group3Hash (and
other columns)

Each of the group hashes in table parent correspond to a group hash in table
group (ie parent.group1Hash exists in/as group.groupHash and
parent.group2Hash also exists (on another row) in/as group.groupHash).

This means table parent can have up to three groupHash's noted in a single
row and each of these groupHash's will have its name in a corresponding row
in table group (using groupHash as key).

Can I perform a select whereby I read a row from parent and use its
group1Hash, group2Hash and group3Hash to retrieve their corresponding names
from table group (all in one select statement)? Or must I perform three or
four individual selects to get the desired values from the two tables.

Multiple Join
I have a query which works, but I would like to know if I can get it to work without having to use 'group by'? If I do not use the 'group by' I get multiple results of the same thing.
Code:

select C.call_id, C.extn_no, dest, G.group_name
from call_data as C, group_user as G
left join user_grp as U on G.group_id = U.group_id
left join grp_user on C.extn_no = G.extn_no
where C.direction = 'Out'
group by C.call_id

Multiple JOIN Problem
I have tables: gallery_album, gallery_photo, node.

On node, the important columns are: type (type must be "gallery"), nid (integer node id), uid - node owner id.

On gallery_photo, the important colums are: nid (relates photo to node entry), album_id - relates photos to gallery_album entries.

On gallery_album, the important colums are: id (must match album_id in gallery_photo), title, must not be empty (check for <> "" does the trick), owner_id

For every node entry of type "gallery" there exists exactly one entry in gallery_photos - one to one relationship.

For every gallery_album entry there exists any number of gallery_photo and node entries - one to many relationship.

I know the user id, I need to return albums belonging to that user together with photo count in that album and newest photo from that album (specified by nid and node.created timestamp).

If album has 0 entries, I still need it!

The problem is that I need to LEFT JOIN (right?) TWO tables (gallery_photo, nodes) on ONE (gallery_album), how do I do that?

What Join To Use With Multiple Tables?
I'm assuming I need some sort of join to accomplish this but I've yet to find something that I have been able to understand.

I have 3 tables - Items, Photos and a table that links the two that I've elegantly named ItemsPhotos

Items
- ItemID
- ItemName
- ItemEtc

Photos
- PhotoID
- PhotoFilename
- PhotoEtc

ItemsPhotos
- ItemPhotoID
- ItemID
- PhotoID

Each Item has a unique ID, as does each Photo. The ItemsPhotos keeps track of their relationship.

I want to be able to select all of the items and only one of the the available photos listed for that item to be returned to an Array for use like the following;

foreach(#){
$databaseOutput[#]['ItemName'];
$databaseOutput[#]['PhotoFilename'];
}

If anoyone knows how I can accomplish this I would be very grateful for a solution.

Sincere appologies if there is an obvious answer or if I have mis-posted - I've been trying to get this (among other things) to work for hours now and I'm desperate for a solution.

Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used).

What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?

Multiple Left Join
Have the below SQL to pull data from 3 tables inside one database. the first two tables pull great - third, no error but no data either.

mySQL="Select tblnetworth.fldName, fldnetWorth, fldhonor, fldLand " &_
      "from tblnetworth " &_
      "LEFT JOIN tblhonor ON tblnetworth.fldname = tblhonor.fldname " & _
      "LEFT JOIN tblland ON tblhonor.fldname = tblland.fldLand " &_
      "WHERE " & strNWSQL & "tblnetworth.fldDate='" & strTodaysDate & "'" &_
      " order by " & strName & " " & strSort & ";"
writing the SQL to the screen comes out like

Select tblnetworth.fldName, fldnetWorth, fldhonor, fldLand from tblnetworth LEFT JOIN tblhonor ON tblnetworth.fldname = tblhonor.fldname LEFT JOIN tblland ON tblhonor.fldname = tblland.fldLand WHERE tblnetworth.fldDate='2004/8/19' order by fldnetworth ASC;

which seems ok - but why would the land not display? In the table the fldLand is INT setting, and there are about 1750 matching records in it.

Multiple Tables With Join
$queryDelete = "
DELETE FROM table1,table2
WHERE table1.id=".$_GET['id']."
AND table2.id=".$_GET['id'];
Looks ok, but doesnt work.....$_GET['id'] is read correctly and available in all tables.

Confused By Multiple JOIN
My query is giving me a syntax error. How do I do this?

Code:

SELECT t.id, t.title, t.name, t.company, t.address, t.country,
t.postcode, t.telephone, t.email, tm.msg, ts.site
FROM trials AS t
LEFT JOIN trials_sites AS ts ON (ts.trial_id = t.id)
LEFT JOIN trials_msg AS tm ON (tm.trial_id = t.id)
LEFT JOIN trials_times AS tt ON (t.id = tt.trial_id)
LEFT JOIN trial_days AS td ON (tt.day_id = td.id)
WHERE td.day = 'Monday'
ORDER BY t.id DESC

I definitely want a whole row from `trials. But each row may have a row associated with it from each of the joined tables. In the case of the `trial_times` table, there may be more than 1 associated row - or none.

Is it possible to execute a query like this?

Multiple LEFT OUTER JOIN
What is the right way to do a LEFT OUTER JOIN on both these tables to the 'Main' table where WHERE Plan.pID IS NULL and Record.rID IS NULL (primary's)

Plan
pID | _mn (foreign main key) | pType

Record
rID | _mn (foreign main key) | rDate

Main
mID (primary) | mComp

-------------------------------------
Will need: pType, rDate, mComp values as a result of the query.

LEFT JOIN With Multiple Tables
I've got a query that does a LEFT JOIN.

Something like this:

SELECT a.id, myB.someField, c.someColumn
FROM c, a
LEFT JOIN b AS myB ON (myB.id = a.id)

However, if I reverse the two tables in the FROM clause, it doesn't work anymore. It seems like the table in the ON part has to be the last table mentioned in the FROM clause.

Curiously enough, it DOES work on MySQL 4 (4.1.16-nt), but NOT on MySQL 5 (5.0.45-community-nt).

This might not seem much of a problem, but sometimes I want to do more LEFT JOINs in one query, with different tables in the ON part. And I don't see how I can do that in MySQL 5.x, as all the tables would all have to be last in line in the FROM part.

DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once
(preferred the earliest "hav_date").

I've tried with "DISTINCT kohde_name" in many ways, but always end up with
syntax error...:(
Is there any solution with DISTINCT, or do I need anotjer solution?

my (simplified) query:
SELECT henk_name,henk_number, kohde_name
,kohde_txt,havainto_id,hav_kohdetxt, hav_date
FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id)
INNER JOIN havainto ON hja_havainto_id=havainto_id)
INNER JOIN kohde ON hav_kohde_id=kohde_id
WHERE ...

JOIN For Display Of Multiple Arrays
My latest challenge is this:

I'm still working on my "Books/Stories" Project. What I'm working out is how to display a "notes" section next to each story entry. Basically it's a comments section next to each paragraph of a story.

So my plan of attack was going to be to setup my select statement with joins to get:

FROM story-text TABLE
storyid - which story the text belongs to
blockid - which paragraph block the text is
content - the actual paragraph text
authorid - which author wrote this paragraph (JOIN with username from users table)
dateadded - when the text was added to the db

FROM story-notes TABLE
storyid - which story
blockid - which paragraph block
content - the actual note text
authorid - Who left the note (JOIN with username from users table)
dateadded - when the note was added

So then I could use a 'while' loop to get and display each paragraph with its notes then move to the next paragraph.

BUT WAIT... There could be multiple notes per paragraph... so how do I efficiently get all the notes when I get each paragraph? I can't while loop with another select statement inside another while loop can I?

I feel like this can be done best with a proper use of JOINS and the subsequent extraction and reconstruction of data, but I'm a little overwhelmed on how that would go.

Left Join Multiple Columns
I am having problems LEFT joining Muti - columns to ONE table

This is what I am using:

SELECT *
FROM nanny_tbl left join position_tbl on nanny_tbl.position_pos=position_tbl.recid_pos and nanny_tbl.position2_nan=position_tbl.recid_pos
WHERE recid_nan = colname

The first left join woks 100%
the 2nd does not

nanny_tbl stucture is:

recid_pos --- this is primary key
position_pos ---- this is VarChar

Who can help me solve this?

Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row.

SELECT * FROM asset
LEFT JOIN task ON (asset.id = task.asset_id)
WHERE asset.id=task.asset_id
GROUP BY asset.name

The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like.

asset1.id asset1.name task1.name task1.status task2.name task2.status

Is there an easy way to do this in SQL?

Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields
between them, not even a KEY or ID or anything like that, except for
the "body" of a blob field. and that text is not identical, just a
portion of that text is identical.

each table has 5 fields, all different except the blob, which is
called "message", so normally I use something like:

select * from table1 where message like '%apple%';

to query this table, and the same goes for table 2, except the blob is
different, table 2 normally is like this:

select * from table2 where message like '%customerid=453%';

It's impossible to change the data in these fields (which would be the
best option), but there is one common element between them in the
message blob.
What I want to do is something like this:

select * from table1, table2 where message like '%order=100%';

however only one table will have that order, either table1 or table2,
but never both, and theres no way to tell which of the tables will
actually have the text.

In other words, I want to search a bunch of tables for common text
without having to actually submit the query five times, because the
list of elements to search is about 25,000 items... I'd rather submit
25,000 queries than 125,000 queries.

Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after
row of Calories consumed by children eating lunch at the school
cafeteria. The "ID" column is an autoincrement field I added, but it
doesn't seem to be doing me any good.

mysql> SELECT * FROM Eats;
+------+------+------+----------+----+
| dow | who | sex | Calories | ID |
+------+------+------+----------+----+
| Mon | John | Boy | 2600 | 1 |
| Mon | Tom | Boy | 1900 | 2 |
| Mon | Jane | Girl | 1200 | 3 |
| Tue | Tom | Boy | 1600 | 4 |
| Tue | Jane | Girl | 1300 | 5 |
+------+------+------+----------+----+

The output I WANT is a table with total calories broken down by gender
and day, i.e., one (and only one) table with a "Boys" total and a
"Girls" total. In this case I want to see:

+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 1200 |
| Tue | 1600 | 1300 |
+------+------+-------+
It's important to have One table with Two headers, not the other way around.

Not knowing any better I tried a JOIN and came closest with:
mysql> SELECT a.dow AS "Day",
-> SUM(a.Calories) AS "Boys",
-> SUM(b.Calories) AS "Girls"
-> FROM Eats AS a
-> JOIN Eats AS b
-> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow;

Which produces:
+------+------+-------+
| Day | Boys | Girls |
+------+------+-------+
| Mon | 4500 | 2400 |
| Tue | 1600 | 1300 |
+------+------+-------+

Everything is right except Mon-Girls, which is twice the real value.
I figure this is due to the pairing of Jane-John and Jane-Tom, causing
Jane's meal to get counted twice. But that pairing is necessary to make
sure we count all the boys' Calories too.

Am I overlooking something obvious? Are JOINs and SUMs just a bad idea?
Most importantly, is there some other way to accomplish the task? I've tried
many, many variations on the above code (with and without using the ID field)
and gotten nowhere.

For this simple example I could do SELECTs INTO variables, but that solution
doesn't generalize to hundreds of rows, nor cases where the children are
further broken down by age. Code:

Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables

ex:
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...
, , ... , , ...table3_Col1, table3_Col2, table3_Col3...

using the 4.0 sql so no subqueries supported.

Inserting Multiple Items (# Unknown) Into A Table Using Primary Id From Master Table
In my database I have a master table for products using a auto-num id column. One product may have several purchase options (items) which have to be entered into another table.

To enter a new product and items I use a form that gathers the information for the master product and one item. This information is then inserted into the database. For the items I use mysql_insert_id() to get the id into the items table.

My challenge is getting this same id for additional items. Once the user has input the initial product and first item they hit a submit button to call a form for adding any additional items, and this is repeated until all items for that product are entered.

The new items are inserted into the database okay, but always with an id of 0. The mysql_insert_id() no longer works. Is there a way to pass this id from the previous insertion to the new one by passing a variable? or echoing input type"hidden"?

Or even better yet, Im wondering if I can ask the user at the time of entering information for the product how many items that particular product has and then bring up that many form fields to enter multiple items? Can this be done using something like an incremented loop to call form fields?

Lots Of Data Lots Of Users..
If I have around 5,000-10,000 pieces of data (which will get updated daily) that I need to display in a grid and will expect 1000+ visitors a day viewing this data, what is the best way to go about doing this?

Would it be better to export all the data to a database and then import to grid or just put the data into text file or something and import to grid from that?

Join Data From Different Dbs
I need code in joining tables, here is the conditions I need to check

(If S.prod _id !=1
If S.prod_nm=x then join x1 where S.prod_id = x1.prod_id and S.dt= x1.dt)
And
(if s.prod_nm=y then join x2where S.prod_id=x2.prod_id and s.dt=x2.dt)

S= source data
X1 = db1.tb1
X2 = db2.tb2

And the result I get I need to insert into Z field in the temp table.

Multiple Data
Is there any way of storing multiple data in 1 mysql field and seperate it with commas like...
word 1, this, another thing, 4th thing, hello
etc, and that would all be in 1 mysql field, and you could be able to pull out each word seperatly?

Multiple Data Directories.
Is it possible to have different data directories for different databases. Not just different subdirectories of the parent directory but data located in completely different locations (say a different drive for instance).

Data From Multiple Tables
I am working on a website about sea shells and basically I have 3 tables:

- shell_tb; (table of shells)
- site_tb; (sites where they can be found)
- instrmt_tb; (instruments used for it)

As you may already have guessed there are two columns in shell_tb (site and inst) which may use more than one item from the site_tb and instrmt_tb.
1st. How do I configure the columns in shell_tb in order to accept more than one value?
(I tried:

CREATE TABLE shell_tb (
shell_id INT(1) NOT NULL AUTO_INCREMENT,
shell_name CHAR(40) NOT NULL,
site INT(20) NOT NULL,
inst INT(20) NOT NULL,
PRIMARY KEY (shell_id)
) TYPE=MyISAM;

but no luck at all when inserting more than on value into inst column.) 2nd. How do I retrieve the info from there?