Select Against Alias

SELECT Alias
Is it possible to use an alias in the expression for another alias, for example:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + tax AS gross
FROM items

But that dosent work, mysql dosent allow an alias in the SELECT statement (says it cant find a field called tax), which is kind of annoying as it means i have to duplicate all my calculations like this:

SELECT net,
tax_rate,
net / tax_rate AS tax,
net + (net / tax_rate) AS gross
FROM items

This might not seem like a big deal in this query, but i have some other far more complicated queries where its just a pain. Is there any way to do this, it seems like it should be possible.

---

Can I Check The Value Of An Alias(i Think Its Called An Alias) In A Query.
I was wondering can I check the value of an alias(i think its called an alias) in a query.

At the moment I have this but its not working:

MySQL Code:

SELECT  * , ( DAYOFYEAR( ToDate )  - DAYOFYEAR( FromDate )  ) AS NumDays
FROM  `tbl_courses`
WHERE NumDays < 3 AND NumDays > 2 AND FromDate >=  &#55614;&#57159;-10-17'
ORDER  BY FromDate

---

Alias Name Not Known
The bug I get is "Unknown column 'debt' in 'where clause'" . Why?

SELECT * , ventas.importe_acordado - SUM(cobros.nombre) , ventas.importe_acordado as nombre_venta
FROM ventas
INNER JOIN cobros
ON cobros.venta = ventas.id
WHERE debt > 0
GROUP BY ventas.id

---

Alias Error
error: Not unique table/alias: 'u'

sql: DELETE u.*,i.*,p.* FROM onig_users u, onig_users_info i, onig_users_profile p WHERE u.users_id=i.users_id AND i.users_id=p.users_id AND u.users_id = 23

i use this same syntax for a different design and had two tables instead of three and it works so I'm confused. Is there something different I should be doing for three tables?

---

Database Alias
Is it possible to create a database alias? I would like to have something like a symlink to another database. Let's say we have database "gurg" and this one should also be accessible through the name "garg". Is there a way to achieve this?

---

Dynamic Alias
I'm trying to output the previous 12 months and have the month name as the column heading. It doesnt want to let me use a function as the alias.

CODESELECT Department,
SUM(case when Month=MONTH((CURDATE() - INTERVAL 11 MONTH)) then Total else '0' end) AS DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%M-%Y'),

---

Sum Alias Column
I have a database mysql 5.0.51a-community with bookings.
I want to know how many days or weeks has been rented last year compared to this year.
The bookings I insert with arrival and departure date in 2 date type columns.
I do not know how to do it, they way I see it, I must count the days in every booking then sum the days.This seems a good way to me, but gives me error #1054 - Unknown column 'dias' in 'field list'
SELECT arrival, departure TO_DAYS(departure) - TO_DAYS(arrival) as days, typebooking, SUM(days) FROM bookings where typebooking = 'client' and year(arrival) = '2007'

As I understand the error, I can´t sum an alias column,...I could maybe do it with php, but don´t want to do a page for it, and must be a way to do it just with mysql.

---

Alias Problem
I get this error Unknown column 'n.nid' in 'on clause' when I run this query.Code:

SELECT DISTINCT(n.nid), e.event_start FROM event e
INNER JOIN node_access na ON na.nid = n.nid
INNER JOIN node n ON n.nid = e.nid
WHERE (na.grant_view = 1 AND
CONCAT(na.realm, na.gid) IN ('all0','simple_access0'))
AND n.status = 1 AND (
(e.event_start > 1138766400 AND e.event_start < 1141185599)
OR (e.event_end > 1138766400 AND e.event_end < 1141185599)
OR (e.event_start < 1138766400 AND e.event_end > 1141185599))
ORDER BY event_start;
Since node n is declared I can't see what's wrong.

---

Trouble Ordering By An Alias
Probably something silly here... why am I unable to specify my custom ordering?

SELECT
CASE
WHEN temp.`time` < 11 THEN &#390;-10 seconds'
WHEN temp.`time` >= 11 AND temp.`time` < 31 THEN &#3911;-30 seconds'
WHEN temp.`time` >= 31 AND temp.`time` < 61 THEN &#3931;-60 seconds'
WHEN temp.`time` >= 61 AND temp.`time` < 181 THEN &#391;-3 minutes'
WHEN temp.`time` >= 181 AND temp.`time` < 601 THEN &#393;-10 minutes'
WHEN temp.`time` >= 601 AND temp.`time` < 1801 THEN &#3910;-30 minutes'
ELSE &#3930;+ minutes' END AS `visitLength`,
COUNT(*) AS `count`
FROM (SELECT MAX(`time`) - MIN(`time`) AS `time` FROM test_log GROUP BY `visitorId`) temp
GROUP BY `visitLength`
ORDER BY FIELD(`visitLength`, &#390;-10 seconds', &#3911;-30 seconds', &#3931;-60 seconds', &#391;-3 minutes', &#393;-10 minutes', &#3910;-30 minutes', &#3930;+ minutes');

ERROR 1054 (42S22): Unknown column 'visitLength' in 'order clause'
Without an ORDER BY, I get the rows I want, without the ordering of course:

+---------------+-------+
| visitLength | count |
+---------------+-------+
| 0-10 seconds | 3823 |
| 1-3 minutes | 796 |
| 10-30 minutes | 394 |
| 11-30 seconds | 430 |
| 3-10 minutes | 682 |
| 30+ minutes | 666 |
| 31-60 seconds | 236 |
+---------------+-------+
7 rows in set (0.23 sec)

---

Can A Query Alias Be Used As A URL Parameter?
Question: Is is possible to use an alias created in a MySQL query as a URL parameter? (I'm not sure if this is a MySQL issue, a PHP issue, both, or neither). My database table uses self-referencing ids to establish hierarchy. In the temporary example below flow is my PK, and both parent and top are self-foreign. .....

---

Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error.

We can use alias names in WHERE clause.

Is there any solution or other way to achieve same functionality?

QUERY:
select concate(firstname, " ", lastname) as name
from user
where name = "Chetan Parekh";

---

Alias Using Unicode Character
how to use the micro sign in an alias? I want the column name to appear as "Chl (µg/L)".

---

Column Alias On Mass
Is possible to retrieve all columns and alias them all at once. Eg, normally
you would write

select * from products which would return

id | name | price
-----------------------
1 | Test | 14.00

but I want to be able to say

select p.* from products p, so that it returns the columns as such

p.id | p.name | p.price

Obviously I can do this manually as such

select p.id, p.name, p.price from products p ....

But that would take a lot of big queries as some of my tables have 50
columns.

---

Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases
but the alias I am using has a space in it, eg 'Device ID'

My querry looks like (simplified, devID is a calculation)

SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123'

and it does not work

but if I use

SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123'

everything works.

How can I use an alias that contains a space in the HAVING clause?

---

Every Derived Table Must Have Its Own Alias
i have a sub-query i'm trying to execute as follows....and i'm getting the above error message.....

select z.date from (select x.date from bsmtruckrecord as x where x.employee_ida=17) where z.date in (select y.holiday from bsmpublicholiday as y) as z

i have referenced all tables by an alias and i'm still getting the error message saying....
"every derived table must have its own alias"

---

Not Unique Table/alias
I've written SQL before and I'm sure this is close to correct, but I have never written SQL for MySQL. Where am I going wrong? I'm getting a Not unique table/alias: 'movies' error.

SELECT movies.ID, movies.Title, movies.GenreID, movies.RatingID, movies.UserID, Genres.ID, Genres.genre, Ratings.ID, Ratings.rating, Users.ID, Users.FirstName, Users.LastName, Users.Email, Users.Phone
FROM ((movies INNER JOIN Genre ON movies.GenreID = Genres.ID), movies ON Ratings.ID = movies.RatingID), movies ON movies.UserID = Users.ID

---

Getting Not Unique Table/alias
Can anyone tell me why im getting this error;

Not unique table/alias: 'q'

When running the following query;

DELETE q.*, r.* FROM questions AS q, replies AS r WHERE r.id_question = q.question_id AND q.question_id = 55

When were alias's first done be mysql as i've just discovered that my provider are running version 4.0.25.

---

Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this
SELECT money1+money2 AS money WHERE money > 10000
but mysql return an sysntax error Is there any way to use a varible like this in a query?

---

Union Result Going To Other Table Alias
I need to check two tables for a value of 'pending'. Now, this value might exist or might not. So i figured a union would do the trick.


MYSQL
SELECT cats AS alias_one FROM table1 WHERE FIELD = 'pending'UNIONSELECT dogs AS alias_two FROM table2 WHERE FIELD = 'pending'
but if table2 is the only table with the result, mysql returns the contents of col_two but under the alias: alias_one.. how can i get around this?

---

Not Unique Table/alias: 'tbl_products'
I have the following

SELECT tbl_products.product_id,
tbl_products.product_MerchantProductID,
tbl_prdtscndcats.scndctgry_ID,
tbl_prdtscndcats.scndctgry_Name,
tbl_prdtscndcat_rel.prdt_scnd_rel_product_id,
tbl_prdtscndcat_rel.prdt_scnd_rel_scndcat_id
FROM tbl_products, tbl_prdtscndcats, tbl_prdtscndcat_rel

LEFT OUTER
JOIN tbl_products
ON tbl_products.product_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id
LEFT OUTER
JOIN tbl_prdtscndcats
ON tbl_prdtscndcats.scndctgry_id = tbl_prdtscndcat_rel.prdt_scnd_rel_product_id

It's saying Not unique table/alias: 'tbl_products'.

Is there anything wrong with the SQL?

---

Cannot Refer To Alias In Mysql Calculation
Allright, newest problem. I am trying to do most of my calculations in mysql rather than PHP since that makes using the data a lot easier. The problem is I can't seem to use aliases I've assigned to data in further operations.

This query:

SELECT
SUM(CASE WHEN correct = 'y' THEN 1 ELSE 0 END) as top,
SUM(CASE WHEN correct = 'n' THEN 1 WHEN correct = 'y'
THEN 1 ELSE 0 END) as bottom,
ROUND((top/bottom)*100,1) as percent
FROM quiz_answers
LEFT JOIN quiz ON quiz.ID = quiz_answers.quizid
LEFT JOIN cadets ON quiz_answers.cadet_id = cadets.cadet_id
WHERE flight='$row[flight]' AND due>='$start' AND due<='$end'
Does not give me the percent. I get this error.
Quote:

Unknown column 'top' in 'field list'

That is true, there is no column top I wanna use the sum i just assigned as top.

I don't want to put the SUMs in the round function because I may need to use that data too.

---

Derived Table Alias Error
[MySQL][ODBC 3.51 Driver][mysqld-5.0.24-community-nt]Every derived table must have its own alias

is the ERROR displayed and my QUERY is....

---

Use An Alias Name For A Field In A Join Ation
I'm using a select like this:

SELECT * FROM `CPESER` INNER JOIN `CONCPE` ON `CPESER`.`id_concpe` = `CONCPE`.`id`
LEFT JOIN `CADSER` ON `CPESER`.`id_cadser` = `CADSER`.`id` LEFT JOIN `CADPRO`
ON `CADSER`.`id_cadpro_data`= `CADPRO`.`id` AND
`CADSER`.`id_cadpro_voice` = `CADPRO`.`id` LEFT JOIN `CONASS` ON
`CONCPE`.`contract` = `CONASS`.`contract` INNER JOIN `CADCLI` ON
`CONASS`.`id_cadcli` = `CADCLI`.`id` ORDER BY `CADCLI`.`name`

but there is a problem: some colums has the same name... it's possilbe to use aliases to the fields?

---

Alias Mysql ERROR 1064
I am trying to configure the mysql install, following the instructions in the manual, specifically the alias to the mysql path and I get the following error:

mysql> alias mysql=/usr/local/mysql/bin/mysql/
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alias mysql=/usr/local/mysql/bin/mysql/' at line 1

---

Create A Dynamic Alias Name Using A Function
Is this possible?

SELECT

sum(if(EXTRACT(YEAR_MONTH FROM hitdate) = EXTRACT(YEAR_MONTH FROM
now()),1,0)) as EXTRACT(YEAR_MONTH FROM now())

FROM TABLE_NAME

---

SQL, Field Name Alias For Write Access
I get an error on the adorec.update line - Unknown column 'MyName' in 'field list'. The code will work for read access but won't let me write to it.  How do I update a field with an alias?

  I know i can lose the "AS" part but that would defeat the purpose of what I'm accually trying to do.

---

Strange Alias And Join Problem On Server But Not Dev Box
I'm running MySQL 5.0.45, php5 and apache 1.3 on windows xp for development. All my tables are MyIsam.
The following query works fine :

MySQL
SELECT
 i.image_id, image_filename, region_name, monument_name
FROM
 image AS i
LEFT JOIN
 image2region USING(image_id)
LEFT JOIN
 region USING(region_id)
LEFT JOIN image2monument AS i2m ON (i.image_id=i2m.image_id)
LEFT JOIN monument USING(monument_id)
WHERE region_name='egypt'
LIMIT 0, 8
On the server (MySql v5.0.15) where I plan to host the site I get the following error:

#1054 - Unknown column 'image_filename' in 'field list'

If I give all the tables aliases and prepend all the column names after the SELECT with the aliases it works but I don't want to have to do this with all the queries I've written. I don't understand why it doesn't work on my dev box but not the hosts server.

I have also noticed that this problem only seems to arise on queries with more than two joins.

Has anyone else experienced a problem like this?

Or should I be prepending all columns with aliases as a matter of good practise anyway?

Cheers x

---

Error 1066 Not Unique Table/alias
I want to select 2 names from Person table. I think a join table would be overboard for this simple requirement, let me know.

person (table)
- id_person
- name

place (table)
- id_place
- name
- id_person1 [fk person.id_person]
- id_person2 [fk person.id_person]

SELECT place.name AS place_name, person.name AS name1, person.name AS name2
FROM place
INNER JOIN person ON place.id_person1=person.id_person
INNER JOIN person ON place.id_person2=person.id_person

ERROR 1066 (42000): Not unique table/alias: 'person'

Wanting output as
place_name: "This Place"
name1: "Fname Lname"
name2: "Fname Lname"

---

Derived Tables / Alias Error When Doing A Union
I am running version 4.1.1a of MySQL server, and I am running into a problem. The below SQL query causes an error, saying:

[MySQL][ODBC 3.51 Driver][mysqld-4.1.1a-alpha-nt]Every derived table must have it's own alias

I have looked at this query up and down and can't find anything wrong with it. I did find somewhere that there is possibly a bug with MySQL and this issue? But according to what I read, it had already been fixed in this version? Is the probem with my code, or is it a bug in the MySQL version that I have? Code:

---

ERROR 1066: Not Unique Table/alias: 'person'
The database 'testdb' has 2 tables, 'person' and 'team', which have the same structure. I tried the query "LOCK TABLES team READ, person AS team WRITE;", and got the following error: ERROR 1066: Not unique table/alias: 'person' What is the worry?

---

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.

---

Not Unique Table/alias: <tablename> For Temporary Table
I’m converting an application from Paradox to MySQL (first time I’ve used this) and am replacing Paradox local tables with MySQL temporary tables – I understand each user will have a unique copy of the table. Basically I issue the following two commands :

'drop temporary table if exists <tablename>'

'create temporary table <tablename> like <tablename>'

where <tablename> is a table structure in the database with 0 records. This strategy was successful while I was working with a local copy of MySQL on my development PC, but when I run the system from a server on the office network I get the following error :

#42000Not unique table/alias: <tablename>

The environment I work in is Windows XP Pro SP2, Delphi 5 and CoreLabs MyDAC data access components. I’ve searched this site and Googled to no avail. Hoping someone has seen this before.

---

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?

---

Differentiate Between Column Alias And Other Column With Same Name
Sample SQL:
SELECT
a,
(SELECT a + 3) AS b,
(SELECT y FROM t2 WHERE b = b)
FROM t1

1. SELECT a + 3 will calculate a column with alias b
2. t2 has a column named b

Question: How can I differentiate between column alias b and t2.b. MySql treats alias b as t2.b in statement (SELECT y FROM t2 WHERE b = b)

---

Using A Field Alias In Another Field
I'm migrating a DB from Access to MySQL and I having problems with queries similar to this:

SELECT 1 AS x, x+1 as y;

I get a "ERROR 1054 (42S22): Unknown column 'x' in 'field list'" error.

Is there a way to do this without calculating 'x' twice?

---

SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity.

Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ?

How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?

---

Combining SELECT Statements Into One SELECT Statement.
I want to take the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN authuser
ON name.empnumber = authuser.uname AND authuser.team = 'PHQ'
ORDER BY name.lastname, name.firstname;


and the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN crew_attendance_6QJ
ON name.empnumber = crew_attendance_6QJ.empno
ORDER BY name.lastname, name.firstname;


And combine them into one query that outputs all of the results both queries would output. Then order those results.

So far I have come up with:

---

Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.

---

Select From Two Different Tables Depending On Result Of First Select
I have three tables A contains three keys (there is other stuff but it's not relevent to this problem):

  it's own pk,
  fk to table B
  fk to table C

if the table B fk is not zero then select various values from table B,
if the table C fk is not zero then select varios values from table C

The two fk are never both zero and never both non zero

The best query I've got so far is

select distinct l.job_parts_id, l.part_id, l.oil_id, l.quantity, l.purchase_price, l.sale_price,

p.part_no, p.description, p.supplier_id,
o.part_no, o.description, o.supplier_id

from job_parts l, parts p, oil o
where task_id = 3
and p.part_id = l.part_id
or o.oil_id = l.oil_id

order by job_parts_id;

the problem at the moment is that when the fk to table c is zero, the result set still contains the values from it's selected fields, I'm trying to work out how to use an IF statement to sort this out,

---

Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends.

Will like to know:

Assuming I have a table with 8 columns.

When I am querying these records with specific WHERE conditions,
will SELECT * be faster, or SELECT (3-5 x columns) be faster?

---

Select Lastest Input To Data Base And Select Ordering From Last Member To First Member, Limiting Output Diplay To A Specific Number
how to display the latest members that signup to website, a query that that can select ordering them base on the last registered member to the first... then limiting output by say 50 members.

a sql statement that can select lastest input to data base and select ordering from last member to first member, limiting output diplay to a specific number

---

SELECT COUNT(*) Or SELECT .. LIMIT 1?
If the purpose of a query is to see if atleast 1 record exists which would be faster?

SELECT COUNT(*) or SELECT .. LIMIT 1?

Both would use a WHERE clause.

---

What's The Best SELECT In This Case / How Can I Make This SELECT ?
well, step 1, I need to get a few ids like:

PHP

SELECT id FROM $table_one WHERE this_field=$that_value // this is OK


I will get multiple rows from this command

Then, I need my main SELECT command (that i need to use) like:

PHP

SELECT * FROM $table_two AS t2 LEFT JOIN $table_three AS t3 ON t2.pid = t3.pid WHERE t2.pid = (all the ids that I have got from my first SELECT)

---

Difference Between Select * And Select Distinct *
What is the difference between SELECT * and SELECT DISTINCT *
Which one is faster?

---

Select Constraint (select ... Where X) If 'where X' Isn't Specified
I'm working on a site where records (say articles) can exist in one of three states: active, archive, trash. I'm doing this so that I can implement soft delete and non-destructive action.

For some brief background, we used to simply have an 'archive' column with enum('n','y'). Which meant that every query had to be appended with "where archive='n'". Later we modified the architecture of the application be more robust so that you could publish records (basically duplicate the records into a table called articles_instance; so you can publish as many versions of a master copy as you want). This worked well enough that when I decided to implement soft-delete, I decided to move the deleted record into the articles_instance table. I created a new column ('state' again) with 'publish', 'archive', and 'trash'.

So whenever I query the instance table, I opt in "where state='publish'" instead of having to opt-out, like before ("where archive='n' and trash='n'". This was better, but I realized that I had to save the old record id in a column 'old_id' because I wanted to be able to send the row back into the regular 'articles' table if the user chose to undo the action or unarchive an article. I decided to keep the old id, rather then send it back with a newly generated id because there were certain relations I needed to keep, like associated audio files with the article (temporary broken links aren't as much of a problem because you can only see the audio when you look at the article; when the article is permanently deleted, then I can remove the relations).

This approach works, but I feel it's messy, and with all the code I'm writing to shuttle the rows back and forth, I feel as if there's only more chances for error. I like the simplicity of simply setting a state in the master 'articles' table. But then I have the problem of having to specify "where state='active'" in all of my queries. If I don't do that, I risk pulling all possible articles, regardless of state, causing strange problems. So here's the latest breakdown:

The articles table has a 'state' column with enum('active', 'archive', 'trash').

The articles_instance is now called 'articles_publish' and no longer has a state column. So it is used exclusively for published articles.

It boils down to this: Is there a way, in MySQL 4 (I don't know the exact version, but I can find it if necessary) to constrain/modify all of my select statements to have an implicit "where state='active'" if a state isn't explicitly specified? If I could do this, I feel I could have the best of both worlds: have two tables with specific needs, where one table has data that can exist in different states, and I wouldn't need to move rows back and forth, possible causing problems. And if I implement the constraint, I don't have to worry about going through and changing a lot of existing code and making sure the problem doesn't crop up in the future.

I am providing all this background in the hope that someone might just suggest a better and more efficient way of doing this and tell me that a select constraint is entirely unnecessary . I might also be convinced that using MySQL as a crutch in this case might be a poor idea and could potentially confuse programmers down the line (although there's only two of us).

If it's not already clear, I'm new to all of this, so I am not really familiar with best or standard practices when it comes to these sort of things. If anyone wants to refer me to any resources (books or articles) about versioning/publishing records or database design in general, I'd love to hear them.

Thanks!

---

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);

---

Select In Select Statement
Could any give me an example of how this works please as for the life of me cant get any results.Basically I have 3 tables, I record home team and away team results in the RESULTS table and up and coming fixtures in the FIXTURES table.

Im wanting to get all my home teams previous results where they are playing at home in an upcoming fixture but also want to return for the same fixture the away teams previous results, I dont even need TEAM table to be honest but thought may be easier to create a join between the two tables.

---

Count(*) In A Select Returns &quot;1&quot;. It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

---

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...



SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using


SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is


id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

---