MYSQL :: Complex Delete

Complex SQL
I'm using MySQL 3.23 (and no they won't let me upgrade).

I have a table which has data for all the actions related to our cases. I need to construct a SQL query to find the most recent action of each type for a certain case.

I've tried the following

select ID, actn_type, date1, result from actions where case_no = "12345"

This of course returned all of the actions. I only want one for each actn_type so I tried

select ID, actn_type, date1, result from actions where case_no = "12345" group by actn_type

This sorta worked in that I now got only one result for each actn_type, but it selected the ID and date1 from the oldest record.

So I tried

select ID, actn_type, date1, result from actions where case_no = "12345" order by date 1 group by actn_type

I got some annoying syntax error, it wants the order by clause after the group by

But when I did that I still got the same results as the previous query. I also tried order by date 1 ASC and DESC but that only changed the order of the results instead of giving me the result I want.

At this point I think I'm on the totally wrong track and might have to just resort to sending a number of queries to the database to get the results I want.

Just to recap, I need a query that will select ID, actn_type, date1, result for a specific case_no where I only get one record for each actn_type that exists and I get the most recent date1, ID, result for each actn_type.

Complex Statement
Hopefully you guys can help me out on this:
the language is PHP, the database is MySQL
Here is my
PHP

function getBounces($start,$end) {
        $profilecode = $this->PROFILECODE;
        $sql = "SELECT DISTINCT(sessionid) FROM `profiles_d` WHERE profilecode='".$profilecode."' AND tstamp >= ".$start." AND tstamp <= ".$end." ORDER BY sessionid ASC";
        $query = mysql_query($sql, $this->DATABASE);
        $bouncers = 0;
        while($rs = mysql_fetch_assoc($query))
        {
            $q2_sql = "SELECT * FROM profiles_d WHERE profilecode='".$profilecode."' AND sessionid='".$rs['sessionid']."' AND tstamp >= ".$start." AND tstamp <= ".$end." ORDER BY tstamp ASC";
            $q2 = mysql_query($q2_sql, $this->DATABASE);
            if(mysql_num_rows($q2)==1)
            {
                $rs2 = mysql_fetch_assoc($q2);
                if($rs2['curpage']=="/index.html")
                {
                    $bouncers++;
                }
            }
        }
        return $bouncers;
    }

Little bit of background.. there is a table called: profiles_d in it is a record for each page hit. it has a sessionid and a page name.

What I need is the number of sessions that only have 1 page hit for a given date. So if viewer 1 had 20 page hits, there would be 20 records in the database. it's part of a analytics system...

From that, I use temp tables and clean it up to 1 record per session....

Is there a way to do that calc in ONE sql statement?

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?

Complex MySQL/PHP
I have a MySQL/PHP project where I need to assign a Red (R) or Blue (B) store coupon to randomly selected contest winners. There are 2 tables (winners, stores). Each store has a region_code and contest winners also have region_code for where they live (both are CHAR(3). Here's where it gets tricky. Each store can only give out 3 coupons (either 3 Red or 3 Blue). Additionally, the winners location (winners.region_code) should be assigned a store coupon from the same region (stores.region_code), until the matches run out, and leftover coupons are assigned to winners that live anywhere. After all this, I'll write the final results to a table.

I can randomly select correct # of winners, and display them in a repeat region just fine. However, I'm unsure about the logic behind assigning data from the stores table to those results, and how to iterate through and 'stop' assigning coupons from each store after 3 coupons. Not sure if this requires some sort of 'view'. Not looking to get out easy, just for some advice on how I should go about this.

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 UPDATE -
Here is code, that runs well on PostgreSQL and MS SQL Server:

update TTable
set summ=(select Count(1) from TTable t2 where t2.mybool=TTable.mybool);

This means that each record (lets name it 'A') in TTable will have field summ filled with the number of the records in this table where the value of mybool field is the same as it is for record A.

MySQL returns error 1093.....

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.

Complex Ordering
I would like to order query results by a numeric column containing positive numbers and zeros at a descending order, except that results with a zero 0 should appear last (e.g.: 2, 10, 15, 16, 0).

Can I do that in a single query, or do I need to use to successive queries or order my results in the programs that receives them before displaying them?

Complex Select
This is something I need to do with one select. Here is my select:

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY MONTH(rec_date)

or

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY LEFT(rec_date,7)

rec_time = time
rec_date = date

as you can see, I'm collecting summary of daily record around midnight and
building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0

BUT! my function call requires a constant-length list. So if any months in
my selection range have no data, I need to fill the space with an empy
value. How can I do this? Perhaps with an helper table, but how?

Complex Index
I am trying to find out if there is any way of creating a complex index on a table.

My data sample is:

a bird can be banded once (coded as '1')
the bird can be retrapped many times (coded as '2')
the bird is recovered dead once (coded as 'X')

How can I create a code that allows codes '1' and 'X' only once, but code '2' infinate times? I currently have a index on Band number, date and code. But this allows multiple code '1's if the dates are different. A uniue index on only the band number and code does not allow multiple code '2's

Seperating the data into different tables is not an option, so currently I am doing the checks via a VB front-end. This is slow and cumbersome. I need a way to do this more efficiently.

Complex Select
This is something I need to do with one select. Here is my select:

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY MONTH(rec_date)

or

SELECT SUM(value1) FROM mytable WHERE rec_time BETWEEN '00:00:00' AND
'00:30:00' AND YEAR(rec_date) = YEAR(NOW()) GROUP BY LEFT(rec_date,7)

rec_time = time
rec_date = date

as you can see, I'm collecting summary of daily record around midnight and
building an monthly array like: 0,0,0,6.5,0,23.7,2,0,0,5.5,0,0
BUT! my function call requires a constant-length list. So if any months in
my selection range have no data, I need to fill the space with an empy
value. How can I do this? Perhaps with an helper table, but how?

Complex Select
I have a table with 6 fields: id, project, section, division, subdivision, title.
Data like:
0 10 1 1 1 'Title111'
1 10 1 1 2 'Title112'
2 10 1 2 1 'Title121'
3 10 1 2 2 'Title122'
4 10 1 3 1 'Title131'
5 15 1 1 1 'Title111'
6 15 1 1 2 'Title112'
7 15 1 1 3 'Title113'
8 15 1 2 1 'Title111'
9 15 1 2 2 'Title112'

As you can see project 10 has:
4 10 1 3 1 'Title131'
and project 15 has:
7 15 1 1 3 'Title113'

And there are 4 records that match section, division, subdivision and title in each project.
1 1 1 'Title111'
1 1 2 'Title112'
1 2 1 'Title121'
1 2 2 'Title122'

I am looking for a select that will give me the following data:
1 1 1 'Title111'
1 1 2 'Title112'
1 1 3 'Title113'
1 2 1 'Title121'
1 2 2 'Title122'
1 3 1 'Title131'

Basically, I want all records from both projects but only 1 of the records that have similar data.

Complex Problem
I have a problem I could use some help with. (I'm new to SQL).

I have a table with 4000+ user details, incuding email addresses. I
need to send out one email per domain except where the domain is an
ISP, or email provider such as hotmail. The best way for me to do
this would be to show all the records where a domain has more than one
email address on it, and then manually go through and delete
superfluous records from the domains which are not ISP's or email
providers.

I need suggestions on how best to tackle this. I've split up the
email column into two seperate columns : username and domain. I've
tried the following :

SELECT count(*) AS num, id, firstname,lastname,username,domain FROM
users
GROUP BY domain HAVING num > 1;

but this only returns one record per domain. I need to return all
records where a domain has more than one email address so I can
selectivly delete the records I don't need.

I'm pretty sure the answer lies in creating a seperate 'domains' table
and setting up a relationship between domains and users, but my
attempts so far have been fruitless.

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 Join
I have a table called users which has primary id 'username', and includes fields called jobpref1, jobpref2, jobpref3, jobpref4, jobpref5. These fields determine a user's rank of job preferences.Values in these columns are id's of another table, 'jobs', which also has a column called 'name'.

So all the jobpref# columns' values correspond to 'jobs' primary key column. What I need is a select query to join the names of the jobs to each of the jobpref#s with a user's username. I'm befuddled as to the proper join I need. I think I could work with a group_concat result as well, if that will accomplish my goal.

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 Record
I'm looking for any insight on how to store a record in MySQL (though it's probably not a very complicated record). Essentially I want to store information about a t-shirt. I only want to have one record for a particular t-shirt, but have all attribute information (size, color, style etc.) associted with that shirt to be in the same one record.
They way I have it now is that the main table (shirt) would end up with several records for the same shirt simply due to the shirt having several colors and sizes. I simply want one record for the shirt regardless of the colors and sizes available.

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?

Complex SQL Statement Construction
am confused how to construct this SQL statement

am using this statement to get what i want

SELECT * FROM table (to get all the records)

SELECT * FROM table WHERE status = Ƈ' (To get all record where status=1)

SELECT * FROM table WHERE status = Ɔ' (To get all record where status=0)

how to combine this statements into one is this possible using MySQL

By the way if itâ€™s possible to do such statements where I can find tutorials on this kind of complex statements constructions.

A Complex Find And Replace
I have a table with a column I need to modify, I think, with a find and replace.

The column contains a number currently with no decimals that I need to convert to a number with a decimal. I need to add a decimal 6 numbers from the right. For instance, 12345678 needs to become 12.345678. All of the numbers have will have at least one number to the left of the decimal, sometimes with 2 or 3 and possibly a negative sign. (They are lat and long coordinates that did not come with a decimal in them.)

Does anyone have any thoughts on how to accomplish this with MySQL or, as a last resort, outside of MySQL?

Two (rather Complex) Problems. Help Needed!
Problem 1

I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem.

When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query:

select
p.id as id,
p.title as title,
p.body as body,
p.reltime as reltime,
p.quota as quota,
count(s1.id) as subcount_ns,
from
packs as p
left outer join
submissions as s1
on s1.pack = p.id
where
s1.state > -1
and p.released = 0
group by
s1.pack
order by
p.id asc;
How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0? I'm not sure how to do this, since I have already joined the submissions table to the packs table.

The ratings table (sub_ratings) looks like this:

id | subid | userid | rating | time
The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5.

--------------------------------------------

Problem 2

For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example:

1,43,65

This would be an example of three users having collaborated on one design.

Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission? I'm looking for something along the lines of php's "explode" function, but in mysql.

Thanks in advance for the help that anyone offers.

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 (for Me) Join Question
This is my first post, as I have reached the end of my brain and could really use some help from those who know what they are doing.

First, I am on MySQL 4.0, so subqueries are out.

Second, my problem goes like this:
I have three tables, a data table, a categories table, and a linkage table. Categories are arranged hierarchically, so the categories table has 'left' and 'right' columns to keep things in line.

Until now, if I know the left and right values of a certain category (for example, 5 and 11, I have been successfully executing queries telling me all the data entries in category 'X' by doing the following:

SELECT DISTINCT data.some_column

FROM data

LEFT JOIN linkage ON linkage.link_data_id = data.id

LEFT JOIN categories ON categories.id = linkage.link_cat_id

WHERE categories.left >= 5 AND categories.left <= 11

As I said, this all works fine, but now I want to be able to find all the data NOT in a certain category. Unfortunately, since each row is currently unrelated, and items can exist in many categories, simply adding NOTs to the WHERE clause just ends up returning basically everything. Another way to think of it is if data is assigned to both the 'round' and 'red' categories, asking for everything that is 'red' works as expected, but asking for everything that is 'not red' still returns the 'red round' object because 'round' satisfies the 'not red'.

Does this make sense to anyone? Am I way off? If not, how do I need to join this tables to successfully exclude the elements I want excluded?

Combining Two Complex Queries
I'm dealing with hierarchical data using the modified adjacency list model. In implementing the ability to move entire subtrees, I've come up with the following two queries:

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:

Complex Search Or Product Name
I am writing search for a shop. The user enter a word or words into a input box and then the presses the search button.
I then want it to search the product list and return the products.
I want the search to search for both words then the first then the second.
Like this.

$query = “SELECT * FROM products WHERE (product_name LIKE ‘$first_word’ AND product_name LIKE ‘$second_word’) OR (product_name LIKE ‘$first_word’ AND product_name NOT LIKE ‘$second_word’) OR (product_name NOT LIKE ‘$first_word’ AND product_name LIKE ‘$second_word’)

Then print it out in that order. I have the query sorted but its not pulling the data off in any particular order.

Creating A Complex Primary Key
I'm creating a 'customers' table for a new version of our ecom store. In order to avoid duplicate unique fields, I'm trying to have MySQL use our existing system for generating customer numbers, rather than having it generate an int PK and then generate this number seperately. Either way, I need to have MySQL generate the information described below.

Our customer numbers consist of two letters, a dash, then one letter and and four alpha-numeric characters EXCEPT the letters o, i and l (to avoid confusion with their numeric look-alikes.)

Examples:

EA-A002S
FB-F39V9
FX-P1U2U

The first two characters are the customer's initials. If only one name is given, we use X for the second character. We start with A0001 and run through AZZZZ then on to B0001. We hope not to run out of customer numbers for a REALLY long time. (When we have 30,000,000 customers with the same initials, we will probably have a different accounting system, or one that can handle more than seven characters -- the dash is irrelevant.)

So here's my problem. I would like to have a field called customer_number. When I run my first insert for this customer, I would like to give MySQL the initials and have it generate the next valid customer number. How can I do this?

Complex Queries On Many-to-Many Relationship
I'm building a system that involves performing fairly complex queries against a set of member data. The member data consists of answers to a series of questions, each question is multiple choice, with the possibility of the user selecting multiple answers to a question. As such, Answers to Member accounts is a many-to-many relationship. My table structure currently looks like this: (with extra, un-related fields removed) Code:

Complex Queries In MySQL
how to do complex queries using MySQL. A potential client needs lots of summarizations and
sub-queries.

Complex Column Names
i have a table with name table1. That table has a column with name thing1/thing2, when i want to do a query like:

select thing1/thing2 from table1;

shows an error sin it interprets as if i wanted the column thing1 divided by the column thing2 (their values).

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.

Complex Limiting In A Join
How do I limit a certain amount of records from a join, ie (quick and idealist ***not for accuracy***)

SELECT * FROM players p, games g LIMIT **first 5 games for each player, but return all players***

is this possible without a subquery?

Complex Calculated Columns
In MSAccess and other databases one can define a view and specify a custom function to calculate a column. How can this be done in MySQL? I can use version 5 with stored procedures etc.
My custom functions are too complex to be placed as expressions directly in the SELECT statement like QTY*PRICE.
Ordinarily I would just calculate these "custom calculated" columns on the fly in the client but I need MySQL to order by them before the result table arrives in the client.
I need a clean implementation. This is not a one off hack but occurs time and time again in various guises in an app that I would like to convert to MySQL.

Complex Select Statement
need help with this statement, that does'nt seem to be working.

PHP Code:

SELECT a.user_id, a.bandname, b.*
FROM gig_users a
LEFT OUTER JOIN gig_album b
ON a.user_id = b.user_id
WHERE a.bandname LIKE 't%' OR bandname LIKE 'u%' OR bandname LIKE 'v%' OR bandname LIKE 'w%' OR bandname LIKE 'x%' OR bandname LIKE 'y%' OR bandname LIKE 'z%'
GROUP BY bandname
ORDER BY bandname ASC

for the end result I need to display all the details from the gig_users table, only if the user_id exisits in the gig_album table, and starts with the letters t,u,v,x,y,z

Although the statement might look ok, it does'nt seem to work.

Complex Select Statement
My database has 43K rows and three columns. The primary key starts at one and counts up. I would like to select a row then use the data for some work and then select the next row.... all till I get to the last row. Could I just select a row at a time in sequential order or do I need to push results into an array to work? I'm using Perl. Should I save a select from my mysql database to an array... is this the best method?
I think I should use the code below but I'm not sure where it goes.
An array is returned that contains the requested names without any further information.

@arr = $sth->fetchrow;

Complex SQL Or Multiple Queries With PHP/MYSQL
I'm no expert when it comes to SQL and I'm creating my most complex site to date. I have a very complex query I need to create and I'm not sure what the best method it so reach my goal. I'm going to try and explain my db structure simply without confusing anyone (or myself)(they are a lot bigger than I'm showing here)....

Complex Searching And Multiple Queries
I have a database of a list of companies and descriptions where users can login and see reports from companies they are subscribed to.

1) I want users to be able to search for a keyword and have it search the ENTIRE database for the keyword and return all rows.

2) For those returned rows, I want to cross-reference the permissions table and have it:
---A) return the company name and description if they are subscribed
---B) print that they are not subscribed to this company.

Here is a simple version of the database:

user
====================
id | username
====================
1 | bob
2 | john

reports
=======================================
id | description | company
=======================================
1 | Green | Crayola
2 | Green | Bic
3 | Blue | Papermate

permissions
===========================
user_id | report_id
===========================
1 | 1
1 | 2
2 | 1
I need to put these two statements together(shown below). I need to have it search the DB and return all rows, then check to see if the person has access to that company. If they do, show it to them, if not print that a company was found but they are not subscribed to it. So if John was logged in and searched for "Green" his results would return two results, one would show that he could see the report from "Crayola" and it would also show that the company "Bic" was found, however he is not subscribed.

Search results:
1) Crayola - click here to see the report
2) Bic - you are not subscribed to this company.

Here is a look at the MySQL I have been trying to use:

PHP

$query1 = "SELECT *
FROM `emt_report`
WHERE `company` LIKE '%$P_search%'
OR `description` LIKE '%$P_search%'
OR `market1` LIKE '%$P_search%'
OR `market2` LIKE '%$P_search%'
OR `market3` LIKE '%$P_search%'
OR `market4` LIKE '%$P_search%'
OR `market5` LIKE '%$P_search%'
OR `market6` LIKE '%$P_search%'
OR `location` LIKE '%$P_search%'
OR `date_year` LIKE '%$P_search%'
OR `date_month` LIKE '%$P_search%'
OR `source` LIKE '%$P_search%'"

PHP

$query2 = "SELECT u.id
     , u.username
     , p.user_id
     , p.report_id
     , r.id
     , r.company
     , r.description
     , r.market1
     , r.market2
     , r.market3
     , r.market4
     , r.market5
     , r.market6
     , r.location
     , r.date_year
     , r.date_month
     , r.source
     , r.video
     , r.audio
     , r.pp
     , r.execsum
     , r.report_url
     , r.exec_url
  FROM user as u
INNER
  JOIN user_reports as p
    ON p.user_id = u.username
INNER
  JOIN emt_report as r
    ON r.id = p.report_id
WHERE u.username = '$username'"

From above, how can i have search by query1 and then have it cross reference query2?

Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table:

CREATE TABLE b5_assignment_lookup (
as_id int(11) NOT NULL auto_increment,
as_blog int(11) NOT NULL default Ɔ',
as_blogger int(11) NOT NULL default Ɔ',
as_milestone enum('start','finish') NOT NULL default 'start',
as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`as_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ;
By running this query, I get the following resultset:

mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= &#55614;&#57158;-09-30'
+--------+----------+-------------+---------------+---------------------+
| as_id | as_blog | as_blogger | as_milestone | as_timestamp |
+--------+----------+-------------+---------------+---------------------+
| 87 | 89 | 41 | start | 2006-05-01 00:00:00 |
|208 | 89 | 41 | finish| 2006-09-02 11:55:27 |
|209 | 89 | 103 | start | 2006-09-02 11:55:27 |
+--------+----------+-------------+---------------+---------------------+
3 rows in set (0.00 sec)
What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41.

What I really need to grok is all bloggers who blogged all or a part of a range of dates.

For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02.

I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25.

The flags are the start/finish, obviously.

Complex (tree Based) Db Design
Firstly, here is the data structure...

Account
User
Site
Notes
Note0
Menus
Menu0
MenuEntry0
MenuEntry1
MenuEntry2
Pages
Page0
Paragraph0
Image0
Paragraph1
etc...
As you can see, the data is essentially a tree structure. Hence I've decided to use an MPTT design to index the data.
Each type of item is stored in a different table, i.e. table_paragraphs, table_images, table_menus, table_menu_entries etc.
My problem is that I'm not sure as to the best way to link the tree nodes to the different tables. Should I include the object type in the tree nodes and use my server side language to extrapolate the query from that or... ?

To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here.
Below is a brief description of the problem.

tablename : test

RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
12004-01-10110
22004-01-20120
32004-01-20270
42004-01-10280
52004-01-15110
62004-01-25310

Output :
RecordId EffectiveDateothertableidvalue
-------- ------------- ------------ -----
22004-01-20120
32004-01-20270
62004-01-25310

Now I want to use a single SQL query to find a result
where there exist one record for each unique
"othertableid" where the record selected for the
"othertableid" should be the recent one with regard to
"EffectiveDate".

That is from the above records, I want to select
Records with "RecordId" = 2 and 3 because they are the
recent one for "othertableid" = 1 and 2 respectively.
Please be sure that I want to retrive all fields
including "RecordId". The result should not depend on
any other fields but "EffectiveDate" only.

I am using MySQL 4.0.12 and it does not support
"SUBQUERIES" which is now given support in latest
MySQL edition. But I have read in the manual of MySQL
that any "SUBQUERY" SQL statement can be written with
proper "JOINS".