MYSQL :: Two (rather Complex) Problems. Help Needed!

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.

Help Needed With DB Structure
I'm finally get a grip on using databases in my scripts, but I'm still lacking a bit in exactly how best to construct my databases ... so I'm hoping that I might be able to get some help with that here

I currently have a MySQL database with one table called "users." In it will be held the registration information for everyone who signs up to the site: username, password, date of registration etc. etc.

Now I want to link that table with another table that will contain information regarding the car that the user drives. This information will be collected from a second form and will contain data such as type, make, year of construction, colour etc. etc.

What's the best way of linking these two tables together in order to help with running searches or scripts in the future? I was planning on inserting the relevant username into the second table as well so that I can run searches across both tables ... but is that the best way of doing it?

MYSQL Help Needed
I am having troubles trying to create my first table in mysql

I am using version 5.0

I have entered the code below in mysql:

mysql>CREATE TABLE table_name (
-> column_1_name column_1_type column_1_details,
-> column_2_name column_2_type column_2_details,
-> ...
->);

and i get the following error:

ERROR 1064 (42000): You have an error in your SQL syntax;check the manual that corresponds to your Mysql server version for the right syntax to use near 'column_1_type column_1_details,column_2_name column_2_type column_2_details,' at line 2

Syntax Needed
I have a table consisting of word_nr (key) and example_nr. Now i want to find out how many entries for example_nr are returned where word_nr=x and word_nr=y.
So i made something like:

SELECT count(*)
FROM mytable WHERE word_nr=x and word_nr=y

Query Needed
i have a little problem with figuring out how to make a query for a certain table. The table consists of two columns: word_no and example_no. It is very likely that an entry word_no refers to more than one entries in example_nr. Now i would like to find out how many entries with the same example_nr are there for word_nr x AND word_nr y together.

Timezone Help Needed
I am in Washington State, Pacific timezone. However, our business day runs from 7:00am-7:00am. I have found if I change the system time on the MySQL box to the Japanese Timezone this works in "fooling" the system into thinking 7:00am local time is midnight, the start/end of a business day.. the problem comes when trying to update our intranet website, with the differing timezones..the huge difference in time will not allow updates with our ppl who use front page.
(yes, I know notepad would be a better choice then front page. Not my call)

I have tried unsuccessfully to change the time zone in the [mysqld] & [mysqld_safe] sections of the startup INI file. When I try to force MYsql into the japan time zone the server fails to start and an error message pops up failed to start unexpectedly,

Performance Needed
I have two tables. One for products and one for reviews.
I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far.
I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server?
Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?

SQL Statement Needed
How would I change in a table every reference to "ren" to "stimpy"?
If a value equaled "renTop" I want it changed to read "stimpyTop".

Help Needed Writing Query
I'm trying to make a query to return 10 sorted values, but I need to calculate the value to sort by, ie its not stored as a field in the table.
So say I had some data like this...

id basicrank time
1 5 40
2 7 33
3 3 55
etc.

I want to sort by (basicrank minus (currenttime - time)), so newer records get some priority, returning highest score first.
My query could then be something like this, given current time of 60.
SELECT id FROM table ORDER BY (basicrank-(60-time)) DESC LIMIT 10

Wordpress Database Help Needed!
I'm trying to import some content into the wp_posts database, which works, but I can't figure out how to make it automatically publish at a later date.

When I publish the imported content manually (eg. edit timestamp and hit publish) it works, but when I try and upload that into the database, it doesn't seem to register.

I'm doing some traveling soon and I don't want the blog to lay dormant! Here's my SQL for an example post:

Quote:

INSERT INTOwp_posts(ID,post_author,post_date,post_date_gmt,post_content,post_title,po st_category,post_excerpt,post_status,comment_status,ping_status,post_password, post_name,to_ping,pinged,post_modified,post_modified_gmt,post_content_filtered ,post_parent,guid,menu_order,post_type,post_mime_type,comment_count`) VALUES
(123, 1, &#55614;&#57159;-11-21 17:45:39', &#55614;&#57159;-11-21 23:31:15', 'I AM THE POST CONTENT', 'THIS IS THE POST TITLE', 19, '', 'future', 'open', 'open', '', 'this-is-the-post-slug', '', '', &#55614;&#57159;-12-01 17:45:39', &#55614;&#57159;-12-01 23:31:15', '', 0, '', 0, 'post', '', 0);`

So all times are set in the future, and the post_status is "future". Why is it not publishing when the time comes around?

Stored Procedure Needed
I have a table named players with these fields:

playerid {int}
teamid {int}
jersey {int}
playername {varchar(50)}
age {int}
And a table named pitchcounts with these fields:

id {int}
gamenum {int}
playerid {int}
pitchcount {int}
Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed
and the days rest required and next availability date of the pitcher based on the criteria below:

In-House Ages 13-16 (Babe Ruth)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-95 3 Rest

********************************************************
In-House Ages 11 & 12 (Majors-American, National & Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-85 3 Rest
No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week.

********************************************************
Travel Ages 8, 9, 10, 11 & 12
--------------------------------------------------------
Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House.
If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play.

********************************************************
In-House Ages 10 and 9'S in Majors (Majors American, National, Continental)
--------------------------------------------------------
Pitches Days Rest
-------------------------------
0-20 0 Rest
21-40 1 Rest
41-60 2 Rest
61-75 3 Rest
********************************************************
Double A In-House (AA)
--------------------------------------------------------
No more than 40 pitches in a game or 80 for a week.

********************************************************
Additional In-House Notes
--------------------------------------------------------
If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch.

Rest Examples:

if 20 or less on Monday, available on Tuesday
if 21-40 on Monday, available on Wednesday
if 41-60 on Monday, available on Thursday
if 61 or more on Monday, available on Friday

SQL SELECT Statement Help Needed
I have three databases, like so:

1. Visitors: vid(int) | vname(varchar)
2. Events: eid(int) | ename(varchar)
3. Participation: pid(int) | visitor(int from vid) | event(int from eid)

The participation table is a criss cross reference table to associate visitors with events.

Given the vid(from visitor table) how go i query the database to get all the events a given visitor is participating in?

M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each.

Stories
* story_id
* story_date

Editions
* edition_id
* publication_id

Story_Edition_Jct
* story_id
* edition_id

What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition.

Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication.

The following query is what I've come up with:

MySQL Join Needed
I'm pretty new to MySQL and I don't really get the whole join thing, although I understand the theory behind it I have problems when putting it into practice. Basically I'm using PHP with MySQL back end and I've got two tables:

TEST
QUESTION

The primary key of Test is "testno" which is an auto_increment and question has "testno" as a foreign key, so I need a join that will insert the value of "testno" into question when ever a record is entered into TEST based on the "testname" which the user enters into a form.

Let me try and clarify:

$testname = $_POST['testname'];
sql="INSERT into question test.testno where testname = '$testname'";

Obviously this will not work but what I need in effect is a query which carries out this function.

Packing Needed For MySQL
I am still playing around with databases. So here it goes:

Is packing needed for MySQL (5.0)?

I have a new DB, which already is 178 MB. Will that continue to grow,
when I add and delete data? Does it reclaim freed space and deleted
rows?

If not, is there a (automated) way to pack tables using SQL or
something simple, which I can call from my app?

Select Statement Needed
The sending page allows the user to choose which search criteria he
wants, so this (and many other) pieces of the select statement may or
may not be present. The statement always starts with

SELECT * from logdata where driver = '$_COOKIE[username]'

If the user has selected them and supplied the proper data for a search,
other elements will be added to the query string. For example, it may
look like

SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >=
$_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant
regexp???

I'm not even sure if that is where a regexp belongs or if that is how it
should be used.
Ultimately, I would prefer it be non case sensitive.
Here are some examples of what I want:

If the user string is "CHIL"
Chili'strue
Charlie'sfalse
CHILtrue
chfalse

Simple Code Needed
Just need a little code to transfer a user to a custom error page if connection to the database fails.
Like:
If(no connect){
header("location: error.php");
}

Just need the "no connect" part I guess.

Lemans Terms Needed
So can someone tell me in lemans terms what does LEFT JOIN do in MySQL? I see it more and more and have read the manuals but still don't understand. Maybe it's a mental block going on.

UPDATE / REPLACE - Assistance Needed
I have a table called COMPANY which holds 5,000 records
Fields: Company_ID (int:2), Name (varchar:100), State_2letter (varchar:2)
i.e. 1, ABC Company, AL
I have another table called STATE (51 records)
Fields: State_ID (int:2), State_2letter(varchar:2), State_Name (varchar:100)
i.e. 1, AL, Alabama
I want to change the structure of the COMPANY table to hold the STATE's table STATE_ID record to correspond with the State_2letter. But in the same time change the STATE_2letter to be an INT format too.
So, my COMPANY table will look like this...
i.e. 1, ABC Company, 1
I just cant find my solution on how to to an UPDATE command correctly.

Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations.

First, here's the situation.

I want to store information about all the cards in a Tarot deck.

In the deck, there are two types of cards; namely the major and minor arcana.

Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type.

For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table.

Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign.

Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters.

Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification.

So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.

Help Needed Merging 2 Different But Similar Tables
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there?

What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are:

id - title- body

IN other words, just the bare essentials.

The target files has a table called jos_content with columns:
id - title - title_alias - introtext - fulltext in that order and others but are not important.

I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time!

It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.

Needed Help With Uninstalling MySQL 5 On Mac/Unix
I have MySQL 5 on my Mac 10.4 and I want to completely uninstall it because I think I have MySQL 4 running on it too. I'm not too sure how to check for this. I'm not the best with administrating a database.

Help Needed For Inserting Using Variables In A Loop
im trying to dod a simple insert into my database but the thing is instead of having stable variables i need to input balues from within a loop.

heres the code but i cant figure out whats wrong with it: :mad:
<?
include "login.inc";
$i=1;
while ($i < $num):
$query="insert into A(B,C,D) values('$E$i','$F','$G$i'";
$result = MYSQL_QUERY($query);
$i++;
endwhile;
print("thank you");
?>

so wot should happen is E1, F, and G1 are entered into the database the first time round and then E2, F, G2 and so on until i=$num.

Help Needed For Query Involving JOIN
I have two tables populated during the use of an application to log
user events and application states. They are named "EventTable" and
"StateTable" and the structures follow:

Backups Of Raw Db Files - Indexes Needed?
Right now I am backing up mysql 4.0.14 on Windows2000 with
a scheduled batch file that shuts down the mysql service - copies
the entire data directory to a ackup directory, and restarts the
mysql service. this ackup directory then goes onto tape a couple
of hours later via veritas remote backup agent. I have tested a
restore of this backup method and it works fine. Now I would like
to add some more indexes to a table that has 11 million rows, however
the index files will be very large.

Is it necessary to backup index files? In case a restore was needed, I could
just recreate the indexes manually (via a batch file with the correct index
commands).

Will mysql choke if the table definition shows an index - but the index file is
missing? are there any "bad things" that will happen if index files are missing?

Libmysqlclient.so.10 Is Needed By (installed) Mod_auth_mysql-1.11-12
Redhat 9.0
Mysql 3.23.56 ==> Running

I want to upgarde to 4.0.13 but this is the error it says:

[root@localhost downloads]# rpm -Uvh MySQL-server-4.0.13-0.i386.rpm
warning: MySQL-server-4.0.13-0.i386.rpm: V3 DSA signature: NOKEY, key ID
5072e1f5

error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-12
libmysqlclient.so.10 is needed by (installed)
perl-DBD-MySQL-2.1021-3
libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2

If i install with -i will it install with the backward compatibility, it
should not break those dependencies.

Backups Of Raw Db Files - Indexes Needed?
Right now I am backing up mysql 4.0.14 on Windows2000 with
a scheduled batch file that shuts down the mysql service - copies
the entire data directory to a ackup directory, and restarts the
mysql service. this ackup directory then goes onto tape a couple
of hours later via veritas remote backup agent. I have tested a
restore of this backup method and it works fine. Now I would like
to add some more indexes to a table that has 11 million rows, however
the index files will be very large.

Is it necessary to backup index files? In case a restore was needed, I could
just recreate the indexes manually (via a batch file with the correct index
commands).

Will mysql choke if the table definition shows an index - but the index file is
missing?

are there any "bad things" that will happen if index files are missing?

Whats Needed On Client PC And Server?
I'm currently using MySQL 5.0.9 with Visual C++ 6.0 and Qt. My project is on the server, with all the dll's needed as well as MySQL with my database. I can only access my database on the server from a client station if this client station also has MySQL installed. It doesn't even execute the queries in my code.

Do I need to use server scripts and if so how do I do it? Is there in Windows something like a mysql-server and mysql-client package? Do we need to install MySQL on all of the client stations?

MYSQL Query Speed + Explanations Needed
Sometime I need to delete results set of the some query.
Like
DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...)

But when I have a table with 100k+ records it become a big problem. Script just hangs up forever
As I understand, mysql get each id from table `table` and reexamine SELECT?

I tried to create temprary table and filled it with select
like DELETE FROM table WHERE id IN (SELECT id FROM `some_temp_table`)

But its hangs up too...
EXPLAIN do not works for the DELETE ... statement, manual do not helped.
So, how does this query work "on the low level"?
DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...)

Any suggestion how can I improve speed for the large tables?

VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table.

SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name

Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria?

$query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";

Help Needed With Load Data Infile Syntax
I'm relatively new to MySQL, and I've never used the Load Data Infile command. I'm having trouble getting it to work correctly. I have a comma separated file generated as output by another program that I can't seem to parse correctly. It contains data similar to the following:

VAL1, LASTNAME, FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3
VAL2, "LASTNAME, JR.", FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3

The "LASTNAME, JR." hoses up the import. It imports "LASTNAME into one field and JR." into another.

Manual Cascade Delete Implementation Needed
I am trying to manual clean up a child table when parent table records are deleted.

I tried:

DELETE FROM agencyImage WHERE imageID IN
(SELECT images.imageID FROM images LEFT OUTER JOIN agencyImage ON
(images.imageID=agencyImage.imageID) WHERE agencyImage.imageID is null)

This failed because you can't delete from a table that you are looking at. So how do I delete the record from agencyImage table if no record exist in the parent images table?

Advice Needed Login/registration System With MySQL 5/PHP
I want to setup a login/registration system for my website with MySQL 5 / PHP. Most of the job should be the standard stuff: register form, login form, forgot password form, etc.

Can anyone advice me how to begin ?

Are there any good tutorials which i should use or are there freeware or cheap commercial tools which i should use ? Or maybe you recommend me to an awesome programmer you know who does this job at low cost ?

- a MySQL/PHP Newbie

Help Needed Fetching Data From Table (string Matching)
I have a table "officers" table two with 4 fields all with variable char which will end up just being names. However, I need to create a report that counts each the occurance of each name and ouputs the name with the count next to it. The names in table "officers" are concatinated from a first name field and last name field in a separate table "persons";

I need some help figuring out how to write this using PHP.

Complex Using Like
I have a bad situation in using like
i wana make a like statment that get the all records which start with char. or number , A-Z and 0-9..

do I have to write 36 or in my query or there is another way ???

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.