Selecting specific records to run query on

Question

I am trying to select a small number of records in a somewhat large database and run some queries on them.

I am incredibly new to programming so I am pretty well lost.

What I need to do is select all records where the Registraton# column equals a certain number, and then run the query on just those results.

I can put up what the db looks like and a more detailed explanation if needed, although I think it may be something simple that I am just missing.

What programming language are you using. In pure SQL all you need is a where clause at the end — Daniel Casserly, Aug 14 '12 at 16:15
Just a suggestion on the side: I'd recommend not to use spaces or special characters when naming tables or fields. That is, use "RegistrationNumber" rather than "Registration#". I'm aware it doesn't answer your question, but, since you stated you're new to programming, I thought it would be a good thing to help you learning good practices from the beginning. — Diego, Aug 14 '12 at 16:19

David · Accepted Answer · 2012-08-14T16:35:47.070

Filtering records in a database is done with the WHERE clause.

Example, if you wanted to get all records from a Persons table, where the FirstName = 'David"

SELECT 
  FirstName, 
  LastName, 
  MiddleInitial, 
  BirthDate, 
  NumberOfChildren 
FROM 
  Persons 
WHERE 
  FirstName = 'David'

Your question indicates you've figured this much out, but are just missinbg the next piece.

If you need to query within the results of the above result set to only include people with more than two children, you'd just add to your WHERE clause using the AND keyword.

SELECT 
  FirstName, 
  LastName, 
  MiddleInitial, 
  BirthDate, 
  NumberOfChildren 
FROM 
  Persons 
WHERE 
  FirstName = 'David'
   AND
   NumberOfChildren > 3

Now, there ARE some situations where you really need to use a subquery. For example:

Assuming that each person has a PersonId and each person has a FatherId that corresponds to another person's PersonId...

PersonId  FirstName  LastName FatherId...
1         David      Stratton  0
2         Matthew    Stratton  1

Select FirstName, 
  LastName 
FROM 
  Person 
WHERE 
 FatherId IN (Select PersonId 
              From Person 
              WHERE FirstName = 'David')

Would return all of the children with a Father named David. (Using the sample data, Matthew would be returned.)

http://www.w3schools.com/sql/sql_where.asp

Not to add confusion, but there's a way to do that last example using a JOIN statement and you'll get better performance in most cases. But since you said you're new, I thought this would be less confusing. As you get more comfortable with SQL, you may want to revisit this. The following link is for SQL Server, but it's pretty much the same as in MySql. I'm posting the link to show how to do the same query in each method (Join vs. Subquery). http://msdn.microsoft.com/en-us/library/aa213252(v=sql.80).aspx — David, Aug 14 '12 at 16:45
More info on join vs. subquery in MySql. It looks like a bigger issue than I thought in MySql: http://www.selikoff.net/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/ — David, Aug 14 '12 at 17:07

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

I do not know what you have done up to now, but I imagine that you have a SQL query somewhere like

SELECT col1, col2, col3
FROM table

Append a where clause

SELECT col1, col2, col3
FROM table
WHERE "Registraton#" = number

See SO question SQL standard to escape column names?.

edited May 23 '17 at 12:04

Community

1
1

answered Aug 14 '12 at 16:15

Olivier Jacot-Descombes

104,806
13
138
188

score 0 · Answer 3 · answered Aug 14 '12 at 16:15

0

Would this be any use to you?

SELECT * from table_name WHERE Regestration# = number

answered Aug 14 '12 at 16:15

The Gav Lad

280
3
9

score 0 · Answer 4 · answered Aug 14 '12 at 16:16

0

Try this:

SELECT *
FROM tableName
WHERE RegistrationNo = 'valueHere'

answered Aug 14 '12 at 16:16

John Woo

258,903
69
498
492

score 0 · Answer 5 · answered Aug 14 '12 at 16:17

I am not certain about my solution. I would propose You to use view. You create view based on needed records. Then make needed queries and then you can delete the view.

View description: A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Example:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

For more information: http://www.w3schools.com/sql/sql_view.asp

Selecting specific records to run query on

5 Answers5