MYSQL :: REQ: Help With M:N Cross Referenced Tables Requested

How To Find Records That Are NOT Referenced In A Join Table?
Table structure...

CREATE TABLE category
(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
INSERT INTO category (id, name) VALUES
(1, 'CSS'),
(2, 'HTML'),
(3, 'XML'),
(4, 'Javascript'),
(5, 'PERL');

CREATE TABLE category_j_article
(
category_id TINYINT UNSIGNED NOT NULL,
article_id INT UNSIGNED NOT NULL,
PRIMARY KEY (category_id, article_id)
);

CREATE TABLE article
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);

Let's say I want to find all articles that aren't filed under multiple categories? For example, if I want to find articles that aren't filed under the CSS and HTML categories?

Advice Requested
I'm going to be creating a signup form with the standard fields (fname, lname, email, etc), plus user definable fields. Currently, I'm kind of stuck on how to go about doing this.

My users table has an id field (PK), email field, IP address field, etc. I want to give the site admins more control over what they can put into their signup forms. Some people may want to have a website field, others may want to have a field for the user's fav book. Whatever.

Now, if I put these into the users table, I'm now fixed at how many fields I can have, correct? I mean, I can set a determined number of fields and call them custom1, custom2, etc. and let the admin define their user-readable names, but it's still a fixed amount of fields.

I could create a new table with 2 fields - name and value - where name is the PK. That way the admin could enter as many as they want. But how would I then have the same fields in the users table to hold the data?

Sellers Without Referenced Property Should Also Appear (was "SQL Query Help")
I have a query that joins 3 tables, one holding property details, one holding sellers details, and one holding an activity log of every operation performed.

The query to list the sellers looks like this

SELECT Agents_Prop_Code, sell_t.Sellers_ID, Seller_1_Prename, Seller_1_Surname, Address_Town, Tel_No, Mob_No, Seller_2_Prename, Seller_2_Surname, Email_Add, prop_t.Prop_ID, Address_Town, MAX(activity_t.Date) AS lastcomm

FROM sell_t

INNER JOIN prop_t
ON sell_t.Sellers_ID = prop_t.Sellers_ID
AND sell_t.Agency_Code =Ƈ'
AND sell_t.Deleted = 'N'
AND prop_t.Deleted = 'N'

LEFT JOIN activity_t
ON Buy_Sell = 'S'
AND Ref_No = sell_t.Sellers_ID

GROUP BY prop_t.Agents_Prop_Code
;
However I have a problem in that if a seller has not had a property referenced to them they dont appear on the list.

I think its down to the part that does

....
INNER JOIN prop_t
ON sell_t.Sellers_ID = prop_t.Sellers_ID
....
But cant find a way forward.

Table/Query Guidance Requested
I want to build two tables from which I will pull information.

One table (roster_table) contains names of employees.
One table (groups_table) contains names of groups within a department

roster_table:
staffID (unique identifier for an employee)
staffLast (last name)
staffFirst (first name)
groupID (a unique groupID from the groups_table)

groups_table
groupID (unique identifier for a group)
groupName (the name of the group)

One employee may belong to many groups, but I don't know how to structure my roster table such that I can show that in a single field (in other words, not have multiple groupID fields in the roster table).

How would one go about structuring the roster_table so that this can be accomplished?

I ultimately want to pull a roster of employees, and show all groups to which the employee may belong.

Below is an example I tried, but it did not work. I'm guessing it is because I only place a single groupID value in the roster.groupID field as noted above.

SELECT
roster.staffID,
roster.staffFirst,
groups.groupName
FROM
roster_table roster, groups_table groups
WHERE
roster.groupID = groups.groupID
ORDER BY
groups.groupName, roster.staffLast

(To be honest I cannot even say if this is a design problem or a query problem.)

Assitance Requested On Syntax With Joins (outer)
Select d.* from
( select agent_addr, max(date_time) from eventlog where priority = 1
group by agent_addr) d,
(select agent_addr, max(date_time) from eventlog where priority = 6
group by agent_addr) u
where d.agent_addr = u.agent_addr
and d.access_time >= u.access_time

The above statement is to get certain data from an event table. I need
all of the lines where the event is 1 and there are no newer event 1's
or event 6's

I need to know what devices are currently down (Priority 1) based on
this history table. where there is no newer event 1 entries for the
agent_addr, or there is no newer priority 6's for that agent_addr
(priority 6 means device is back up)

I'm stumped on the syntax to do joins, or to get this to work.

the table works like the following
device goes down entry is made as priority 1 eventid 1
device comes back up new endtry is made priority 6 eventid 2
device goes back down another entry is made as priority 1. eventid 3

I need to make sure i only am getting a list of the latest status of
each device in the aformentioned scenario it would be the one with
event ID 3

ODBC Driver Does Not Support The Requested Properties
I'm trying to log data to a certain schema on MySQL 5 server via DSN. MySQL connector/ODBC driver version is 3.51., running in Win XP.

*Sometimes* I receive the following error:

---
An error occurred calling 'LogResults' in 'DBLog' of 'DBLog 1.0 Type Library'
An error occurred executing a statement.
Schema: My Recordset.
Statement: My_Table.
ODBC driver does not support the requested properties.
Description: ODBC driver does not support the requested properties.
Number: -2147217887
NativeError: 0
SQLState:
Reported by: Microsoft OLE DB Provider for ODBC Drivers
Source: DBLog
---

The worst thing is that sometimes everything seem to be ok but no data is logged to database! This case is very confusing, the logging has been successfull before and I've not been able to trace this. I've checked the basics (SQL syntax, schema priviledges, ...).

Client Does Not Support Authentication Protocol Requested By Server
i have this message when i try to use 'mysql_connect' command in php. error message :

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:program filesapache groupapachehtdocs7mysql_test.php on line 2

Many To Many Cross Reference
I am looking to pick up the relationship between items in a cross-reference table. For example, searching for c OR f (bracketed below to highlight them), I want to list the other right hand side keys that are linked by the left hand side key. So in the below case, "a" is linked 3 times due to (c), b is linked twice due to (c), d is linked
twice due to (c) and (c OR f) and finally e, linked once due to (c).

1 | a
1 | b
1 | (c)
1 | d
2 | a
2 | b
2 | (c)
3 | z
4 | a
4 | (c)
5 | (c)
5 | e
6 | (f)
6 | (c)
6 | d

Just wondering what the best way of doing this is, and how the result can be ranked by the number of hits (so a,b,d,e) and how to get the number of links (3, 2, 2, 1)

Cross Reference
I have a cross reference table with two ids like such:

mappings
dealerId -> int
categoryId -> int

is there any way to quickly select all dealerIds that appear in all the specified categories. Something like this:

SELECT dealerId WHERE categoryId IN ( 14,64,23 ) GROUP BY dealerId

Except, above will return all dealerId's that appear in any category instead of all categories.

Cross Referencing
I have a situation where I have 3 tables, we'll call them OrderInfo, OrderContents and Inventory. All of a customers info as far as name, address etc are stored in OrderInfo and each one has a unique Id. In OrderContents, the contents of each order are stored and there is a column called OutgoingId that has he Id of the customer from the OrderInfo table to specify which order these parts go to. Then each of these parts also have a PartId which specifies which part in the Inventory table they are.
What I need to do is go through the tables and figure out which customers in the OrderInfo table have ordered a particular part, say a part with PartId 28 and return results that give me info that is available in the OrderInfo table itself such as the Company Name, Order Date etc. I figured that I would need to somehow use a Join but I have no idea where to begin.

#1251 - Client Does Not Support Authentication Protocol Requested By Server; ...
I am trying to get phpmyadmin working with mysql but I get the below errors...
Why is this??

"phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server."

and...

" #1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client "

changes in config.inc.php file:

$cfg['PmaAbsoluteUri'] = 'http://localhost/phpmyadmin/'

$cfg['Servers'][$i]['auth_type'] = 'config'
$cfg['Servers'][$i]['user'] = 'root'
$cfg['Servers'][$i]['password'] = 'pword'

Any ideas if I am missing something?

ERROR 1251: Client Does Not Support Authentication Protocol Requested
I have a problem with a MySQL environment.

- One server (Windows)
- Two instances of mysql (4.0.27 & 5.x.x)
- Each instance are running as a service with a different port number (tcp 3307/3306)
- Both services are set up with their own parameter files, so they point to the correct binary and data directory

When I try to log on through the console against the MySQL 5 database by starting c:program filesmysql5inmysql, then everything is working as it should.

If I try to do the same against the Mysql 4 database, it returns an error that the client does not support the authentication protocol requested.

Cross Dependency In On Clauses
I'm getting the following error message when executing my query:

Cross dependency found in OUTER JOIN; examine your ON conditions

Any idea how to solve this? Below is a link to the query.

Cross-table Arithmetics
First of all, let's delimeter our tables:

Table Sales (id, name, date, price)
Table Payings (id, id_sale, date, amount)

In a SQL query, I need to know the debt of each sale.

That means : price_of_the_sale - ( amount of all payings related to that sale ).

It this possible?

Farewell!

Cross-table Query
I'm updating an old query that will return a random selected song from a radio stations playlist. After the orignal query was implemented the queue policy has changed and the query should no longer return songs from an artist that is already in the queue and waiting to be played.

The current query (simplified):

Problem With The Cross Joins
I have a problem with the cross joins.
let say that i have 3 tables as shown below:
cat_tbl (cat_id, cat_name);
books_tbl(book_id, book_tittle, book_price);
cat_books_tbl(cat_books_id, cat_id, book_id);
and a table named authors
authors_tbl(author_id, book_id, author_name);

when i make the joins between them brings me a lot of records or what is named a cross join.

Complicated Cross-tab Query
I want to get total sales and number of orders per date for a date range separated by payment type and ordered by date.

Here's the table layout:
ORDER
order_id
payment_id
total
...

MENU_ITEM
menu_item_id
order_id
menu_id
..

MENU
menu_id
cost

Here's my query that does get everything I want EXCEPT the number of orders per payment type - like how many orders were made with cash and credit:

SELECT DATE(o.date) AS dt,COUNT(DISTINCT o.order_id) as ct,
ROUND(SUM(o.order_total),2) AS total,
ROUND(SUM(IF(o.payment_id = 24, o.total, 0) ) ,2) as cash,
ROUND(SUM(IF(o.payment_id = 87, o.total, 0) ) ,2) as credit,
FROM t_order o, t_order_item i
WHERE o.order_id = i.order_id
AND o.date>񟭇-08-01'
AND o.date<񟭇-08-03'
GROUP BY DATE(o.date)

When I try adding this to the select portion:
COUNT(IF(o.payment_id = 24, 1, 0)) as cash_orders,

Aand I have tried:
SUM(IF(o.payment_id = 24, 1, NULL)) as cash_orders,

cash_orders turns out to be more than the total number of orders. I have also tried a subquery in the select portion and did not work properly.

Cross Tab Queries In MySQL
I have a simple CrossTab query that runs fine in MSAccess 2000 which is

TRANSFORM Count(CRId) AS CountOfCRId
SELECT Month(DateReceived) AS TheMonth,
Count(CRId) AS TotalOfCRId
FROM tblCorrReg
GROUP BY Month(DateReceived)
PIVOT CorrType;

tblCorrReg is the table
CRId is the Prime key
DateReceived is of type Date
CorrType is of type SmallInt and currently can be [Null | 1 | 2 | 3 | 4 ]

The above causes a syntax error when I use it as the rst.Open " .. .. SQL string to open a recordset on the MySQL database.

I've now ported the data to a mySQL database but I still need to run the CrossTab query in the Access Front End.

Adding An Unrealted Column To A Cross
I have crosstab query that uses a where clause to limit the data retrieved. Is there a way to add an additional column to the crosstab that isn't limited by the where clause?
Ideally I would like the results of this query as a column in the main query: Code:

Mysql Sql History(cross Session)
I have found many docs to see is there any way to figure out how can
mysql store the typed words or sql(whatever it is a valid sql or not)
so that we can just use "arrow up" or "arrow down"
easily to move to the previous sql or even some sqls typed yesterday or
in different sessions(such as by putty/konsole) that is already logged
out ?

Does anyone have any clues about this ? I think that it should be done
by setting some mysql server vairables but i didn't see any mentioned in the mysql
docs.

Cross Table Query Fails
I get this error which I can't quite understand....PHP Code:

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 'select x10_channel from devices where device = 'Lamp')' at line

The line mentioned involves a cross table query:PHP Code:

$sql = "select commandID from commands where x10_channel = (select x10_channel from devices where device = '$device')";

which works perfectly on a friends laptop. I'm guessing there is a configuration difference between our installations.

Cross Join Effects With Query Optimization
ELECT count(t.id)
FROM `ll_textlink` AS t, `ll_period` AS p , `ll_user` AS ua , `ll_user` AS up
WHERE
t.actual_period = p.id
AND p.status_approval_publisher = Ƈ'
AND p.value > Ɔ'
AND t.online >= Ƌ'
AND Ƈ' >= (
SELECT count(p.id)
FROM `ll_period` AS p
WHERE p.textlink = t.id
)
AND t.online <> Ɗ'
AND t.current_value > Ɔ'
AND t.advertiser = ua.email
AND t.publisher = up.email
AND ua.rights = Ɔ'
AND up.rights = Ɔ' ;
This is what I am at now:

SELECT count(t.id)
FROM `ll_textlink` AS t
INNER JOIN `ll_period` AS p ON (p.textlink = t.id)
INNER JOIN `ll_user` AS ua ON (ua.email = t.advertiser)
INNER JOIN `ll_user` AS up ON (up.email = t.publisher)
WHERE
t.actual_period = p.id
AND p.status_approval_publisher = Ƈ'
AND p.value > Ɔ'
AND t.online >= Ƌ'
AND t.online <> Ɗ'
AND t.current_value > Ɔ'
AND ua.rights = Ɔ'
AND up.rights = Ɔ' ;
However, the number return is quite a bit higher than the from the first query. Are there any cross-join effects and if so how can I avoid them?

Cross Table Search For Orphaned Records
I have two tables in a databases. The tables are:

orders
devices

Orders table has a primary key order_id which auto-incs. Devices points to a single order via a field, associated_order_id which is an int.

I am trying to execute a query to return all devices that have an associated_order_id which doesnt acutally exist as a valid order_id in orders. I.E. Orphaned devices with no parent to an order.

Here is what I was thinking, but IT DOES NOT WORK. It returns thousands upon thousands of results.

SELECT devices.* FROM devices, orders WHERE devices.associated_order_id != orders.order_id

Creating / Managing Database(s) Cross Platform
Is it possible to create / edit database(s) in my iBook laptop and copy the database files straight back to the Windows 2000 server? Coz I wanna do the design and testings in my laptop and 'migrate' it to the server once it's finsihed debugging.

Error Code : 1044 Cross Database Query
I am using a query editor UI and I am connected to two database and trying to write a query from within one database connection that joins both databases and am receiving the error;

Error Code : 1044
Access denied for user 'user1'@'localhost' to database 'test2'

The query looks something like this where I am in the test1 database UI and trying to included data from the test2 database.
select *
from test1
where test1.id in (select test2.id from test2)

I'm assuming I am having a syntax issue but I cannot find the proper syntax.

Return Result Set From Procedure 5.0- Cross Post With Internals
I was looking into returning a result set from a MYSQL 5.0 stored
procedure to php. Unfortunately I do not know what type to return nor
can I find any types that would allow me to do this. Is this possible
under the 5.0 implementation?

Correct Table Structure To Cross Sell Products
I have implemented an e-commerce system which is currently being used in about 10 different websites. I would like to develop to second phase to it by implementing cross sell/up sell so that I can display related products to the customer when viewing one item.

The way I’m planning on forming the relationship between two liked products is by creating a table that with the following fields:

Create table cross_sell…{
Id int..,
Product_id int.., - this will hold the ID of the source product I want to compare
Related_product_id int – this will hold the ID of the product that is similar to Product_id
}

I’m not so sure if this is the industry standard for creating cross-sell/up-sell relation, so I would greatly appreciate it if you provide me with some input.

Remote Access Not Working: Error 1251 Client Does Not Support Authentication Protocol Requested By Server
I'm trying to set up my MySQL database for remote access. I've never been a DB admin so this is all mostly new to me. I am binding to my public IP address, and I have created a new user with the following grant commands run from the mysql command line on the server:

mysql> grant all privileges on prs_data.* to 'username'@'%' identified by 'change';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on prs_data.* to 'username'@'localhost' identified by 'change';
Query OK, 0 rows affected (0.01 sec)

When I try to connect remotely from another host, here's what happens:

Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client In
I have recently upgraded to MySQL 5. Got this error message in my IE Browser. Have read some of the comments on other forums and my choices are: Upgrade all client programs to use a 4.1.1 or newer client library, or

SET PASSWORD FOR
-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd')

Can someone please advise?

Mysql_connect(): Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client
Basically I am setting up php,mysql and apache to run an application. While trying to connect I get the following error:

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in c:sudhirapachehtdocssvgmap est4.php on line 14
Cannot connect to the database.
Client does not support authentication protocol requested by server; consider upgrading MySQL client
I have tried the following message posted earlier by many of you guys i.e
"SET PASSWORD FOR peter@localhost = OLD_PASSWORD('something')
flush privileges

I am trying this from a long time and do not find a way to fix this problem
I request you guys to help me in soughting this out and I appreciate your patience in reading my post.

Mysql_connect(): Client Does Not Support Authentication Protocol Requested By Server; Consider Upgrading MySQL Client
I am having an awkward problem with my MySQL installation. I have a number of scripts that access the database from a few different web hosting sites. I have no problem accessing the database from the scripts that I host locally. The scripts that are on an outside server accessing the database gives me a mysql_connect() error.

Locally I use MySQL 5.0 and PHP 5.0. I am assuming that the 3rd party web host uses an older version of PHP and thinking that may be the problem. Is this the possible problem and if so is there a way to fix it without ruining the scripts that are currently working?

"Unable To Select Requested Database."
i currenty run a game. Well just adding flies now and sorting it out etc..... but when i go to like online.php or anyother flie that the DB runs off.. i get "Unable to select requested database."

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

Multiple Smaller Tables V.s. Fewer Bigger Tables
What is the best way to store data in a database? Multiple smaller tables (which means many Inner/Left Joins when fetching the information) or fewer bigger tables (which means few or no Inner/Left Joins when fetching information).

InnoDB Tables And MyISAM Tables In ONE Database?
My application demands some advantages from InnoDB and some from MyISAM.

Is it attainable to have both InnoDB tables and MyISAM tables in ONE database?
Or else, getting views from two databases, one from InnoDB and the other from MyISAM?

If not possible, any other approaches will do the job?

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6

How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

100 Databases, 4 Tables Each, Or 1 Database, 400 Tables
I was able to build, tweak and do whatever necessary to establish a rather different project but am worried that it may sooner or later crash due to idiotic programming / database usage.

In essense I took 1 database with 4 tables and duplicate it 100 times BUT because I need to connect to the different tables within the php script and am not experienced enough to created nested database connections, I duplicate the 400 tables within the same database.

What I want to know from a MySql perspective, would it make any difference if the script "play around" with 1 database connection and 400 tables or would 100 databases with 4 tables each be better? (Maximum database queries to only 17 tables at a time)

More Tables, Or Huge Tables?
i'm opening a web-service where people can open their own sites.
i have about 18 tables for a website to be put on the air.

i wonder what's better for my users:
one huge table with a lot of rows, or opening 18 new tables in a database in the format:
0_users
0_images
1_users
1_images
2_users
2_images

what's better performance wise? for 1000 users? for 10000 users? 100k users?
i assume that in about 20k users i'll have to get a standalone db server..

How Can I Tell If My Tables Are MYISAM Tables Or What?
Is there a MySQL commad to determine what table type existing tables in a
database are?

Small Tables Or A Few Big Tables
My Web site is going to use a MySQL database extensively throughout the
Web site. It is a content management system type Web site with lots of
users and written articles and other things. With my database I'm
trying to figure out if I should create big tables that have a lot of
rows in them or if I should create a lot of tables that are very
specific to its data and use a lot of table joins in my select
statements. I'm mostly concerned about performance.

I'm thinking that if I make a lot of tables, then select statements I
do will be really fast because my tables won't contain very many rows.
But I don't know if this is true or not or if it matters for
performance reasons or not.

In my Web site I have a lot of different kinds of data that could put
in many different tables. But, without data redundancy and without
using extra storage space, I can still make a few big tables, and make
that work. I just don't know which way is best for performance --
speed, I want speed. Anybody know what I should do or know a book I
should read or a Web site to go to?

Tables On ISP
I am a newbie. I recently had the web developers for my company create a form online for one of my vendors that uses MySQL as the database. I have been unsuccessful at using the command line to connect to the tables. Do i need the mysql service on the computer that i am using to connect to my web server? What exactly do i need to connect to the tables?

SQL Tables
I am working on my table structure and am alittle stuck on what to do. The user has the ability to reg himself, and if he doesnt then his details wont be saved but his transaction will. Now, when I store the transactions for regged and unregged users should I store them in different tables ? See the tables below:

User Table
UserID
Name
Contact
Email
Gender
Registered
username
password
moderator

Event Table
EventID
EventName
MaxNoTickets
SingleTicketPrice

Purchase Table
PurchaseID
UserID
EventID
NoOfTicketsPurchased
TotalPrice

As you can see, the following table would only work if the user was registered. But now the question is how I should store events for unregistered users. So far it looks like I will need to create a seperate table.

Many Tables In 1 DB Vs. Few Tables In Many DBs
How much of a performance hit will I have if I have 1 database with 300 tables, versus 30 databases with 10 tables each? (the single database would have about 30x the traffic of any single database). I don't expect the tables themselves to be very long at all, perhaps a couple hundred records max.

Also, this will all run on the same server installation either way, so with 30 tables I don't get 30 MySQL servers. The traffic isn't going to be super heavy at all.

So, the question is, is it really worth setting up 30 DBs or should I just drop everything into one and keep it simple?

Get Max() Value Between Two Tables
What is the best way to get the maximum value of a "like" field in two tables?(I.e.

Table1 - Field1 - Values: 0002, 0003, 0005
Table2 - Field1 - Values: 0001, 0005 )

Result should be: 0005

Too Many Tables - Bad?
I have a database with 138 tables. Is having too many tables bad? Can it slow things down?

Tables In Use
Everytime I try a repair with my Web Host Manager my whole site freezes up.

I have one table that is "in use" a sessions table.

I can use putty and get to the
>Mysql prompt but I am lost from there...

Is there a way to repair or fix this? I have tried using phpmyadmin and cpanel and neither work, they just time out.