MYSQL :: List Available Items Based On Prerequisite Table

Get A List Of Items That Are In 1 Table And Not The Other
I have a couple of tables, and would like to make a query to get a list of items that are in 1 table and not the other. A join command will get the list of items that are in each one. But I have not been able to figure out how to only pull out the ones that I need.

Table 1 has invoice number, and code number. Table 2 has code number.

I need a list of the items from Table 2 where the code numbers are not in Table 1.

View Replies !

Items Based On The First Letter
we have products database with CD's, and would like to create an alphabetical list of artists, then display the CD's that the artist has underneath them.
The database has the products in them, with the artists name being called 'prod_artist_name'. the query i tried, though I knew it wouldn't work is:

SELECT *
FROM products
WHERE prod_artist_name LIKE '%n%'

Now, this displays all the artists with the letter 'n' in their name, obviously. just wondering if there was a way to display the artists with 'n' as the first letter of their name.

View Replies !

List Of Items In A Field
I have a table of products for a shopping cart. I want to give the user to ability to select, for example, a color for a certain product. How can I set up my database table so that certain records can have a number of options available to them. Is there a data type like a "list" where I can list the options? I thought adding a field that when set would indicate the presence of additional options, which would then indicate that I should check for additional options.

View Replies !

Datatype For A List Of Items
I have a table called students and a table called classes.

In the web page you create a student by typing in the classes he has taken. If the class doesn't yet exist in the classes database, it will be created.

Somehow, I want to store what classes each student has taken. I to be able to find, for example, all the students who have taken courses 232 and 199 but haven't taken course 104.

Is there a datatype similiar to an array/list, or is there a better way to store this information? (I want to be able to search quickly.)

View Replies !

Retrieve A List Of Values Based On The String Length
Code:
$qry = "SELECT ID WHERE (SELECT CHAR_LENGHT('field_1') FROM tab = 8)";

View Replies !

Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it.

It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time.

I've simplified what I'm doing above, but it is detailed below.....

View Replies !

Assigning Values To Records In Database Based On Random List
I have a single database table contains several hundred records (the data is not important). I need to be able to assign a random value from a list (say: 10, 15, 20, 25, 30) for every record, for an specific field in the table, say the field called "category_id".

View Replies !

Prerequisite
So I have a table that has, among other things, these feilds:

id PRIMARY KEY
name VARCHAR(50)
prerequisite INT NOT NULL

Prerequisite contains a reference to the id of whatever item comes before
it.
How can I generate a query on this that will put them in sequence

View Replies !

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

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

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

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

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

View Replies !

Filtered Items In The Same Table?
I have a little SQL issue and need some guidance for a plan of attack.

I have records which sort of look like the following (very loosely based on original)

NAME SHOT DESCRIPTION DISTANCE
JOE FAIRWAY 150
JOHN FAIRWAY 180
JOHN FAIRWAY 210
RICK BUNKER 190
TODD BUNKER 180
JOE BUNKER 220
JOE FAIRWAY 280

Now I have a statement that groups all the data by name and then gives me total number of shots (regardless of where it landed) and distance between 150-200.

My dilemma is adding another column (BUNKER_SHOTS) with - of those filtered shots, how many were in the bunker. Seemed simple at the time. Do I use a subselect or inner join or what?

View Replies !

Copying Missing Items From One Table To Another
two structurally identical tables (national_db_1.users and national_db_2.users) in two different databases. I need to find what's missing in one and add it from the other.

I would like to insert records from national_db_1.users, that are not currently in national_db_2.users, into national_db_2.users.

Also, the entire row(s) should be copied, with the exception of the primary key, which can be automatically generated by the insert

View Replies !

Multiple Items In An ALTER TABLE Statement
Was in the midst of doing something today and I attempted to drop a
number of columns in a table with the following:

ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4;

Unfortunately MySQL gave me an error reading:

ERROR 1064: 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 'col_2, col_3, col_4' at line 1

Can you not have multiple columns names in an alter statement?

View Replies !

Moving Average With Multiple Items In Table
Try to get a moving average on a table with multiple items. i found a query that works fine when you use it on 1 item....

View Replies !

How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2?

TABLE 1:
+---+------+
| i | name |
+---+------+
| 1 | item1 |
| 1 | item2 |
| 7 | item3 | <-- delete all rows with i=5,6,7
| 6 | item4 | <-- delete
| 5 | item5 | <-- delete
| 5 | item6 | <-- delete
| 7 | item7 | <-- delete
+---+------+

TABLE 2:
+---+---+------+
| j | i | name |
+---+---+------+
| 1 | 1 | item1 |
| 1 | 3 | item2 |
| 1 | 2 | item3 |
| 2 | 5 | item4 | <---- j=2 => i=5
| 2 | 6 | item5 | <---- j=2 => i=6
| 2 | 7 | item6 | <---- j=2 => i=7
| 3 | 8 | item7 |
+---+---+------+

View Replies !

Selecting Data Form One Table That Is Based On An Entry In Another Table
This is the code I am using:

***********
SELECT co_name,city,country,logo_small FROM $info WHERE $info.co_name=$categories.co_name AND WHERE $categories.everyday_wear='y' ORDER BY co_name ASC
***********

I am using a while loop in php to loop through all of the records but no records are being displayed. What am I missing? Do I need to use a JOIN statement?

View Replies !

Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary)

I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's.
I need to do this several times and have tried it several ways to no avail.

Table A
---------
companyID int(10) pKey
legacyID int(10) old legacy pkey

Table B
---------
bAID int(10) pkey
companyID int(10)
legacyID int(10)

Table A has values for both companyID (unique key) and legacyID.
Table B has values for bAID (unique key) and legacyID but companyID is empty.

I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship.

I need a query that will update ALL rows.

View Replies !

Updating Table Based Upon Matching Field In Second Table
I have a database of books that was originally created as a flat file.
Each record has a number of fields, including the authors name. I'm
trying to convert the database to something a little more efficient.
I've created a new table (called Authors) of unique authors names and
assigned each one a unique ID. I've added a new field in the original
table (called Books) for the author's ID. Now, I need to update the
original table with the author ID from the Author's table.

Something like this:

UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName =
Authors.AuthorName

This obviously doesn't work. Any assistance on how to forumulate this
query (or, if I'm headed down the wrong path, the correct way to do this
operation) greatly appreciated.

View Replies !

Select Data From 1 Table Based On Criteria From Another Table
is it possible to select all the data in one table based on a criteria from another table?

for instance i want to select all the therapist from massage_therapist WHERE massage_schedule.finish > 0.
i don't want merged results. i just need to list all therapist based on the where criteria from a different table.

these two tables have the therapist_id in common.

View Replies !

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

Update Table Based On Email In Another Table
I'm having trouble updating the entries from a table. The situation is as follows:

Customer table contains:
1) customer_email_address
2) customer_newsletter (value 0 or 1)

Visitor table contains:
1) email

The visitor table contains email addresses from customers that have signed up through another system.

I would like to update the customer table and set customer_newsletter to 1 where customer_email_address matches email from the visitor table.

View Replies !

Selecting From A Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

UPDATE Based On Value In Another Table
How do I update a table to remove orphaned references to a second table? I've deleted rows in the second table, which has a unique auto_increment key. The first table now has references to keys that no longer exist. I need to update the first table, setting the value to NULL where the referenced key no longer exists. Something like:

UPDATE table1 SET table2ID = NULL WHERE table1.table2ID NOT FOUND IN
table2.ID;

The NOT FOUND IN isn't SQL, of course, but I'm not sure what should go there.

View Replies !

Update Based On Another Table
Is it possible to do an update in MYSQL based on another table? I have version 3.23 and when I try to run this statement:

UPDATE ApplicationTbl
INNER JOIN AcademicTbl
ON ApplicationTbl.CampusID = AcademicTbl.CampusID
AND ApplicationTbl.Application_Period = AcademicTbl.Application_Period
SET ApplicationTbl.App_Status = 'Qualified'
WHERE AcademicTbl.Sem_OnCampus >= '1'
AND AcademicTbl.GPA >= '2.4'
AND AcademicTbl.Judicial_Sanction IS NULL

It keeps saying its wrong. Even though I know its not

View Replies !

Selecting From One Table Based On Another?
I have a table for products

and another table for the type of product it is like this:

I need this is be in a seperate table because one product can be several different types like ring,band,wedding,three-stone,etc.

but how can I then select a product based on criteria from the types table

say I wanted to select all products that are type = "Band" and type != "Fancy" ?

View Replies !

Table Based On Info In Another Table
Essentially this is what i want to do. I have two Tables,

Table A- has the field "id" which is the primary key
Table B- has the field "id" which is the primary key

Select * from tableA where Table A.id is not in tableB.id

How can i form a statement that will accomplish the above.

View Replies !

Updating A Table Based On The Value In Another Table In Ver 3.23.54-Max
I am trying to update fields in table SOLAR_SYSTEM based on the values in RESEARCH_LEVEL table

This is a SQL that I am trying to use

UPDATE SOLAR_SYSTEM JOIN RESEARCH_LEVEL ON SOLAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SYSTEM.CASH_METAL=SOLAR_SYSTEM.CASH_METAL+1000,
SOLAR_SYSTEM.CASH_CRISTAL=SOLAR_SYSTEM.CASH_CRISTAL+1000,
SOLAR_SYSTEM.CASH_ENERGY=SOLAR_SYSTEM.CASH_ENERGY+1000
WHERE RESEARCH_LEVEL.TYPE='M' AND RESEARCH_LEVEL.LEVEL=1;

I got error

"ERROR 1064: You have an error in your SQL syntax near 'JOIN RESEARCH_LEVEL ON SO
LAR_SYSTEM.USER_ID=RESEARCH_LEVEL.USER_ID SET
SOLAR_SY' at line 1"

I don't know what is wrong. Can I do such an update in Ver 3.23.54-Max in the first place ? IF yes, then what's wrong with my statement?

View Replies !

Creating New Table Based On Old Table
I have a MySQL database with a user table will lots of DUPLICATE email.

I want to delete the rows completely having the duplicate email.

I found a way out is to use some thing like this:

Code:
insert into newtable (email)
select distinct email from oldtable
But this way, only the email column is populated in the new table. I want all the other columns as well.

View Replies !

Join Table Based On Specified Condition
Is it possible to join two tables based on the specified condition in MySQL. For example, i have two tables t1,t2 and t3. The table t1 and t2 has field called name and the table t3 has field name class. I want to select the names from t1 or t2 based on the condition of class(t3). If class=4 then select name from t1, if class=10 then select name from t2.tell the query in mysql.

View Replies !

Deleting From A Table Based On Record Id
I need to execute the following query:

delete from history where id=(select min(id) from history where user = username);

this query doesn't work and gives me an error "ERROR 1093 (HY000): You can't specify target table 'history' for update in FROM clause"

How do I rewrite this query? Ofcourse I can go for two queries, but I'd prefer to do it with a single query.

View Replies !

Order By Based On Results From Another Table
I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this:

SELECT A.id AS Id, A.nome AS Nome
FROM main_table A
JOIN joined_table JT ON JT.id = A.id_cat
ORDER BY ("first whoever have a row in" another_table AT)

another_table stores the image paths related to main_table , i need to show all query results from main_table even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first...

I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results. it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)

View Replies !

Select List Table Name Beside SHOW TABLE
is there any way to select list of table name beside using SHOW TABLE syntax

by using SELECT syntax

such as SELECT bla bla bla

View Replies !

ORDER Based On One Field But LIMIT Based On Another?
Say I have a table with students and their grades, and I want to get the students with the top 10 grades, but the result to be sorted based on their name. How would I do that?

View Replies !

Table Creation Based On User Login
I'm trying to build a website that will allow the addition of users (w/ username, password, etc.).

That seems simple enough.

But is it possible to set something up (in PHP, for example) that will create a new table in the database for each new user?

Ex. John Smith signs up, is verified, and a PHP script (or Java, or whatever) creates a table specifically for him.

I know I'll need a master table of usernames, passwords, etc. These new tables would store certain user-specific data, such as preferences and other data the user might want to restrict access to.

I know I could store all of this information in a master table, but I'm trying to think ahead. Worst case scenario, if there are millions of users (unlikely, but not impossible), I'd hate to have all that data in one table, but if I have to, I can.

View Replies !

Counting Levels On Adjacency Based Table
Does anyone know a single query that could successfully count the number of levels in a table based on the adjacency model?

By "number of levels", I mean the number of LEFT OUTER JOIN that needs to be used in a query such like this one :

SELECT
level0.cat_ID AS level0_ID, level0.cat_parent_ID AS level0_parent_ID, level0.cat_name AS level0_name,
level1.cat_ID AS level1_ID, level1.cat_parent_ID AS level1_parent_ID, level1.cat_name AS level1_name,
level2.cat_ID AS level2_ID, level2.cat_parent_ID AS level2_parent_ID, level2.cat_name AS level2_name,
level3.cat_ID AS level3_ID, level3.cat_parent_ID AS level3_parent_ID, level3.cat_name AS level3_name
FROM ecom_categories AS level0
LEFT OUTER JOIN ecom_categories AS level1 ON level1.cat_parent_ID = level0.cat_ID
LEFT OUTER JOIN ecom_categories AS level2 ON level2.cat_parent_ID = level1.cat_ID
LEFT OUTER JOIN ecom_categories AS level3 ON level3.cat_parent_ID = level2.cat_ID
WHERE level0.cat_parent_ID IS NULL
ORDER BY level0_name, level1_name, level2_name, level3_name

View Replies !

Update Based On Row Offset From Begining Of Table?
Is there a way to update a table row based on its offset from the begining of the table. I want to be able to update based on a rows position when its brought up when all rows in a table are selected. ie; select * from table.

View Replies !

Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records.

I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are.

I have found some
SELECT COUNT( * ) AS num_entries, title
FROM title_table
WHERE title >1
GROUP BY title

But this doesn't seem to work, although looks like it's on the right sort of lines?

I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!

View Replies !

Select Based On Two Rows In Mapping Table
I have a many to many relationship between a resource and tag:

resource - 1:M - tagged_resource - M:1 - tag

So a resource can have many tags and a tag can belong to many resources. The data would look like this:

RESOURCE TABLE
id: 1
name: resource1

TAG TABLE
id: 1
tag: new
id: 2
tag: tested

TAGGED_RESOURCE
resourceID: 1
tagID: 1
resourceID: 1
tagID: 2

How can I select the single row from the RESOURCE TABLE which has the tags 'new' and 'tested' in the TAGGED_RESOURCE TABLE?

I have tried using 'IN' but it acts like an OR operator rather than AND.

View Replies !

Update Column Data Based On Another Table
I've got 2 tables: 'city' that list over 2000000 rows and 'profile' where each row are associated to a city.

What I want to do is to update cities popularities based on profile without having to scan the hole 2000000 rows of 'city'.

So is it possible to make those 2 query in one so I do not need to do a php loop:

SELECT city, COUNT(city) FROM profile GROUP BY city;

then

UPDATE city SET popularity = COUNT(city) WHERE city.city_id = profile.city

View Replies !

Multiple Table Search Based On User Input
I'm working on a database containing our research data. I know very little about PHP, MYSQL, or database structure (except keep like data together in different tables and have a key field in each).

I'm currently working with 3 tables (tbl_2006 and tbl_2007 structures are identical): ....

View Replies !

Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.

MySQL
SELECT *
FROM default_categories
LEFT JOIN custom_categories ON category_id = custom_cat_id
WHERE category_parent = ''
AND custom_cat_parent = ''
ORDER BY $order_by $sort

View Replies !

How Do I Replace Data In An Existing Table - Based On A Column With An Identifier
Each record in the table I want to update has a unique identifier: products_model. For a given model number, I want to replace its image, which is located in a field called products_image.

The file with the new data is a .txt file, a sample of which looks like this:

v_products_modelv_products_imageEOREOR
5361453614.jpgEOREOR
5361553615.jpgEOREOR
5372453724.jpgEOREOR

The beginning part of the table looks like this:
+----------------------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+---------------+------+-----+---------------------+----------------+
| products_id | int(11) | NO | PRI | NULL auto_increment |
| products_type | int(11) | NO | | 1 | |
| products_quantity | float | NO | | 0 | |
| products_model | varchar(32) | YES | MUL | NULL | |
| products_image | varchar(64) | YES | | NULL | |
| products_price | decimal(15,4) | NO | | 0.0000 | |
| products_virtual | tinyint(1) | NO | | 0 | |

From what I've read over the past few days...I think I need to:
(a) delete the EOREOR column from the text file;
(b) use the LOAD DATA INFILE command somehow, telling it to ignore the first line of column headers in the text file.

View Replies !

Returning Rows In Left Table Based On Mutliple Criteria In Right
This is starting to get to me. I'm sure there's a simple way of handling what i'm trying to do, but someone might be able to help out quicker than spending another hour searching and testing for this. Here's the problem:
Simplified tables:
ARCADES
=======
ID,
name
GAMES
======
ID,
name
ARCADES_GAMES
=============
ID,
arcade_iD
Games_ID
Straightforward enough so far right? I'm trying to get all arcades that have ALL games in a passed in set of game_id's. So for instance I might want all arcades that have the games 11 and 14, but it must have both those.
I can do soemthing like...

SELECT a.name
FROM arcades a
WHERE EXISTS(SELECT 1 FROM arcades_games
WHERE arcade_id=a.id AND game_id IN (11,14))
But that'll return all rows that match ANY of (11,14) rather than ALL of 11,14.

It all comes down to the simple thing of getting rows in a table where all criteria from a list is met, but any advice on how i would do something like this?

View Replies !

Populate A Table From A List
I am trying to populate a mysql table from the contents of a <SELECT>
list using PHP. The list is populated from another list and from
there all the items need to be saved to a table.

View Replies !

How To Get Mysql Table List ?
Is there a way to get VIA SQL the table list for a specified database ? Same thing for the database list ... and.... column list, Something that could look like scanning the syabse sysobject table ?

View Replies !

List Occurrences In A Table
I have a table of STUDENTS structured like this :

Students (IdStudent, NameStudent, FirsNameStudent, AdressStudent, DateOfBirthStudent).

My question is : I try to list occurrences in this table concerning NameStudent, in order to check if there are several guys/girls with the same name.(I don't want to use the name for my query, only people who exist twice or more).

I tried this :

SELECT COUNT(NameStudent) AS Nombre
FROM Students
WHERE NameStudent='PEARSON';

^^ this query works but shows me '2' and suppose that you kwnow the name you're looking for.

View Replies !

Getting A List Of All The Table Names
if I am connected to a database is there anyway that I can get a list of all of the tables that are in the database?

View Replies !

Create Distribution List From Table
I'm currently managing a site with information from our members such as
phonenumbers and emailaddresses, stored in a table. I would like to retrieve
the emailaddresses from the table and create a distribution list from it.
Right now I store the addresses twice, first in the mysql-table, and second
in mailman. I would like to circumvent the second.

View Replies !

List Of Tables Which Have FK Referring To PK In A Particular Table
Is there anyway that I can get the list of all tables which have foreign keys and referring to the primary key in a table, called A?

I would like to drop the table A. Before that I want to drop all FKs which are referring to PK in this table.

Is there anyway to drop this table forcibly without removing the constraints?

View Replies !

Using A Field, Which Is A Comma Separated List, As The List In An IN Comparison Test.
The following query only reads the first character of a comma separated list stored in a field of the records being queried.

The varchar field in table d named "display" holds 1,4

The query " select * from d where 4 IN (display) " tests false.

If I change the field "display" to 4,1

Then the query tests true.

It is either testing only the first character, or is stopping at the comma.

I need this to test true if the value is anywhere in the list, what am I doing wrong here?

View Replies !