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);
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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.
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
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+)
Select Statement
I'm trying to use a select statement for a table that uses numbers for = the field names. Here is the query I've been trying. Please help! SELECT * FROM table_name WHERE '18'=3D1; (18 is the field name, 1 = obviously is the value I'm looking for.) Everytime I have tried a variation, I get the same result where MySQL = keeps evaluating the WHERE statement like a mathematical equation.
SELECT Statement
I have a users field in a posts table that I want to select the 3 users that have made the most submissions. I'm having trouble coming up with a select statement that will work. Can someone point me in the right direction?
Select Statement
I have a table with 3 columns.I am using mysql.In the SID column are listed the items. The aid column contains the specifications of the items. I don’t know how to syntax the query to give me only the items that share the criteria(= specifications) I selected. For example I need the items where the specification ids are 23 AND 24. With this query it brings me the items which have specification id 23 OR 24. query =”select * from draseis_sinisfores_users_info where aid IN(23,24)”; Idsidaid 32823 73023 83024 123323 133324 What syntax will return ONLY the item(s) which contains both specifications in this example? Here I used only 2 criteria. I want to be able to do the same with any number (even an unknown number) of criteria.
Regarding Select Statement
I am retrieving data from two tables using the UNION statement and i am thinking of having a field in my resultset(this field is not found in the database) to distinguish the data from two tables.Is it possible to set a fixed value for a particular field using the select statement?For example, a field value of 'buy' and 'sell' values are used to distinguish between the two tables.
Select Statement
In the following statement can someone please tell me what the a stands for in a.'ID', a.'Name" and at the end of the FROM statement where it says test.accts a;? SELECT a.`ID`, a.`Name`, a.`Notes1`, a.`Notes2` FROM test.accts a; I have researched the mysql documentation and while I can find instances of this syntax I can't seem to find a definition of why it is there or what it stands for.
SELECT Statement
I'm trying to write a database, but this one problem is giving me a real head ache. Can anyone help? This is my select statement: $query_get_projects = "SELECT nis_project.id, nis_project.subject, nis_project.billingcode, nis_completion.comments, nis_completion.completed, nis_completion.na FROM nis_project JOIN nis_completion ON nis_project.id = nis_completion.project_id AND "' . $_GET['select_store'] . '" = nis_completion.store_id"; This is the error I get: Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in /home2/sit/public_html/nis_live/dbase/projects_data.php on line 8 Line 8 of course is this SELECT statement. I'm almost sure is something to do with the quotes around the $_GET variable, but I've tried every combination.
SQL Select Statement Returns Nothing?
When I view this page, nothing appears in the browser PHP while($row = mysql_fetch_array($result)){$Identifier = $row['ID'];$random_thumb_query = mysql_query(" SELECT userid, filename FROM photo_photoplog_fileuploads WHERE moderate = 0 AND userid = '$Identifier' ORDER BY dateline DESC LIMIT 1") or die(mysql_error());$random_thumb_row = mysql_fetch_array($random_thumb_query);echo $random_thumb_row;$random_thumb_nail = '<img src="/photoplog/images/'.$random_thumb_row['userid'].'/small/'.$random_thumb_row['filename'].'" alt="" border="0" />' http://miamiasp.com/providers.php u:luke x: urtnowski is the $Identifier variable ok?
Select Statement Trouble...
I thought I could do a comparison in my SQL statement and get data from Wednesday to Wednesday but I do not know how to tell if my date of entry (which is set with the function NOW() when I write to the database) falls in the most current week. Some psuedo code would be - Select * from conference_dates where date_of_submission is >= the previous wednesday and date_of_submission is <= today (meaning any day). I can get any 7 days previous but I am not sure how to get only the number of days necessary (today would return 1 days records).
Can Someone Help Me Modify The Select Statement
can someone please help me modify the select statement so that only categories that have a depth of 0 shows. SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM categories AS node, categories AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft;
Select Statement Problem
If I could have some help with this select statement: Quote: SELECT DISTINCT count( * ) FROM `mos_gs_grave` g, `mos_gs_gravemarker` gm, `mos_gs_person` p WHERE g.`graveid` = gm.`graveid` AND gm.gravemarkerid = p.gravemarkerid AND LOWER( p.`lastname` ) LIKE 'coombes%' AND LOWER( g.`code` ) LIKE '%w%' LIMIT 0 , 30 In this query, one grave may contain many gravemarkers, one gravemarker may commemorate many people. In this instance the one grave has one gravemarker with two people, both with the name "Coombes". Unfortunately the query returns a total of two, when what I am after is the total number of graves - in this instance one. Should be easy but all my hard won SQL knowledge from several years back seems to have slowly dribbled out the back of my head when I wasn't looking.
Hi, Need Urgent Help On Sql Select Statement
I have two tables that stores product details which is carinfo and products.I did it in 2 separate tables as the products are by diffrent category and therefore the fields are different. I'm not sure if that was the correct way doing it coz i seem to have problem now. The problem is,each product purchased are stored in a order table and that table only stores the product ID. The items in the first two table which is carinfo and products, have both unique ID like p1 and c1. My problem now is when i'm trying to get the product information of item in order table i need to check if that is from product table or carinfo. Coz i wanted to generate a report that will list the item in order table together with the items information. The sql i use is "select p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription from carinfo c,product p where p.productid='p14' or c.productID='p14' group by p.productid,p.prodname,p.proddescription,c.productid,c.prodname,c.proddescription" You can view th example results here. http://www.ineers.com/meg/error.JPG the results i get are the product information of the item if its in product table,but the quesry also results to list all the product name in carinfo table. I hope my question is clear. Its urgent and i've been breaking my head thinking how to solve this without having to change my database design which is to combine both carinfo and product table as one.
Problem With A Select Statement
I have tried this query on my local server and it works perfectly, anyway after i tried it on phpmyadmin 2.7.0-p version it didn't work $select="select download_id, download_name from downloads where not exists ( select * from recommended where downloads.download_id=recommended.download_id)"; It returned the following 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 'exists ( select * from recommended where downloads . download_i What's the problem with mysql on that version of phpmyadmin, how can i make this statement understand it? or if i cant.
SQL SELECT Statement Help Needed
I have three databases, like so: 1. Visitors: vid(int) | vname(varchar) 2. Events: eid(int) | ename(varchar) 3. Participation: pid(int) | visitor(int from vid) | event(int from eid) The participation table is a criss cross reference table to associate visitors with events. Given the vid(from visitor table) how go i query the database to get all the events a given visitor is participating in?
Select Statement In Normalized Db
can anyone share the best way to retrieve from a normalized db? My goal is to get the engines.eng_definition. Here's what I have: thehub: 1N --------- 1)item_no carinfo: 2n ---------- 1)item_no 2)eng_ID engines: 3n ---------- 1)eng_ID 2)eng_definition my query has to get the item number from theHub. Is there a way for me to JOIN these together? I understand how to do a JOIN on theHub and carinfo but can I join engines in the same select statement? right now this is what I have. Keep in mind there are other items being retrieved. Select x, y, z FROM thehub INNER JOIN carinfo ON (thehub.item_no = carinfo.item_no)
|
TRACKER
