table design question

[bonosa]

I was trying to be clever but am in difficulties now. I have a set of records. Each row has a bunch of columns (ints , varchars). I put some of these columns (all ints) into another table, with the recordID being the 'common' key. Now I look in table 2 and get all the records that have at least three non zero columns. I now have to get all the records from the first table that go with these selected records. So if my search got me 600 records from table 2, then how do I query my table 1 to give me back the corresponding related info? I could build a query which says :" select * from table1 where recordID = n1 and recordID = n2 and recordID = n3... " and so on for 600+ records but that seems all wrong. I dont really want to put all the columns in the same table , but if thats what I need I will restructure my tables. I was wondering if theres something I've overlooked since I am new to sql.
Thanks very much,
sb

 

[TonyGroves]

If table1 and table2 have a one-to-one relationship and the same primary key name, then you could simply use:
[tt]
SELECT *
FROM tbl1 JOIN tbl2 USING(recordid)
WHERE [/tt](tbl2-has-3-nonzero-columns)

Just one point though: as you suggest, splitting a table that way is usually not a good idea - it complicates queries, reduces performance, and creates confusion. There's nothing wrong with a table having hundreds of columns.

 

[bonosa]

Thanks for the feedback. Another question: How long can the query be?
APpreciate the input,
sb

 

[TonyGroves]

The query can be as long as you like; there's no limit.