MYSQL :: IN Clause For Select Query

IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS..

I'm trying to do this:

insert into table2 (field1) select field1 from table1 where field1 not in
(select field1 from table2)
delete from table1 where field1 in (select field1 from table2)

1. Insert field1 from table1 into table2 if it doesn't already exist
there...
2. Once moved, delete from table1.

Where Clause In Select Query
I have this query atm: SELECT postid, body, posttime, commentcount FROM nz_posts WHERE sections = ':7:' ORDER BY 'posttime' DESC LIMIT 0, 5

this works, but "sections" can have something like ":7: :8:", ":4: :7:", etc... all the numbers are between : and there can be one or many numbers (all diferent).

I want to select all the rows that has :7: in the sections field.

Select Clause
We have a recordset with the following structure id (int), mls
(varchar)

What we need to do is within a single sql call we need to show all
records where the mls = '' and all unique records where mls!=''

In other words, there will be multiple records with duplicate mls
entries. We do not want to show duplicate enties for where the mls has
been set, only where the mls has not been set.

Any suggestions?

SELECT Using IN Clause
for example, i'll do something like:

select first_name FROM users WHERE user_ID IN (2,1,3)

and i want the first name for user_ID 2 to come first, then 1, then 3
but instead, understandably so, it searchs 1, then 2, then 3, and i was severely hoping there was a way to preserve the order.

Select Clause Problem
how can i select second highest age from a table having fields.

Select Statement Where Clause
Does MySQL support "==" in the where clause, or is an exact match the default behavior?
I noticed the following select statement in existing code:
***********************************
$q = "SELECT
account_id, account_username, account_email, account_password, account_status
FROM account i
WHERE (upper(account_username) = $tcName)";
***********************************
I would have expected two records matching for the following situation:
$tcName = "BECK",
with a record having account_username = "BECK" and
a record having account_username = "BECKER"

It only returned a single record ("BECK"). The account_username is a varchar - does that make a difference? Are there any MySQL settings which control "exact on"?

Subquery In The SELECT Clause
I am having the problem in the sql statement

SELECT name,
(SELECT Sum(xxx) FROM myTable GROUP BY fld) as mySum ,
(mySum + 1)/2 as myVal
FROM myTable1

This resulted in an error!!
How can i use the 'mySum' in this query

How To Select Distinct Rows Using The WHERE Clause
I have an app at work that will only allow me to customize the WHERE clause on a SQL statement.

Currently the SQL Statement is hard coded something like this:

SELECT * FROM `ERRORS`

Is there a way to do a DISTINCT in the WHERE clause that would return the same results as:

SELECT DISTINCT `MEMBER_NUMBER` FROM `ERRORS`

Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working

Creating View For UNION SELECT Clause
I've problem creating a View w/ a Union select clause.

I can't do this:

Create view v as select name from employee union select name from customer;

An error message:
"ERROR 1143 (42000): create view command denied to user 'root'@'localhost' for column 'name' in table 'employee'"

For simple illustration, the datatype of the fields are same (VARCHAR)

I've no problem creating a view for just a simple select statement (i.e. not UNION)

Dynamic Field Builder In Select Clause
I have a MySQL 4.0.15 installation. I'd like to run this kind of query:

select
ic.id,
ic.route_prefix,
from
icdrs ic,
cdrs c
where
ic.id=c.id and
CONCAT(ic.route_prefix,'_route') IN ('DT...',....) and
1;

where ic.route_prefix can take one of the value 'incoming' or 'outgoing' and therefore the query would translate to:
where ... and incoming_route in (....)
or
where ... and outgoing_route in (....); depending on the row value in table icdrs.

SQL Query JOIN And LIKE Clause
I need to run a query which uses a like query like the one below, no problem:

SELECT * FROM footballers WHERE sname LIKE '%$criteria%'

however, if I want to join two tables and still use the LIKE clause how would the syntax look??

My guess below was incorrect. any help would be appreciated. thank you

WHERE sname LIKE '%$criteria%' AND (footballers.footballerID = PremTeams.footballerID)

Which Query Is Used For Minus Clause
Can u give me the ans which query is used for minus clause. in MySql?

SUM Query And Comparing Values In WHERE Clause...
Here is my query:

Select With Select In Where Clause
This is a very simplified SQL statement intended to reproduce an error, not do anything interesting:

select * from value where id in (select id from value where id=1000000);

It fails with:

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 'select id from value where id=1000000)' at line 1

However, the following works

select * from value where id in (1000000);

What Query To Check If Any Rows Exist Satisfying WHERE Clause?
I'm looking for a query that will check if any rows exists in a table according to a WHERE condition. I know I can use COUNT(*) but then mysql will do unnecessary task of counting all the rows whereas I just need true or false. So far I did this:

SELECT COUNT(*) AS exists FROM mytable WHERE ...
Sometimes I just select the first row and check later in php how many rows have been returned:

SELECT some_col FROM mytable WHERE ... LIMIT 1
But I cannot do this check (or can I?) in sql alone and I have problems when I want to use this in a subquery, for example:

SELECT id,
name,
(SELECT COUNT(*) FROM mytable WHERE ...) AS exists
FROM othertable
WHERE surname='xxx'
Can I do the same without using COUNT(*)? I would like a query that returns 0 or NULL if no rows were found, or 1 (or some other value) if 1 or more rows were found.

Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

Using A Limit Clause, But Return The Number Of Rows Of The Query Without The Limit
I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there.

I search the mysql manual, but could not find anything.

Select Query With Sub-query For Count And Max
I have a table "test"... as below... I want to have a result set of all the parents with the count of their children and the lastest created date of their child ...

Select Query Help
Okay, so I'm no SQL guru and I've run into a problem while working on building a query for one of my PHP applications. Here is the query in question.

PHP

SELECT
    details.salute_url,
    details.about_me,
    details.html_header,
    details.html_footer,
    details.ads,
    details.publisher_id,
    details.channel,
    details.border_colour,
    details.title_colour,
    details.background_colour,
    details.text_colour,
    details.url_colour,
    main.profile_perm,
    friends.ref_id AS friends_check
FROM
    isps_details AS details,
    isps_main AS main,
    isps_friends AS friends
WHERE
    details.uid=Ƈ' AND
    main.uid=Ƈ' AND
    friends.uid=Ƈ' AND
    friends.friend_id=?' AND
    friends.pending=Ɔ' AND
    friends.pending_3pt=Ɔ'
LIMIT 0, 1

What this query is supposed to do is pull some information on a user from "isps_details" and some more information from "isps_main". Then it is supposed to pull up some info from the "isps_friends" table which is used to determine whether the current user(friends.friend_id) is "friends" with the owner of the current page(friends.uid).

The thing is, sometimes the user may not be friends with the current page owner and as a result the friends part of the query will be false and as a result of that the entire query returns nothing. Is there anyway to modify my query in order for it to still pull up the records for the other part of the query even if the friend's WHERE statment is false?

Again, I'm not too good with mySQL so any help is appreciated!

NB: I'm on mySQL 4.0.27-standard

Need Help With A SELECT Query
I have two tables.

Table 1 = ContentWords
Table 2 = TitleWords

The tables have both each two fields named wordId and articleId. The wordId field references a wordId in a table named Words which contains the fields wordId and word.

As you might understand I use these tables (ContentWords and TitleWords) to index words that appear in articles to use with a search function.

It's easy to construct a search query to have it select a articleId from either ContentWords or TitleWord (SELECT articleId FROM ContentWords/TitleWords WHERE wordId = $var).

But if I want to search if a word exists in both the Content and Title? I've constructed a SELECT query that kind off works:

SELECT ContentWords.articleId AS ContentArticleId, TitleWords.articleId AS TitleArticleId
FROM `ContentWords` , `TitleWords`
WHERE ContentWords.wordId = TitleWords.wordId AND ContentWords.wordId = $var AND TitleWords.wordId = $var
This will output something like:

ContentArticleId TitleArticleId
53 76
77 76
That means the word exists in the content in article 53 and 77. The word also exists in the Title in article 76.

What I actually want is only ONE field named ArticleId with the result of 3 rows (53,76 and 77).

Is this possible?

Thanks in advance!

Help With SELECT Query
I am having trouble coming up with the proper SQL that will get me a certain result.

i have a table that looks something like this:

request_id | member_id | Club_id

1 | 10 | 12
2 | 10 | 12
3 | 10 | 142
4 | 10 | 142
5 | 10 | 72

I am trying to come up with a query that will give me the number of times that member #10 chose each different club.

ex:
club #12 - 2 times
club #142 - 2 times
club #72 - 1 time

i have tried a few things but everything i have tried with DISTINCT, or COUNT gives me 5 (total requests). And it is late in the day and my brain is a bit fried.

SELECT Query
Is it possible to SELECT from a table but only retrieve part of a field value?

I have a table that contains stock data, the field in question is the description.
I would like to only return the first 100 characters of the field rather than it's full contents.

Using LIKE In A Select Query -
I am trying to query 2 tables. One has a field, County_name which includes a list of counties and the state they are associated with ie. MT-Missoula

The other table is a list of sites and has a State field. I would like to bring up a list from the County_names field that begin with a State that matches the value in the Site State field. I can't figure this out, can anyone help a new mysql user?

$countyresults=mysql_query("SELECT ct.*, s.State, s.Site_ID
FROM county_tbl AS ct, site_tbl AS s
WHERE s.Site_ID='$ID'
AND ct.County_name LIKE 's.State%'
ORDER BY County_name");

Select Query Help Please
The below query works well for returning 1 result however I now need to expand it to return multiple results. At the moment I feed it 1 car type and 1 company id and it spits out one result, I then loop the query to get the number of results I need - but this is very inefficiant so I want to change it so I pass all my variables at once.

So where I have cars.type =Ƈ' I want to have car.type =Ƈ' or cars.type=ƈ' which in itself works but how to I extend the company section as when I add the extra 'Or's' in there the query goes wacky and spits out hundreds of results which are all the same - I think its something to do with the inner join but im not sure.

MySQL
SELECT prices.single_f,
prices.return_f,
prices.vehicle,
resort.company_id,
resort.resort,
cars.type,
cars.description,
cars.long_description,
cars.image,
surcharges.type,
surcharges.adult,
surcharges.kids,
prices.timefrom,
prices.timeto,
prices.resort_id,
cars.min,
cars.max,
company.is_enabled
FROM prices
INNER JOIN resort ON resort.id = prices.resort_id
AND resort.company_id = Ƈ'
AND resort.resort = 'Acoteias'
INNER JOIN company ON company.id = Ƈ'
INNER JOIN cars ON cars.type = prices.vehicle
AND cars.type = ƈ'
LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id
WHERE prices.break <= ïb'
AND company.is_enabled = 'Yes'
AND (
(
prices.timefrom <= prices.timeto
AND prices.timefrom <= ཁ:00:00'
AND prices.timeto >= ཁ:00:00'
)
OR (
prices.timefrom >= prices.timeto
AND prices.timefrom <= ཁ:00:00'
AND ན:59:59' >= ཁ:00:00'
)
OR (
prices.timefrom >= prices.timeto
AND ༼:00:00' <= ཁ:00:00'
AND prices.timeto >= ཁ:00:00'
)
)
LIMIT 0 , 30

Select Query Help
How do i query something like:

select id from table-name where number=1234 , 123123 , 32123 , 123123 , 123123 , 123123

id & number is int variable.

it works if i do it one at a time.

eg: select id from table-name where number=1234;

and so on ....

Can't Use Select...IN Inner Query
I'm trying to make the following SQL query and keep getting errors:

select * from mytbl where reportid in (select reportid from mytbl where level>60)

I get this error:
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 reportid from mytbl )' at line 1

When I'm running the inner query (select reportid from mytbl where level>60) it is running OK. Also, when I'm running without inner query (select * from mytbl where reportid in (1,2,3)) itis running Ok.

But Somehow I can't run it together.

I'm using MySQL 4.0.14.

Using IF In A Select Query
I want to say in mysql that if a field is NULL, then ignore it in a select query statement like this -

$result = mysql_query("SELECT
                id,
                head,
                IF(blurb IS NULL, SUBSTRING(story, 1, (locate( ',', story, 50)))) AS blurb, etc etc

Is there a way?

Select Query
use a select statement in a update query but it doesnt like the select bit... here is what i have

$query = "UPDATE virtual_bc SET assigned = 'p' WHERE virtual_id IN ('SELECT * FROM virtual_bc WHERE assigned = 'n' ORDER BY virtual_id LIMIT 1')";
$result = mysql_query($query) or die(mysql_error());

Select Query
I'm trying to get a select query working but am having problems. I have a table that has survey anwer results. The two items each entry has that I need to pay attention to are the user who entered the info and the survey they were taking. How can I write a select statement that will check if a user answered more than one survey? One survey is easy:

SELECT distinct userID from surveyResults where surveyID='11'

But what do I do if I want to see who took survey 11, 12, and 13?

SELECT Query
When using a simple SELECT query, how to make all the rows in the resultset to be numbered?

Let's assume that a query resulted in 1000 rows - I want all of them to have a number (1 to 1000) in front of them (in a special column) to be able to read what item is on row 354 and what is on row 767 (for example). With another sorting (using ORDER BY) the sorted data should be again numbered from 1 to 1000 in an ascending way (the number is not firmly assorted to a special row, it changes depending on the sorting order).

Select Query
The below query is pulling all records no matter what value 'LOCATION' holds. LOCATION holds the users dept. and is fine so I think it is a problem with the conditions. I'm trying to selct all records "where nsi.orderid= '$orderid' || nsi.recipient= '$orderid'" but nsi.location=LOCATION.

SELECT * FROM nsi
where nsi.orderid= '$orderid'
|| nsi.recipient= '$orderid'
AND nsi.location='".LOCATION."'
ORDER BY orderid"

Select Query
I have a table that has a column for surnames and another with firstnames, among others. What I am trying to do is a "select query" to drag out all the unique people sharing the same surname. For Eg, if I have Jo Blogs, Jim Blogs and Edna Blogs each with 50 rows of data, I only want to display the people that have the lastname "blogs", like Jo,Jim and Edna with no other data, just the three lines. The closest I have come is as follows:

SELECT
m1.firstname,m2.firstname
FROM
  Member m1
INNER
  JOIN
    Member m2
    ON ( m1.firstname != m2.firstname )

WHERE
  m1.lastname LIKE 'Blogs'
  and m2.lastname LIKE 'Blogs'

Select Query
lets say i have a poll where users can vote on the following
1. Good
2. Bad
3. Ugly

i want to write a query (from PHP) that will let me select and order based on how many each has...so if 2.Bad has the most votes, display it first...if 3.Ugly has the second most votes, display it second.my sql query skills...

Select Query
I have a mysql table with about 2,000,000 rows. Whenever I run this query

select * from table limit 1000

it takes less than 1 second to return. But when I run this query

select * from table where Destination like '%talk%' limit 1000

it takes about 70 seconds to return the result. Destination column has an index. Is this a normal response from this size of a table?

SELECT Query
I use php and MySQL.

User fills out search form.
There can be possiblities:
1) user knows the full name
2) user knows first letter(1 or 2)
3) user leaves the field empty meaning - look for any name

How do formalte my query?

SELECT * FROM table WHERE AGE>20 AND NAME='';

Select Query
I need to construct a mysql select statement that will rank the rows returned such that the more hits per keyword the high rank within the results.
For example, lets say I have the keyword phrase:
"keyword1 keyword2 keyword3"

I need mysql to search the text field in such a way that it will rank multiple matches on any of the keywords such that the more a keyword is found within the text, the higher it will go within the returned rows.
So far I have been trying:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('keyword1 keyword2 keyword3');

...this has had limited results.

SELECT Query
I got 3 relevant tables:

'links': ID, Link, UserID, SiteID
'sites': ID, Name
'user': ID, Flag, Name

Now I would like to SELECT all Links from users with the Flag "Premium".
Simple example would be, I got 10 users, 4 of them with the 'Premium' Flag. All of those 10 users got about 4 links in the link table. Now I'd only like to select the links which belong to the Premium Users(=flag field in table user).

SELECT Query
I have a table called thingamajigs which i keep tabs of product colors.
In thingamajigs there are id, productid and productcolor fields. There could be dozens of productcolors for a product id - resulting in multiple entries of the same productid in this table.I need to find out all productid's where there is availability in both Red and Blue.

I thought my query should look something like this:

SELECT productid FROM thingamajigs WHERE productcolor='Red' AND productcolor='Blue'

But, that returns zero matches -- even though I know there is at least one productid which is available in both Red and Blue.

SELECT Query.
Table

1 3 #1,#2
2 5 #2,#3
3 3 #1,#2,#3
4 4 #4
5 5 #1,#2,#3
6 5 #1,#2,#3
7 4 All
8 3 #1,#2,#3

Need to compare the value of field2 with the occurrences of "#" in field3.
Tried this:
$query_plcResultsSet = "SELECT * FROM Table WHERE RteType IN ('Mainline', 'Connector') AND StartDateD = '$strchvarval' AND (field2 = 'All' OR " . substr_count(field3, '#') . " = field2)";

Select Query
premise:running mysql 4.0.20
i got a table with following columns

Code:
win1 win2 lose1 lose2
a b c d
a d
b c a e
a d
d a c e

so basically its like this..i got teams a-e
win1 and win2 are the 2 teams that wins (if theres an teamate then there is win2,otherwise, he won it alone) and same principle goes for the losing teams..
so how does the select query goes if i want to select all teams and order em by the amount of wins they have (win1 + win2)

Select Query Prblems
The below query is prelexing me - basicaly when you leave the search filed empty is to display all columns below where status = 'ON', but it does not - it display all no matter the status.

SELECT id, name, jobtitle, date, location, salary_from, salary_to, job_desc
FROM vacancy
WHERE status = 'ON'
AND id LIKE '".$searchterm."'
OR jobtitle LIKE '%".$searchterm."'
OR location LIKE '%".$searchterm."'
OR salary_from LIKE '%".$searchterm."'
OR salary_to LIKE '%".$searchterm."'";

More Complex Select Query
i tried to do this also looking at documentation from mysql site, but keep getting error messages:
basically i got data on a table, there is a field 'views', and a field 'published_on', related to articles.
what i need is a query that selects the articles and orders them according to views/day.
so, i'd need some operation that first figures how many days the articles have been up:
DATEDIFF(NOW(), published_on)
then, i gotta divide that by published_on, and make the query return first the articles that have had the most views a day.
any idea how i can accomplish this?

Mysql Select Query Help
i am working with a shop script that i have inherited and i want to add the ability to have products in multiple categories.

To do this i have created an extra field in the product table called cat_id_2.

Now on the list products page I have the follwing call at the moment, which pulls all of the products for the current category...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
FROM tbl_product pd, tbl_category c
WHERE pd.cat_id = c.cat_id AND pd.cat_id IN $children AND pd_status=1
ORDER BY pd_name";
I tried to change this myself and added the extra paret to the WHERE clause and came up with this...

$sql = "SELECT pd_id, pd_name, pd_price, pd_author, pd_thumbnail, pd_qty, c.cat_id, c.cat_description, c.cat_name
FROM tbl_product pd, tbl_category c
WHERE (pd.cat_id = c.cat_id OR pd.cat_id_2 = c.cat_id ) AND pd.cat_id IN $children AND pd_status=1
ORDER BY pd_name";
But for some reason, instead of putting a product in two categories it puts the same product in the category twice, - its wierd.

Can anyone see anything wrong with what i have tried?

Select Query (date)
How would I set the WHERE condition to select the fileds below to select only those who were placed 2 years ago?

mysql> SELECT placement_date, name, age
-> FROM table1
-> WHERE

Difficult Select Query
Here's the scoop,

I need to list the newest 5 works or series(group of works) that are available

Don't list a work if it's in a series. Do list if it's not.

The result set will just be w_id , finished, and s_id, but s_id will be null for works that don't belong in a series

The series date == date of the newest finished work in that series

Here's the table structure

work
---------
id primiary
finished date

work_series
-------------
w_id int
s_id int

series
-----------
id int

Optimizing A SELECT Query
SELECT id, gameid, name, type FROM media ORDER BY date DESC LIMIT 5
SELECT * FROM games ORDER by wishlist DESC LIMIT 5'
SELECT * FROM games ORDER by mygames DESC LIMIT 5
SELECT mediaid, count(*) AS number FROM download WHERE date > &#55614;&#57159;-08-14 10:44:43' GROUP BY mediaid ORDER by number DESC LIMIT 5
SELECT * FROM news ORDER BY id DESC LIMIT 14'
SELECT gameid, count(*) AS number FROM views GROUP BY gameid ORDER by number DESC LIMIT 10
All of these queries I have determined are inefficient (using some tools) and they all have the same thing in common. They're all trying to find the "Top 5" or "Newest 10" or whatever from a table, but in doing so they are going through every single value and comparing them.

What other way is there to do this same idea of getting the "top 5" of a certain column values, without the SQL having to check every entry?