Fucking SQL

[sarettah]

Originally Posted by Nymph

Trying to understand this here....

You have one record, that has data in both tables? Couldn't you do a double query assuming that not all records would have the same data info in each table?

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?

In the case where the accident occurred, I was trying to delete a selected set of records from a sub table.

There is a main table that has all the admin info and a vertical database for all the "other stuff". So, when I want to clean up test shit real quick I just use a delete query against the sub table where I pull the keys to delete from the master table.

In this case, I was moving too quickly and missed editing a table name in the query, so the table that the subquery was pointing to did not have the field I was trying to match to in it. Instead of throwing the error it instead deleted 18000 records.

I was running without a net in firehose mode too, my bad. But the query should have blown up, not deleted anything................. dammit