To Re-write Complex Query With JOINS

Complex Joins
Here is the problem. Table_1 (gl_Train_KeyIdeas)

+----------------------+----------------+-------------------+---------------+
| KeyIdea_ID | Unit_ID | Group_ID | Title |
+----------------------+----------------+-------------------+---------------+
| 1 | 1 | 27 | yada 1 |
| | | | |
| 2 | 1 | 27 | yada 2 |
| | | | |
| 3 | 1 | 27 | yada 3 |
| | | | |
| 4 | 1 | 27 | yada 4 |
| | | | |
+----------------------+----------------+-------------------+----------------+

Table_2 (gl_Train_Progress)

+----------------------+----------------+----------------------+
| ID | User_ID | KeyIdea_ID |
+----------------------+----------------+----------------------+
| 12 | 5 | 3 |
| | | |
| 11 | 5 | 2 |
| | | |
| 10 | 5 | 1 |
| | | |
+----------------------+----------------+-----------------------+


The following sql returns field KeyIdea_ID = 4 which is the only
KeyIdea not in both tables.

SELECT gl_Train_KeyIdeas.KeyIdea_ID
FROM gl_Train_KeyIdeas LEFT JOIN gl_Train_Progress ON
gl_Train_KeyIdeas.KeyIdea_ID = gl_Train_Progress.KeyIdea_ID
WHERE (((gl_Train_Progress.KeyIdea_ID) Is Null) AND
((gl_Train_KeyIdeas.Unit_ID)=1));

What I seem to be having trouble with is specifying the User_ID in
table 2. I need to specify the current user for example: an sql with a
User_ID = 6 would return KeyIdea_ID of 1, 2, 3, and 4.

---

Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords

Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter.

What is the best way to do this in the most efficient query possible.

I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators.

any ideas?

here is the query fyi:

PHP

$sql =  "SELECT products_id as prodid,
                    products_name as name,
                    manufacturers_id as manufacturers_id,
                    products_price as price,
                    products_msrp as msrp,
                    products_date_added as pda,
                    products_status as status,
                    products_sku as sku,
                    products_weight as weight,
                    categories_id as cat_id,
"
        .boolean_sql_select(
            boolean_inclusive_atoms($search_string),
            $fulltext_key)." as relevance
"
        ."FROM $table_name
"
        ."WHERE
"
        .boolean_sql_where($search_string,$fulltext_key)."
"
        ."HAVING relevance>0
";
        
        
        
        if (isset($HTTP_GET_VARS['sort'])) {
            if ($_GET['sort']=="brand") {
            $sql.="ORDER BY products_name ";
            } elseif ($_GET['sort']=="size") {
            $sql.="ORDER BY products_weight ";
            } elseif ($_GET['sort']=="price_low") {
            $sql.="ORDER BY products_price ASC ";
            } elseif ($_GET['sort']=="price_high") {
            $sql.="ORDER BY products_price DESC ";
            } elseif ($_GET['sort']=="fav") {
            $sql.="ORDER BY products_name ";
            }
            
        } else {
            $sql.="ORDER BY relevance DESC
";
        }

---

How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!".

I have 2 tables, same DB.

Table 1 is named Policies
Table 2 is named Assignments

Both contain a PolicyID field.

I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments.

So if "Select PolicyID from Assignments" returned the following:
1, 2, 3, 4, 5, 6

And "Select PolicyID from Policies" returned:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

What do I use to return this? 7, 8, 9, 10

Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!

---

Need Help With Complex Query
i've got 2 tables

TABLE 1 contain a list of color with field color_id, title, description

TABLE 2 contain connection between different color

ex:

colorA | colorB
1 - - - - 2
1 - - - - 8
1 - - - - 9
2 - - - - 3
8 - - - - 6
8 - - - - 7

what I want is to be able to list all ColorB for a specific ColorA, but ordered by the most connection from all colorB .....

so if I list connection for colorA #1

it should give me

8
2
9

as 8 have 2 colorB, 2 have 1colorA and 9 have no colorB at all!

Is there a way to do this in 1 query?

---

Complex SQL Query
Hi every1. In my domain I have two kinds of objects: Software and Enumerate. It is many-to-many relationship between Software and Enumerate objects. I use mysql database to store them. So I created 3 tables: software, enumerates and soft_to_enum to hold relationships between objects.

software
-------------
id
name char(255)
developer char(255)

enumerates
---------------
id
type char(255)
name char(255)


soft_to_enum
-------------------
soft_id
enum_id


I have no problem inserting and updating this kind of relationship. But when it comes to selecting, I feel confused. I want to select software objects which links to at least 3 enumerate objects with a type equal to string ‘os’.

I write this SQL statement:

SELECT s.id, s.name, s.developer, s2e.soft_id, s2e.enum_id, e.id, e.type, e.name FROM software s INNER JOIN soft_to_enum s2e ON s2e.soft _id = s.id INNER JOIN enumerates e ON s2e.enum _id = e.id WHERE e.type =’os’ GROUP BY s.id HAVING count(s.id)>3;

It seems to be working. In a result I got several software objects, along with software name and software developer name. And I got column values for only one enum record. But the problem is that I want to load all the enum records, not only one, connected to selected software record. How can I make this by only one SQL call?

---

Help With Complex (I Think) Query Please
I am building a site with PHP and MySQL. I am writing a function to get data from a table, but I need to select distinct rows from within distinct rows, and more. The function I have written so far contains about three queries, and the way I see it, that is jut not good enough.

I know this is not the PHP forum, but I am looking for help on writing a query which will incorporate all three, to save server load, and time - the fabled "let MySQL do all the work".

Basically, I have a table of various client jobs. Each record has a unique id, as well as a numeric project_id, used to group records from the same job together. At the same time, each record has a job type (varchar) This could be "installation", "brochure", "website" etc.

So, each project is made up of various job types.

I need to select the distinct project_id, thus grouping all work from the same project together, at the same time, I need to select distinct job types from within each project. We could have two websites done in the same project, yet, I do not want to display "websites" twice on my page.

There is also a client_id field and a client lookup table ("clients") I also need to join this table with the corresponding record in the clients table to get the client name.

I am not sure if I have explained to well, but here is my PHP code to try and show what I am trying to achieve.

PHP

function showRecentWork($limit){    // get the projects from client_work table        $s_getWork = "SELECT DISTINCT * FROM client_work GROUP BY project_id ORDER BY project_date DESC LIMIT ".$limit;    $q_getwork = mysql_query($s_getWork);    $numjobs = mysql_num_rows($q_getwork);            while($recent_work_item = mysql_fetch_array($q_getwork))    {                    // get the client id        $project_id        = $recent_work_item['client_id'];        // get the project id            $project_id        = $recent_work_item['project_id'];            // get the various job types for this particular project        $s_getjobtype = "SELECT DISTINCT * FROM client_work WHERE project_id=".$project_id ." GROUP BY job_type";        $q_getjobtype = mysql_query($s_getjobtype);        $num_jobtypes = mysql_num_rows($q_getjobtype);                    // build the list of job types        while($this_jobtype = mysql_fetch_array($q_getjobtype))        {            $thistype = $this_jobtype['job_type'];            $job_type .= " <a href="/work/".$thistype.".php">".$thistype."</a>";        }                    // get the client name from the clients table        $s_getclient = "SELECT * FROM clients WHERE id=".$clientId;        $q_getclient = mysql_query($s_getclient);        $r_getclient = mysql_fetch_array($q_getclient);                $client_name = $r_getclient['client_name'];                ....                //Print out all the data        }}

---

Complex Query
i have 3 tables :
AUDIT
SHOP
PAGE

The audit contains actions of what a site manager does :
_____________________________
|AUDIT_ID|TYPE|ADMINISTRATOR_ID|LINK_ID

The Audit_ID IS THE PRIMARY KEY
TYPE will be EITHER SHOP or PAGE

I want a query which will retrieve the details of the audit table and the matching record from either shop or page depending on what type is. The matching record in either the SHOP or PAGE table is found by AUDIT.LINK_ID.

So AUDIT.LINK_ID WILL = either SHOP.SHOP_ID or PAGE.PAGE_ID.

So if TYPE = SHOP i would want * FROM SHOP WHERE SHOP.SHOP_ID = AUDIT.LINK_ID.
But if TYPE = PAGE then the query would be * FROM PAGE WHERE PAGE.PAGE_ID = 'AUDIT.LINK_ID.

---

Complex Query
I have an odd problem with my database. Well, here are the results from the query

Link Tag
--------------------------
www.cool.com cool
www.cool.com Rad
www.cool.com Bad
www.mysite.com Clothes
www.mysite.com Hair
SELECT table1.Link,table2.Tag FROM table1 inner join table2 on table1.id=table2.linkkey

I am trying to print the link and then print all the tags that belong to it and it will go to the next link then print all the tags that belong to that link.

Also, is their a way to use Limit on table1 with this type of query?

---

Need Help With Complex Query
I have a single table with 11000 rows. Table has a field named journals, and one named ISSN. The journals field only contains 1 of 5 possible values: Sociology, Criminal Justice, etc.

I need to find identicals, distinct to one, or distinct to the other, ISSN's when comparing any two sets of journals.

So, for example, if my visitor wants to compare Sociology to Criminal Justice, I select ISSN where journals = Sociology and where journals = Criminal Justice. Now, sometimes they want to know which ISSN's are identical between the two, which are only in Sociology, and which are only in Criminal Justice. I hope that makes sense.

I don't know how to create select statements that will grab the ones I want, and allow me to sort based on any of the fields in the rows.

---

Complex SUM Query
I have the following query, it's working but was just wondering whether there is a way to trim it down/optimize it?

SELECT `order`.*, computer.Price AS Price, price_discount.PDiscount, price_tax.PTax AS VAT, ROUND(SUM(Price-(price_discount.PDiscount/100)*Price) + (((price_tax.PTax)/100)*(Price-(price_discount.PDiscount/100)*Price)),2) AS SubTotal, shipping.SPrice, ROUND(SUM(Price-(price_discount.PDiscount/100)*Price) + (((price_tax.PTax)/100)*(Price-(price_discount.PDiscount/100)*Price) + shipping.SPrice),2) AS Total
FROM `order`, price_discount, computer, price_tax, shipping
WHERE price_tax.PTID = `order`.PTID
AND computer.compID = `order`.compID
AND price_discount.PDID = `order`.PDID
AND shipping.SID = `order`.SID GROUP BY `order`.OrderID

The second SUM (Total) is basically the first sum but adding the shipping.SPrice. I was wondering whether there is a way to save the first SUM, which ive declared AS SubTotal so that for the second one I just type: SubTotal + shipping.SPrice?

---

Complex Query
have used Dreamweaver to develop a site which allows people to apply for free assistance. There are 3 tables - users, apps and advisers. The app table has reference to the ids in the other two tables eg iduser_user, idadviser_adviser.

What I want to do now is use data from all 3 tables. I want to display the application details, user details and the allocated adviser. I am stuck as to how to get this into one record set so any help very gratefully received.

---

Complex Query
I am building a system where I have ratings for file downloads. I was thinking I'd have a table of ratings so that each rating could be stored, and I'd just calculate the total rating by grabbing all of those, summing them and dividing by the total.
My idea is that MySQL can do this all within the query. So I say something like:

SELECT id, title, COUNT(the rows in ratings_table where fileid=id) FROM file_table.

I'd also have to do the adding and division, unless there is an easier way to grab an average across a bunch of numbers pulled. But anyway, the part in bold is the part I have no idea how to write. Any suggestions?
It might be easier to just record how many people have rated it and keep a static number (yes no?).

---

Complex Query
The query deals with two tables. The first table is called 'Reference' and the other is called 'Customer'. There is a foreign key called customer_id in the Reference table which references the Customer table.
So, each of these tables have auto_increment primary key in them which is the tables ID. So there is a 1 to many relationships between the two tables where the Customer can have Many References
What i need to do is the following... I have the reference_id from the reference table. I need to write a query that will first get the customer_id in the Reference table then select all the references that relate to that customer in the Reference table
The only way that i know how to do it would be to first select the customer_id From the Table and then select all the references from the Reference table in a separate table.
Does anyone know how I can get around this with just one query?
r937 the last time I had a question about something MySQL you were the man to sort me out

---

More Complex Select Query
i tried to do this also looking at documentation from mysql site, but keep getting error messages:
basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles.
what i need is a query that selects the articles and orders them according to views/day.
so, i'd need some operation that first figures how many days the articles have been up:
DATEDIFF(NOW(), published_on)
then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day.
any idea how i can accomplish this?

---

Yet Another Complex Php/mysql Query
im trying a select, compare, update sota query thingy 'm' jig..

what i need to do is:

i have a 2 rows in a table(table=users) called gcount and credits

both rows are numeric.

im trying to build a query that

a) updates gcount by gcount = gcount + 1
b) if gcount = 5 reset gcount to 0 update credits = credits+1

so basically what im after is a query that adds 1 to the g count every time but if the gcount reaches 5... it is reset to zero and credits is reset to + 1

is this too much to ask from one query?

am i totally bonkers and you have no idea what i mean?

---

Complex Query Over Two Databases..
Can someone please help me combine these two queries? Basically, there
are 3 columns in the ebluecard table that refer to agent. I need to do
this first query, but filter by the office which is in the agents table
in a different database. Is this possible?

This is the main query:

---

Creating Complex Query
its a booking form. user selects start date, finish date, and room type.
on my table i have roomInfo(roomNo, type, NoOfBeds);
occupancy(roomNo,DateStart,DateFinish,GuestNumber);
i am trying to do something like:
select roomNo from roomInfo where roomNO = single and roomNO not IN ...
cant do subselect its mysql 4.x version... would i have to do an outerjoin/innerjoin?

---

Sort A Complex Query
$query =
"SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` WHERE MATCH (`title`, `descrip`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)
UNION
SELECT page.* FROM `page` LEFT JOIN `url_pages` USING
(`page_id`) WHERE MATCH (`url_pages`.`page_url`)
AGAINST ('$radio_keyword' IN BOOLEAN MODE)";

How do I ORDER BY `page`.`title` for the entire query? ie. I want to
get the whole query sorted by the page.title field, but I don't see a
way to slap on an "ORDER BY" on the whole query.

---

Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link.

I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:

---

How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records
possessing the highest numbered version for that unique file_name. I show
sample data and two trial queries below. Logically I want to use
max(version) as a constraint in a Where Clause. However, the max() function
is not allowed directly in a where clause.

I have contemplated a second table to track the max version for each file
name. I would like to structure the data in an efficient manner for query
performance when the data set grows to many thousands of unique file_name
records with many hundreds of versions each........

---

Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables.

What is the best approach one of these or another?

After generating a candidate 'unique string' the two strategies that came to mind are:

1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or

2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again.

I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.

---

Joins Query
I have a page whereby I list all events and flyers for each event. Because each event can have more than one flyer it's obviously a many-to-many relationship, so I have introduced a new table called event_flyers which has the following fields; event_id, flyer_id. How *should* the query look?

SELECT *
FROM calendar,
flyers
LEFT JOIN calendar_flyers
ON calendar_flyers.flyer_id = flyers.id
WHERE calendar_flyers.calendar_id = calendar_id

---

Relational Query And JOINs
I'm trying to set up a simple invoicing system, and am having trouble figuring out the right query to retrieve data from three tables...

The important fields in the tables are:

Customers: CustomerID, Name, Address etc etc
Orders: OrderID, CustomerID, PaidSoFar
OrderRows: OrderID, ProductID, RowTotal

I need to make a list looking something like this:

---

Design / Query Confusion - Joins?
Sorry for the long post here. I'm trying to give enough info. to explain my situation, and am novice enough to not know how much someone might need to know to help.

I'm very new to SQL, I've read through the Sitepoint "Build Your Own Database Driven Website" book, and though I did follow it, I didn't build the joke database, I just converted it to the specific task I want to do to organize some stuff for my department at work.

Basically what I am doing is tracking design drawings; Job name and number, clients, architects, when drawings leave and return for approvals, to the shop and field, etc... Getting all the information that our detailers might need, in one place.

Where I'm running into problems, is designing the database with some thought on how we set up our job numbers and attempting to keep the tables fairly small by creating tables for each year.

A typical job number would be something like:
B06-064 (B is first initial of the Project Manager - 06 for the year - and 064 for the 64th job that year)

04_jobs, 05_jobs, and 06_jobs all have the same columns:

CREATE TABLE 04_jobs (
job_year YEAR(2) NOT NULL DEFAULT &#3904;',
id SMALLINT(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
job_name VARCHAR(255),
pm_id TINYINT(3),
client_id SMALLINT(4),
arch_id SMALLINT(4),
finished SET(&#390;', &#391;') NOT NULL DEFAULT &#390;',
PRIMARY KEY ( job_year, id)
);

The job_year seems a bit redundant since the tables are named by year- I was thinking the problem with my select query had to do with the duplicate PKs (id) across tables - this may be a good indication of how little I understand everything - I just added this column and changed the PK this morning.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs;

I get rows joined together - each job in each tabled joined on rows.
not what I was expecting. - getting a row for each job - as if the query were on a single table.

When I run a query such as:
SELECT * FROM 04_jobs, 05_jobs WHERE pm_id="1";
I get an error that the clause is ambiguous.

I guess where I'm most confused is not knowing if its:
1. a design flaw
2. a query flaw (everything I've read so far on JOIN seems directed at joining rows - which isn't what I want)
3. Something that is easily handled with a PHP loop (I'm new to PHP too).

I'm doing everything on a command line for now, I haven't written any php for this area of the project.

---

Report Writing Query Problem (joins Again...)
Hola database type folks,

I have been trying to get this right for sometime but now must admit defeat

I have 3 tables

artists -> holds details on the artists
card_gallery -> hold details of card available related to artists via artist_name
scart -> shopping basket related to card_gallery via card_image_id

I am writing a reporting facility that allows the administrator to view all the artists in a table along with their number of cards online and cards sold figures.

Currently I have something like

select
card_gallery.artist_name as cg_an,
count(card_gallery.artist_name) as cg_ca,
count(scart.card_image_id) as s_ci,
from
card_gallery
left join
scart
on
card_gallery.card_gallery_id=scart.card_image_id
group by card_gallery.artist_name
but that doesnt give the correct number of cards sold.
Cards sold are registered in the scart table by setting the processed field to 1

Ideally I would like to be able to view all the artists on one table with the number of cards online and the number sold even if it's 0

---

Any Tool Where You Can Visually Trace Query Joins?
Talking with a colleague, we wondered if there exists a tool where you can build a query and see all the tables it joins visually but soem sort of line or graph.

---

Unable To Do Joins With MySQL Query Browser
I downloaded the Query Browser for Windows. I try to join two tables as described in the manual, drag over the JOIN tables tab but nothing happens. The query remains as is.

---

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

---

Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read.

OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11).

With each shoe it does (or is meant to do) the following:
1. The PHP script that runs the query is looping through every size outside of the query
2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on
3. When it finds the size it is on, it then deducts the correct number of units from the stock table
4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before)

Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not.

Here is the query I have so far (with a little simple PHP around it doing the loop):


PHP

$shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11');
    $numshoesizes = count($shoesizes);
    
    for($i = 1; $i < $numshoesizes; $i++) {
        $stockupdate = "
        UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." =
            (SELECT
                CASE
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12
                THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13
                THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1
                THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2
                THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3
                THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4
                THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5
                THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6
                THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7
                THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8
                THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9
                THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10
                THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price)
                WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11
                THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price)
            FROM items_ordered WHERE items_ordered.StockUpdated = 0)
                
            WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name))
        , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails

        
        // update stock for size $i
        mysql_query($stockupdate);
    }

Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.

---

Natural Joins And Joins With USING
I was wondering if there is a way to make MySQL 5.0.15 ( final release ) able to use natural joins and joins, using old code that worked with 5.0.11 and earlier.

Since I am new to starting mysql and modifying it, more information the better.

---

Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query:

$posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC");

And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are:

rel_id
post_id
category_id

category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio.

So pseudocode would be something like:

SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).

---

How To Write This Select?
I'm having some troubles writing a select. I have the following tables:

theater
-------
- id
- name

movie
-----
- id
- name

showtime
---------
- id
- theater
- movie
- from
- to
- schedule

I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?

---

How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in
my table as a condition.

"select id from employees inner join
translog on employees.emp_id=translog.empid
inner join outofofficedays on
employees.emp_id=outofofficedays.emp_id inner join holidays
where (here is my question: &#55614;&#57159;-04-02'
doesnt exist in the column translog.vtransdate)
and (&#55614;&#57159;-04-02' not between holidays.H_START and H_END)
order by translog.vTransDate,employees.emp_id limit 1"

---

How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot!

Using MySQL triggers, I want to sync content between two tables; I have table “user� and table “users�, each have similar columns “last_name� “first_name� “user_id� …etc, and when a row gets written to table “users�, I wanted that record to be replicated and inserted to “user� (with all the same field info).

How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.

---

Write A Mysql Db Onto A Cd
how to write the db of a mysql on one system onto a cd so as to copy that db to the mysql on another system.
The 2 systems are not connected to each other.

---

Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line.
Can't successfully use "INTO OUTFILE". Getting the following:
mysql> select *
-> into outfile '/users/steves/desktop/test.txt'
-> fields terminated by ','
-> from industry;
ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13)
At the shell, I get this:
steves2ndmac:~ steves$ perror 13
OS error code 13: Permission denied

Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?

---

Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql
table. I can view them all fine but that's about it. I've checked
the user permissions on mysql and have set them all to 'Y' incase that
was the problem but still no joy. Can anyone suggest what the problem
may be? I'm tried the usual RTFM but cant spot the problem

---

Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this:

May 19 20:42:09 2002
7
Services
3.23
4.34
0
None
2.31

Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?

---

How To Write Like Comparison In Procedures?
this sample procedure takes in an username, searches for number of entries matching the pattern of the username.

delimiter $$
create procedure sampleDB.sampleProcedure( IN m_username varchar(30), OUT m_username_matched_count int )
deterministic
begin
if length(m_username) = 0 then
set m_username = 'jason' #set temporary name if empty
end if

set m_username_matched = 0; #finds number of matches for the given username

select
count(*) into m_username_matched #store into the variable to be returned
from
sampleDB.sampleTable
where
userName like '%m_username%' #this is the statement that does not work#

end$$

---

Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to
use the MySQL C API to be able to use a normal sql insert statement
that will send the data or file to a directory and NOT the database.
I'm not sure if there is anything out there that will allow this, but
I've searched everywhere, and I have found no solution.

---

C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I
am using Stata that is written in C API, and I want to write the data
out to MYSQL database. Using ODBC and loading the data in MYSQL from
STATA takes forever. Can anyone please tell me if I can create the myd
files by using the data in memory thru C programming.

---

Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql
table. I can view them all fine but that's about it. I've checked
the user permissions on mysql and have set them all to 'Y' incase that
was the problem but still no joy. Can anyone suggest what the problem
may be? I'm tried the usual RTFM but cant spot the problem

---

MySql Write Vs Read
Essentially I am faced with a design choice;

have an oft run (modifiable) query run every time any user wants to
run it, comparing criteria againt each and every user in the table and
delviering list OR saving that list (user/user/) so the next time all
that needs to be queried is the existence of a record in the table.

The basic tradeoff; version 1 there are many simultaneous queries of
the user db on complex search criteria; version 2 there are fewer but
their are as many far more simple queries to a much larger table, not
to mention many writes to same to keep the table current.

I know this is pretty vague, but I was wondering if in general there
is an area that mysql excels in vis a vis complex query smaller tables
simple query larger tables.

Question 2 is there anyway to guestiate the search/write times of a
user log in to both query and update? #Records, fields, etc? I'd like
to keep log in to < 10 seconds max.

---

Write Conflict Error
I have a database with access as front end and Mysql as back end. I am
gettting the following 'Write Conflict' Error. when i am on my order
form, This form does has a subform where i enter all the products that
are ordered.

Could any one let me know what could cause this problem. I am the only
one using the database as its still in the build stage Code:

---

Slow Write Speeds
Here is what i have

IBM x226 - 8GB ram - 2x 3.4 Ghz Xeon (single core) processors - RAID 5E (6x300GB disks)

Suse Linux 9.0; MySQL 5.0.13

So, I am migrating from SQL server 2000 on a much smaller Dell PowerEdge (2gb ram, 2 Xeon 3.0 Ghz, Raid 5 over 5x74GB). I am exeriencing very slow write times.

For instance, there is one table on both servers that has approx 9mil rows...here is a basic test:

Update loan
set test_col = 1;

Platform Time
IBM/Linux/MySQL 5 9:45
Dell/Win/SQL server 1:37

The tests were run during very light server activity and I tried them multiple times.

Our IT department is fairly insistant that the IBM server is running per spec...obviously something is not right. I have changed some of the server variables with no improvement.

---

Write Once Read Many Field
I am designing a database and one of the requirements my client has is that once data is entered into certain fields it cannot, under any circumstances, be changed. Is it possible to create a write once read many (WORM) field in MySQL directly?

---

Re-write Text File
i'm creating a log file regarding database operations. My problem is i want to append a new record into the log file each time i update my database. Previously I've used the following method to create a file:

SELECT * INTO OUTFILE 'c:/"+fileName[i]+" '
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '
'
FROM "+tableName[i]+";

MY problem is I cannot re-use the file created. Is there any way to call the same file for the purpose of re-use. Or maybe the OUTFILE do not allow file created to be re-use.

---

Create/write To The File
I am working on Solaris and seem to get the following error
Cant create/write to the file '/home/dslab456/temp/tempoutput.lst'
As in windows i cannot specify which drive it is or so ? could anyone help me out with this ?

SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst'
FROM stocklist_vokus s,p_tmpr p
WHERE s.STOCKLIST =p.stocklist_value
ORDER BY p.rid



Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.

---

Vita And Write In MySQL
My web site use a odbc to a MySQL database. I create it on XP and it's worked but I'm now on vita and the data base on a 2003 server. All the reading access on the database are ok but all the writing access are denied. I haven't error message just error 500 on ie7.

---