MYSQL :: SQL Select Statement Returning NULLS

Syntax For Select Statement Returning Output?
I have an application that I am converting from MS SQL to MySql.

I need to return a value from MySql using a parameter.

In MS SQL my select statement is:
"select @id = id from database"

That doesn't seem to be working with MySql... does anyone have any
suggestions?

MYSQL Statement Not Returning Data...
Is there something I am doing wrong in the following query:
SELECT * FROM 'photos' WHERE approval='NULL' ORDER BY 'reg_date' ASC LIMIT $page, $limit".
It doesn't seem to target the approval field in the photos table in which all current values are NULL... or am I targeting NULL incorrectly? The field is a tinyint(1) so it can be 0 for NO, 1 for YES, or NULL so that I can target photos that have not been checked yet. It runs fine but returns no results when it should return all current rows in the table.

Prepared Statement In Stored Procedure Not Returning Rows To App
I have a stored procedure that I'm using to allow users to perform a search on the database. I'm compiling the select statement in the stored procedure and then using PREPARE, EXECUTE, DEALLOCATE.

When I run just the stored procedure it works. When I call it from the app it returns 0 rows. If I just put the SQL statement without using PREPARE it returns rows. Here is the DDL for my stored procedure:

Simple Select Returning More Than One Rows
I would like to ask if it's possible to do a select 1,2,3 and to be returned 3 rows and 1 column with values 1 for the first row,2 for the second etc. select 1,2,3 returns me a row with 3 columns.

MYSQL SELECT Query Not Returning Wanted Result
MS SQL Server has support for cascading update or delete. Does MySQL have anything like that?

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.

NULLS 2
if I have a table for:

PHONE_NUMBERS

that can be associated with FKeys to one or more COMPANY_ID and/or a PERSON_ID how would you construct the columns/relationships?

Assuming a PHONE_NUMBER_ID as PK then a n:m relationship to both the COMPANY and the PERSON tables??? (i.e. a new table with a compound PK of phone_number_id plus company_id plus person_id) but then I'd have to allow either the company_id or the person_id to be null wouldn't I?

NULLS
if you had a table with columns:

Prefix_ID ---> data for say 75% of rows normalized to Mr/Mrs./Ms
FirstName ---> data for 50% of rows
MiddleName ---> data for 25% of rows
LastName ---> data for 100% of rows
Suffix_ID ---> data for 5% of rows normalized to Jr./Sr./III

would you:

1.) make suffix_ID NULL-able ?

2.) make any other column NULL-able?

3.) make Suffix_ID default to 1 where Suffix_ID 1 = ''?

4.) make FirstName and/or MiddleName default to '' ?

or other alternatives/suggestions for best practices?

Concatenating Nulls
I would like to concatenate two columns where one is potentially null. The result I get now is if one is null, the concatenated value is null. Here's what I would like:

Column1 = '123', Column2 = null, return '123' (not null)
Column1 = null, Column2 = 'abc', return 'abc' (not null)
Column1 = '123', Column2 = 'abc', return '123abc'

The statement that isn't working is something like:

SELECT CONCAT(Column1,Column2) FROM Table WHERE id=12

Joins And Nulls
I have three tables containing information about companies. The first lists companies, each with an id. The second defines what information (attributes) can be held about each company and format it takes (eg address, postcode, owner, etc). The third table lists the specific values of each attribute for each company.

Each attribute value for each company has its own line.

Now, I am trying to select data from this group of tables. The problem is that not every company has complete information. Some, for example, don't have a postcode. But where they do, I'd like to display that. Which brings me to this SQL: Code:

NOT NULLs With Default
I'm cleaning up a database but I see this all over the MySQL world. I understand that having defaults speeds things up. But what I don't understand is why someone would make a field NOT NULL and then put a null default in it? This makes no sense to me at all. Is this the fault of using phpMyAdmin to create tables?

Here's a sample:
CREATE TABLE `listnames` (
`id` int(11) NOT NULL auto_increment,
`list_name` char(30) NOT NULL default '',
`display_name` char(100) NOT NULL default '',
`active` char(3) NOT NULL default '',
`last_modified` timestamp(14) NOT NULL,
UNIQUE KEY `id` (`id`)
)

Ok, so you don't want NULLs in your data but there is no default. It's very likely that the data will be wildly differently. So setting a default makes no sense -- or does it?
I don't want to drop the default '' if it makes sense & I'm just not getting it.

Finding NULLS
I am querying with the following hoping NULL and not NULL:

SELECT *
FROM pet
WHERE sex='NULL';

returns empty set, but there should be one record.

SELECT *
FROM pet
WHERE death<>'NULL';

also returns empty set when there should be one record.

Count Of NULL And NOT NULLS
How would I write a query to count the number of instances where a the
PREP field is NULL and when it is not null. I know how to do it
seperately, can I get the numbers in the same query. I want something
like this:

Project #PREP_NOTNULL #PREP_NULL

Ignoring NULLs In Indexes
Access has a nice feature that allows creation of indexes WITH IGNORE NULL which means that records with null values are ignored in the index, so the index will be
shorter and faster to search. Is there any equivalent for this in MySQL?

Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?

Help Me With This Select Statement :)
I have two tables
1- articles table which contains the articleid and article_description
2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description

I would like to select all the articles which aren't translated yet... How can i do this in MySQL?

Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements:

Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following:

SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g

This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?

Need Help With SELECT Statement
I have 2 simple tables: emps and rprts.
Table emps has: "ID", "name" (simple string), and some other irrelevant fields.
Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean).

I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest.

For example:
emps:
id name
------------------
1 jeff
2 wanda
3 brad

rprts:
id emp_id rprt type
-------------------------------------------
1 3 df 1
2 1 ie 1
3 2 ei 1
4 3 fd 0
5 2 tg 0
6 3 gf 1
For this data, the following should be selected:

id name type
----------------------------
1 jeff 1
2 wanda 0
3 brad 1

I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.

Select Statement
SELECT *
FROM Distance, LorryDetails
WHERE cavan = (Select min (cavan) From Distance where cavan>0);

here is the results i am getting

source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position
Athlone 90 0 180 80 3 02-CN-8001 Cork
Athlone 90 0 180 80 7 00-CN-1000 Cavan
Athlone 90 0 180 80 10 02-G-8001 Galway
What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.

SELECT Statement
I am trying to search a table with a statement like the following;

"SELECT name FROM table WHERE state='$state' AND trigger=1 AND areaA='$area' OR areaB = '$area'";

The problem is I need it to find the 'area' value in one of two different colums while matching the 'state' and 'trigger'. My syntax seems to be incorrect and I dont know how to find matching for 2 values while the other 2 columns can be optional. but one must be found.

Select Statement
I have a transaction table, wich has multiple transactions for one customer. each transaction has type. I want to find all customers who do not have a particular type of transaction.

The problem I have is it seems to find a postive result for every customer because every customer has a least one transaction that is not of this type. and I want the ones who have never had a transaction of this type.

Select Statement
I have a DB with a lot of tables (of course) :-)

In one of these tables I have the following structure (almost) Table "News" - ID, username, pagetitle, content. I also have a "users" table where I have username, fullname and groupname (simplified)

I want to list out how many "posts" every user has done, sorted by group..

Example:

user 1: 226 pages - Testgroup
user 4: 115 pages - Testgroup
user 6: 29 pages - Testgroup
user 2: 1 pages - Testgroup

user 12: 226 pages - Anothergroup
user 7: 115 pages - Anothergroup
user 9: 29 pages - Anothergroup
user 3: 1 pages - Anothergroup

Can anyone give me a hint in howto setup my select statement?

SELECT Statement
I have a single table that I want to select each row and group by one field and order by another. Example:

blogger  |   blog text  |  blog date

jim           blah           11/11/07
anna          blah           11/10/07
chris         blah           11/09/07
chris         blah           11/08/07
jim           blah           11/07/07
anna          blah           11/06/07

I want to group by the blogger and date descending, BUT ALSO sort by which blogger has the newest entry My output should be this:

jim           blah           11/11/07
jim           blah           11/07/07
anna          blah           11/10/07
anna          blah           11/06/07
chris         blah           11/09/07
chris         blah           11/08/07

What is the best way to produce my desired results?

Select Statement!
I have a simple query that is similar to this one:

SELECT distinct b.username from permissions a, users b, links c where
a.user_id = b.id and c.information = 14;

In this case, it selects the all users details from table 'user' that are
permissioned to access a certain information with id = 14.

The problem is that I need to select the users that are not permissioned but
I have sql v3.23 and I canot use union.

In other words, I need to do a select b.username from users b where
b.username not in "the result" of the last select.

SELECT Statement
I have two tables say TableA and TableB. I want to be able to select
a group of entries from TableB but only display the records that meet
the search criteria and are NOT in TableA. For example.

TableA:
111,John,55
112,Joe,13
113,Matt,55
114,Shawn,55
115,Dave,13

TableB:
990,John,12345678
991,Joe,12345678
992,Matt,12345678
993,Shawn,87654321
994,Dave,87654321
995,Steve,12345678
996,Tom,12345678

I want to display a list of people from TableB belonging to group
12345678 who do not already appear in TableA's group 55. The result
should be:

Joe
Steve
Tom

I have had no luck at all finding a solution. I know this would
probably be pretty simple if I were able to nest queries within
queries but the version of MySQL we are using doesn't support this.

Select Statement
--------------------
| ID | CODE |
--------------------
| 1 | A |
--------------------
| 2 | A |
--------------------
| 2 | B |
--------------------

I need to get ID that contains both CODE A & B, (i.e., 2 in the above case), is it possible that I can make do this in a single select statement?

Select Statement
I've got this little SELECT query that's not returning any results. Maybe someone can give me some insight?

my $user = 'abc@emailaddress.com';
my $passwd = "xyz123";

my $sth = $dbh->prepare("SELECT email, password, sub_code FROM userfile WHERE email = '$user' AND password = '$passwd'");
$sth->execute();
my @row = $sth->fetchrow_array;

Else If In Select Statement
i have a bunch of records that i want to pull on the basis of eg

SELECT lat, lng, html, label, icontype FROM ade WHERE icontype='" . $_POST['name'] . "'";

but i'd like to make the first query and also another on an "if else" basis to cover various bases

if i post a query where the 'name' isnt an icontype, i want it to look in the "section" field and pull those records matching a certain value ie all records that have a value of "5"

is it possible to do something like this

SELECT lat, lng, html, label, icontype FROM ade where icontype='" . $_POST['name'] . "' ELSE IF section='" . $_POST['name'] . "' ELSE IF catid='" . $_POST['name'] . "'";

SELECT Statement
I am having trouble returning the correct records. My database structure is as follows:

TABLE1
ID

TABLE2
MEMBER_ID
START_YEAR
END_YEAR

START_YEAR and END_YEAR are stored in TABLE2 as a number, not an actual year.

Two numbers are static 36 and 55. I need to select any records that have a START_YEAR and END_YEAR range between 36 and 55.

Here is my current query but it only returns records with START_YEAR or END_YEAR values between 36 and 55. I would like to return any record that has a value between its START_YEAR and END_YEAR that falls between 36 and 55.

SELECT COUNT(ID) FROM TABLE1
INNER JOIN TABLE2 ON (MEMBER_ID = ID)
WHERE START_YEAR BETWEEN '36 AND '55' OR
END_YEAR BETWEEN '36' AND '55;

The following would not be returned in this quey but needs to be.

START_YEAR = 22
END_YEAR = 56

Select Statement
I have a database with say 2000 records. The key record is numbered 1,2,3...2000, nothing fancy there.

I am using the mySQL database with perl.. basically I want to submit a few queries at the top of the page, and then use the information as I go down the page. The issue that comes up, is on this page I want a weird order of information. Basically I want records with secKey 1, 2, 3, 1566, 4, 5, 6, 7

So my first thought was to do one big select.. something like

$result = mysql_query("SELECT * FROM `db` WHERE secKey<1004 OR secKey=1566 OR secKey<8");

The problem with that.. is I don't think I have a guaranteed order. They HAVE to be in the order I listed before. So my second thought was something like this Code:

SELECT Statement
How can i get MySql to return a string or true or false or whatever if certain column is empty or null

like:

SELECT * FROM a
IF a.columnA = '' THEN a.columnA = 'something' END IF

So i dont need php to evaluate every column returned from mysql

like:

if($row['columnA'] == ''){

echo 'Nothing here';

}else{

echo $row['columnA'];

}

SELECT With IF Statement(?)
# Table structure for table `db_emails`
#

CREATE TABLE `db_emails` (
`id` int(10) NOT NULL auto_increment,
`user_id` int(10) NOT NULL default '0',
`email` varchar(255) NOT NULL default '',
`main` int(1) NOT NULL default '0',
`list` int(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

#
# Dumping data for table `db_emails`
#

INSERT INTO `db_emails` VALUES (1, 1, 'a.a@a.se', 1, 1);
INSERT INTO `db_emails` VALUES (2, 1, 'aa.aa@a.se', 0, 2);
INSERT INTO `db_emails` VALUES (3, 2, 'b.b@b.se', 0, 1);
INSERT INTO `db_emails` VALUES (4, 2, 'bb.bb@b.se', 0, 2)

So, now to my question. How can i do a SELECT where i want all email adresses BUT only the main if main=1 and all if main=0. The result for this dataset should be:
a.a@a.se
b.b@b.se
bb.bb@b.se

Select Statement
i am currently using the followin code to query an access db

SELECT * FROM tools WHERE item LIKE '%#item#%' or des LIKE '%#item#%'

item is a var i am passing in

this works fine however what i wanna do is display the results in this order

i want to list the matchs with item first regardless of the order rather then des which is where the item is matched 1st.

Select Statement
I have 2 tables one has the list of clients the other the shipments the client has received.
Table 1 name is client
id
first_name
last_name

Table 2 name is shipment
id
clientid
shipped_date
productid

My question is how do I setup my query to show the last time the client was shipped. I was just doing a group by clientid, but that does not group by last shipped_Date.

SELECT Statement
I want to execute the following statement:

SELECT ID,title,starttime,endtime FROM mytable WHERE starttime >= 32400

This procudes the following error:
You have an error in your SQL syntax; ch
eck the manual that corresponds to your MySQL server version for the right synta
x to use near '>= 32400' at line 1.

starttime is a VARCHAR(10).

Select Statement
when i try the following at the mysql command line...
select * from items;

nothing returns, it just hangs indefinitely. Does anyone know why this is? I have ran "unlock tables"
I have already logged into mysql, all its services are running, and I have selected the right database, but not sure why its not working.

SELECT Statement
I need some help with this select statement

SELECT * FROM gallery
WHERE type = 'skate'
AND park = 'Both' OR park = 'Park 1'

I just need it to look at one table and grab all the rows that have a type of 'skate' and have a park value of either 'both' or 'milwaukee'.
I think the OR is throwing it off.

SELECT WHERE Statement
I have a table that stores calendar events with timestamp field for the start time and end time. I am trying to write an ASP page that displays these fields, however I am not sure how to go about this. I tried something like:

SELECT * FROM events WHERE eventstarttime LIKE '20050816%'

to get all the events for 8/16/2005 but this did not display any records, even though there are events in the calendar for that date.
What would be the correct syntax for this query?

Select Statement
I want to query the dabase base on search terms. This the code i used for the query

SELECT * FROM hotels WHERE hotels.Region LIKE '%$searchterm%' OR hotels.Rating LIKE '%$rating%' ORDER BY hotels.Rating Desc, hotels.Name Desc LIMIT 0, 10;

the search criteria will be passed from my php script as a variable.
I want users to search for hotels based on region or ratings or view all hotels and can also view hotels by ratings at particular region or the whole country. When i use sql statement up there, it only select everything in table hotels but not according to what i want to achieve.

Select Nav Statement
I have a nav table in my database that grabs information to put into the navi menu, this is dependant on user access etc...
i have an item in my table that is in zone "members" and it is marked for permission Z.
Although when i run the query it dont pick it up.... these are the queries that ive tried...

SELECT * FROM acwe_nav WHERE privs LIKE '%A%B%C%D%E%F%X%Y%Z%' AND zone='members' ORDER BY weight;

SELECT * FROM acwe_nav WHERE privs LIKE '%ABCDEFXYZ%' AND zone='members' ORDER BY weight;

SELECT Statement
Is that possible to SELECT statement with and autoincrement field?

Select Statement
I have a table containing names of people in the neighborhood. The table only contains first name and family names.
How to get lists of first names grouped by the same family name ?
This is similar to getting "sum" of salary grouped by a certain condition. Unfortunately, "sum" does not work for strings.

SELECT Statement
I have a table called well that contains fields plate and clone among others.
WELL

ID: integer PK
plate:integer
clone:integer

I want to select the plates that contain ALL clones in my list eg (1234,1235,1236)
I was using
SELECT plate FROM well where clone IN (1234,1235,1236) group by plate
but this is not right as its a clone=1234 OR clone=1235 etc.
Sometimes my list will have hundreds of clones in it.

SELECT Statement
I'll write out what I want the code to do and then I'll show you the code that I think should work but doesn't:

select * from t1 where (id is not in t2)

basically I want to select all the rows from a table where the id of any row is not showing as a foreign key in the second table.

Code:

SELECT * FROM t1 WHERE id NOT IN (SELECT DISTINCT t1_id FROM t2);

Select Statement
im working in a relational database and i have two tables one is student and the other is result. the columns in my student database include sno, sname, address, and sex. the columns in my result database include cno, sno, and score. so need the sname of students who have failed in cno=15 (SCORE < 60). im using the sql statement:Code:

mysql> SELECT student.sname FROM student, result WHERE result.score < 60 && result.cno = 15;

from this sql statement i am getting the right result if i display the score (which is 52 but it displays it many times) but when i display the names (which is what i need) it simply displays all the names. Each one with the score next to it even though not all the students got that score. anyone have any ideas on how i can fix my select statement? if i didnt describe it well enough respond and ill repost.

One Select Statement
I have a table with the fields

id | titleid | catid

Each title can have more than 1 catid so it's possible for the fields to look like this:

id | titleid | catid
1 1 1
2 1 2
3 2 1

Now I want to select any rows where titleid matched more than one catid. In this example I would like to select titleid 1 because it is in catid 1 and catid 2 but I can't seem to figure out a statement that properly says
"give me any titleid that has a catid of 1 AND a catid of 2"

SELECT Statement
I have two tables. Table A and table B has ADD1 as primary key and foreign key respectively. I want to select all from Table A where the same data in ADD1 DOES NOT appear at table B. How should I write the SQL?

Select Statement
I am tracking professions by age, and I would like to run a query where I get a count of each profession by each age group.
If I do the following, I get one age group:

SELECT Profession, COUNT(Profession)
FROM Accounts
WHERE Age = "Under 18"
GROUP BY Profession;

Can I somehow use multiple WHERE criteria or modify the SELECT statement to show all of the age groups (I have under 18, 18-25, 26-35, 36-49, 50+)