Fucking SQL

[Nymph]

Originally Posted by sarettah

no..

A sub query in a where clause returns a set of records (just like a regular select query). You can compare a field (or fields if you concatenate them) and perform actions on the matching records.

So, say you have a list of names and addresses in a master table and in there you have a unique tag called cust_id that identifies any individual customer.

Now, because you know that you aren't going to get a phone number for all your customers you decided that that was a differnt table so you have a table called phones and in that table you have the phone number of those customers that gave you one and each phone record also has a tag called cust_id that is a matching tag to the cust_id field in your master table.

To see which customers had given you phone numbers you can use a subquery in the where clause. (you could also use a join and probably would if you were actually pulling the data but sometimes it is quicker to use a subquery) So, to get a count of customers that had phones I could use a query of:

"select count(*) from customers where cust_id in (select cust_id from phones)"

Now, before the xspurts jump in, yes, I could also have just counted distinct cust_ids in phones to get the same result. I am just trying to demonstrate how it works.

That make sense?

Yes.
After I posted that, I took a look at our favorite recipe script to see how it pulled querys. I have 3 tables for each recipe, so it calls the recipeid from each table to edit, delete, or whatever I need to do with it.