MYSQL :: Using A Query To Exclude Records From One Table, If The Field Value Exists In Another

Table Field Exists?
Is it possible to know whether a particular table field exista in a table or not?

Secondly is it possible to display all the table fields existing in the table.

I have a table called articles inside a database named mysql
I want to display all the table fields present in articles or check whether a particular field exists.

Adding New Field To Table That Already Exists??
here is the syntax i used to create a table with certain fields:

CREATE TABLE article (
id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
section_id int(10) unsigned DEFAULT '0' NOT NULL,
category_id int(10) unsigned DEFAULT '0' NOT NULL,
approved enum('N','Y') DEFAULT 'N' NOT NULL,
title varchar(100) NOT NULL,
author varchar(50) NOT NULL,
email varchar(50) NOT NULL,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (id),
KEY id (id, section_id, category_id)
);

now since i have done that i want to add a few new fields:

INSERT INTO TABLE article(
score ??? unsigned DEFAULT '0' NOT NULL,
genre varchar(50) NOT NULL,
fuser varchar(50) NOT NULL,
);

firsty, is the above syntax correct (i just want to add new fields, not delete any existing ones or their content)? secondly for the field 'score' that will be a 2 digit number but not an integer (will have decimal place) - what do i put instead of ??? there?

Find If Field In A Table Exists Or Not
Is there a way to find out if a field in a table exists before trying to send data to it?
then if not exists create it.

Select Rows Where A Field Value Exists In Another Table
I have a table with a column of email addresses I have a second table with a column of email addresses I want to select all the rows in the first table whose email value exists in the second table Can't get a query to do this without an error,

Exclude Records In Between Two Dates
Basically, I have two tables:

create table dresses (
id int unsigned auto_increment primary key,
reference_number varchar(16)
);

create table bookings (
id int unsigned auto_increment primary key,
dress_id int unsigned,
booked_from date,
booked_to date,
reason varchar(32)
);

Now I can select everything from dresses with all my required criteria, but what I need done is that the select statement excludes any dresses that are within a specified time frame.

For arguments sake:
I need a dress from '2006-01-10' to '2006-01-14'. I want to see all the dresses in my database that arent currently booked out inbetween those two date.

So it would be something along the lines of:

SELECT
dresses.id,reference_number from dresses,bookings
WHERE
(booked_from NOT BETWEEN('2006-01-10') AND ('2006-01-14')) AND
(booked_to NOT BETWEEN('2006-01-10') AND ('2006-01-14'))
;

Problem comes in with the relationship in-between dresses and bookings clearly. How do I make it display all the dresses?

How To Exclude Particular Records In MySQL?
My PHP script has a SQL statement as shown below that works fine, but it is slow and inefficient. I need to speed it up by using one sql statement instead of using the sub-query.

The solution I need is to eliminate the use of the NOT IN clause ie

SELECT DISTINCT CONCAT(fname,' ',lname) AS realname, staffid
FROM staff
WHERE team = 'umi'
AND staffid NOT IN (SELECT staff.staffid
FROM staff, calendar
WHERE calendar.staffid = staff.staffid
AND calendar.type IN ('arl','lsl')
AND (calendar.start BETWEEN '$begin' AND ADDDATE('$begin', 6)
OR '$begin' BETWEEN calendar.start AND calendar.end)
)

where $begin is a PHP variable that has a value like this '2007-11-20" etc. What I want is that it excludes staffid that is already defined in the calendar table by start and end fields. I tried to make it more efficient, but I could not solve.

Join To Exclude Records?
I have two tables joined on email address. I would like to include all the values in table 1 that do not exist in table 2 (basically, the opposite of an inner join).

How Do I Test If A Value Exists In A Table Column With A Query?
I know this is probably something that should be obvious or easy, but I can't figure out how to test whether a value exists or not.

For example, in table "bookgroup" in the "meetingdate" column (which contains dates for meetings), how could I test whether the date "20040307" exists? I don't want to return a value in the same row... I simply want to return a boolean TRUE or FALSE value... like 1 if it does exist, and 0 if it doesn't. Can I do this without creating another column specifically for this purpose? Is there a query which can do this?

All I have so far is

FROM `bookgroup`
WHERE meetingdate='20040307'

but I don't know where to go from there.

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

How Would I Exclude Certain Categories Within My Query?
My client has a cart for which I've made a custom homepage.

We use this query to display the 3 newest items on the homepage:

$query = 'SELECT * FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3';

Now, my client has several categories which he does not want the items of to be displayed on the homepage.

In the database the column is "category_id" which is within the same table as in the query shown above.

How would I exclude several categories from being displayed via the query above? ....

Move Records From 1 Table To Another And Including 1 Field
I've created a table called `temp` and I want to move all the rows in `temp` to `songs` based on:

`temp`.`title` -> `songs`.`title`
`temp`.`artist` -> `songs`.`artist`
`temp`.`catalog` -> `songs`.`catalog`
NOTHING -> `songs`.`userid`

There is no field in the temp table that has this value, i will need to insert it along with each record. How would I go about doing that, without looping through 1 query and inserting each as I go?

How Do I Exclude From Table?
I have a table A and a view B that is a subset of A.

Table A has 2 columns, ticket and link. Every row has a unique ticket#. When a transaction closes a ticket, that row's link value will have the ticket# of the matching trans.

To get View B, All I had to do was select where link is not 0. How would I get the rest of Table A?

JOINS giving me a headache. How would I select from Table A whose link is not the ticket in View B.

How To Exclude Table(s) When Using Mysqldump
I am sure if it's possible to do that, but I want to use mysqldump to backup
my databases. However, there is only one large table I want to exclude. Can
this be easily done?

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

Update WHERE Field Exists
is there a way , in MySQL to do this:

i have a table with rows id,name,passwd,nickname

Check if row with `name`='harry' exists
if so, UPDATE with new data

if not, INSERT a new record

now i'm doing this with a php if else construct, but there must be an easier way.

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now....

Determining If A Substring Exists In Field
How can I determine if a substring exists in some comma delimited data using sql?

I need to return records where a word is found in a list of words from a field. Rather than returning every record and then doing split() and searching myself I would like to know if I can do it using sql.

All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`

Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Can You Use A Boolean Field Within A Table To Evaluate A Query?
One quick question. I want to build a query like this:

SELECT * FROM <table> WHERE <condition>

Where <condition> is a boolean field in the table being accessed. I assumed in the construction of the query that simply placing a boolean-type field in the query after WHERE would cause the MySQL engine to only return the rows where the field is TRUE. Please let me know if this is correct, and will work.

Can't Create Table -- Error 1050 - Table Already Exists
I tried to create a table in the database. Here is the query sentence :

CREATE TABLE word (aff_code varchar( 10 ) NOT NULL default '',position tinyint( 1 ) NOT NULL default '0',flag tinyint( 1 ) NOT NULL default '0',PRIMARY KEY ( aff_code, position )) ENGINE = MYISAM DEFAULT CHARSET = latin1;

I was expecting to create a three column table. However, MySQL kept reporting error : ERROR 1050 (42S01): Table 'word' already exists. The fact is there is no table in this database. I then changed the table name, engine type, MySQL still report the same problem. I've tried changing database name. Still the same problem.

Prior to the query, I run a program that created (and inserted data as well) 65,000 tables in another database (same server although). I'm wonderring would this cause some kind of dead lock that result in the problem of not able to create any more table?

Can anybody give me some tips on how to solve the prolem?

Fetch Data From Two Table Of Same Field With Single Query
Can I fetch data from two table of same field with single query?

Can I Use Select To Query Mulitple Data From Within A Field Of A Table?
I would be grateful if someone could tell me whether Select is appropriate to choose data within a field and separated by commas . I have made the foll. script. What I want to achieve, is below the script.....

Perform Alter Table Only If A Table Exists
I need to run a batch of "Alter table" statements on a set of database tables.
However, some of the databases may not exist, and even if they exist, the specific table might not exist.

Alter table only works if the table exists. Is there a way to tell MySQL to execute the "Alter table" statement only if the database and table exist? If so, how?

Can't Find Table Error, Even Though Table Exists
I use the following query:

SELECT * from ads where user!='$id' and ready=1 and category='$category' and main.id=user and main.credits>4;

And it returns #1109 - Unknown table 'main' in where clause

But the "main" table exists. What gives?
I've tried it with other tables in the same database and it says that they all don't exist.

Any ideas?

Is There A Query To Check Directly If A Datarecord Exists?
Is there a query to check directly if a datarecord exists?

Table Exists
Is there a way to find out if a field in a table exists before trying to send data to it?
then if not exists create it.

Function To See If A Table Exists
I need to do a check to see if a table exists or not, any mysql functions that do this?

Finding If A Table Exists
Is there a statement I can pose to determine if a given table even exists
within a MySQL database?

Querying If A Table Exists
I am familiar wth the "IF EXISTS" clause to drop tables before recreating them. However, I am wondering I there is a way to do that in a select statement.

I'm trying to do something like this:

ex: select if(exists('mytable'), 'Found Table', 'Unknown Table);

Create Table If Not Exists
I'm having trouble with this. My impression was that nothing would be done using this form, but it appears that the select clause is adding rows in to th existing table, but my intent was to only create a table once in my code:

CREATE TABLE IF NOT EXISTS z_set_1000_1999
(
SELECT rid, mid, rg
FROM ts2
WHERE rid>=1000 and rid<= 1999
ORDER BY rid, mid
);

and then refer to the table on later references to it, using:

SELECT rid, mid, rg FROM z_set_1000_1999 ORDER BY rid, mid;"

What appears to be happening is that the CREATE is adding duplicate rows to the existing table. The second time through, the table size doubled. Is that supposed to happen; insert the rows if the table exists? If so, how do I test if the table exists so I can go directly to the SELECT and bypass any additional creates or inserts?

Since I am creating a large number of tables like this, I thought it would be beneficial to query an existing tables rather than search a larger table every time, since so many of the queries are identical.

Checking If A Table Exists
Basically what I need it to do, is when you open the page it checks to see if a table exists in the MySQL. Then if it does NOT exist redirect to a page where you begin the steps to install it. If it DOES exist then it will redirect to a page showing that information.

Table Exists In Queries
How do I alter a MySQL insert, update or select statement to fail gracefully, without producing an error, if the table doesn't exist?

I know it sounds weird, but I have a table that's used for logging certain non-critical information. I have an external process that periodically backs up this table. It backs up the table by renaming it with a number after it (for example, table "log" becomes table "log_1", "log_2", etc) and they are organized so that it only saves the last 10 and keeps the names consistant, etc. But that's beside the point.

Check If A Table Exists
is there any way to check if a table exists in mysql? i am using a graph program in vb, but if the table it is trying to graph doesnt exist it breaks down! i need some sort of check so as the offending table will not be added to the query if it doesnt exist?

How Do I Check If An Table Index Exists Via PHP ?
I was wondering if it's possible to verify if an table index already exists or not? As it's possible to add indexes multiple times.

Check If Row Exists In Anotehr Table
Is there a way to check if a certain value exists in another table without using something like

SELEC * FROM tbl WHERE someid = 'somevalue';

and seeing if it returns false

I ask this because I'm running a kind of search query where it selects all records from a table where this field is this, that field is that, etc.

So for example

SELECT * FROM members WHERE id='this' || name='that' || height='67' || etc..etc...

now one of the attributes is a zip code.

In another table, i just ahve a list of like a billion zip codes. Ifi want to check if the zip code exists in this list in this other table, how would i do so while preservingn the above format in one call?

Error: SQL:DROP TABLE IF EXISTS Accounting;
Dropping tables if exist...Query Error:You have an error in your SQL syntax near '; ' at line 1

SQL:DROP TABLE IF EXISTS accounting;

Code:

Select Rows From One Table Which Not Exists In Antoher
How do I select functions that website with website_id = 1 doesn't have.

The correct answer will be function_id = 4 since functions_to_websites contains function_id = 1 to 3 for website_id = 1

And if I add another row in functions which is not mentioned in functions to websites it should be returned aswell. Code:

1050 Error Table 'columns_priv' Already Exists
I have dumped a database into the file named dbase.sql
when I am trying to recreate the database its giving the following
error.
can any one help me in this regard?

[root@ws43 project]# vim dbase.sql
[root@ws43 project]# mysql -u "root" project < dbase.sql
ERROR 1050 at line 19: Table 'columns_priv' already exists

Vasundhar/

Help With Moving Data From One Table Field To Another Table Field
I am a relative newbie at this so would appreciate help - already searched and found and tried several suggestions for similar issues - but nothing quite worked!

I need to move a membership roster to another table in the same db - currently the data is at jos_users1 and it needs to be moved to jos_users. The jos_users1 table has only one field, named email.

The jos_user table has several fields including the email field, and already has data in it - so I don't want to overwrite the table - just upload the additional email addresses into the table.

View Records In A Column/field
I used to use phpmyadmin and loved the fact i could just click on a field and hit 'browse' and it shows me all the records in it.

I just downloaded all the GUI tools for mysql 5.0 and I love them and don't think i need phpmyadmin anymore. I have problems though with one thing. How do you browse through all the records in your db fields? I am sure this is possible with one of the new GUI tools I downloaded from this site:

MySQL Migration Toolkit
MySQL Administrator
MySQL Query Browser
MySQL Workbench

I am probably just missing the obvious. If anyone knows how I can browse all of the records using one of these tools,

Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported
from Excel, which explains why it's a mess to begin with.

Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have
duplicates.

example;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 4 | 7 | 3 | 9 | 122.165.177.211 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
| 6 | 24 | 3 | 17 | 122.165.177.211 |

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |

Combining Multiple Records Into One Based On A Key Field
I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I
need to combine multiple records which share two like fields. (If that's not
clear, the real-world explanation is below.)

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | | | | |
| COMP1 | 123 Main St | | 1 | | | |
| COMP1 | 123 Main St | | | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | | | |
| COMP2 | 45 Oak Pl | | | 1 | | |
| COMP2 | 45 Oak Pl | | | | 1 | |
| COMP2 | 45 Oak Pl | | | | | 1 |
+----------+--------------+----+----+----+----+----+

Must be distilled to :

+----------+--------------+----+----+----+----+----+
| ID | ADDRESS | P1 | P2 | P3 | P4 | P5 |
+----------+--------------+----+----+----+----+----+
| COMP1 | 123 Main St | 1 | 1 | 1 | | |
| COMP2 | 45 Oak Pl | 1 | | 1 | 1 | 1 |
+----------+--------------+----+----+----+----+----+

So essentially I want to combine all of the 'Px' fields for each ID into one
record.

The real model : The data represents companies (identified by ID) and the
product lines they stock (P1 - P5). Right now, there's a separate rec for each
product line. I need to combine those seperate recs into one.
____________________

I suppose I could import the raw data into separate tables, each with one 'P'
field -- and then combine them into another, but I'm not sure about that
approach either.
____________________

I already tried to make Excel combine before exporting, but had no luck.

Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates.

example;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 4 | 7 | 3 | 9 | 122.165.177.211 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
| 6 | 24 | 3 | 17 | 122.165.177.211 |

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |

Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id.

How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously?

My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense).

Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.

COUNT Row But Exclude 0
I have a table called 'userrating' with 3 INT fields in it as shown with sample data:

rating
0
3

rating2
2
4

rating3
0
0

I want it to count each row of each field in seperate queries BUT ONLY if there is not a '0' in that row.

So the count of:
rating = 1
rating2 = 2
rating3 = 0

I have a start put i don't know how to check for zeros in the rows, here is what i have for the first field rating:

$ratinginfos = $db->query_read("SELECT *, COUNT(*) AS totalvotes, SUM(rating) AS total FROM " . TABLE_PREFIX . "userrating WHERE userid = $userinfo[userid] AND active=1 GROUP BY userid ORDER BY dateline DESC");