January 19, 2009 at 10:08 pm
I'm a total SQL noob, and I recently had this project fall into my lap. I've had it pretty easy with simple select * from X where Y = Z queries so far but now I have something a little more complex
I have two tables, Client and Household_Members. Client has a primary key Client #, which is a foreign key to Household_Members. Household_Members contains information on people who members of a Client's household.
For simplicity, I'm removing all non relevant columns
CREATE TABLE Client {
[Client #] Int, Primary Key
}
CREATE TABLE Household_Members {
[Client #] Int,
SSN String
}
There are Household Members which belong to more than one Client, so I would like to write a query which returns all Client rows with a related household member with X SSN.
This is what I've been trying but it doesn't work:
Select * from Client
inner join Household_Members
on Client.[Client #] = Household_Members.[Client #]
where Household.SSN = @ssn
I've been reading about joins all day trying to figure out how to do this, and now I'm tired and asking you guys so I hope it makes sense 😀 Thanks a lot for any help!
January 19, 2009 at 10:20 pm
But different household members have different SSNs, right? You drop out other household members because of that.
I'm not completely clear what you want. You are getting all matching household/client rows for each social security number. Are they not ordered correctly perhaps? Maybe you should work with one SSN and get that query working (add an AND ssn=xxxx) and then remove that line to test once you think it's working.
I guess I'm wondering do you want all info for one SSN (and only that SSN), or info for things related to that SSN?
January 19, 2009 at 10:37 pm
Steve Jones - Editor (1/19/2009)
But different household members have different SSNs, right? You drop out other household members because of that.
Yeah, you're right. Thats not the code I'm using, I just made it quickly so you guys could see the layout of the database in as uncomplicated a way as possible
I'm not completely clear what you want. You are getting all matching household/client rows for each social security number. Are they not ordered correctly perhaps? Maybe you should work with one SSN and get that query working (add an AND ssn=xxxx) and then remove that line to test once you think it's working.
I guess I'm wondering do you want all info for one SSN (and only that SSN), or info for things related to that SSN?
Yeah, see maybe I don't want to use a join because I only want client rows returned not client/household rows.
Lets say I have 3 clients, client #'s 1 2 and 3. Household member Bob has ssn 123 and is a a member of client 1 and 3's household. How do I write a query that searches the household table for SSN "123", then returns client rows 1 and 3 as the result? It seems to me like it should be really easy, but like I said I'm a complete SQL noob
January 19, 2009 at 11:04 pm
OMG, I'm so dumb, I just figured otu what to do. I'm just too tired and mind-numbed from two weeks of programming. I'll just do two separate queries, query household for the client #, and then query client separately. Maybe there's a way to do it in one query, but this will work fine. God, I'm so embarassed :blush:
Sorry for bothering you guys and thanks! This is a cool forum though I'll keep reading and I may need help again sometime
January 19, 2009 at 11:04 pm
You have written the query right. If you want just the client columns use subquery
You can use select distinct client.* also
January 19, 2009 at 11:13 pm
joeroshan (1/19/2009)
You have written the query right. If you want just the client columns use subqueryYou can use select distinct client.* also
You don't need a subquery or distinct. If you just want the client columns, just specify the columns you want. You should always specify the columns you want regardless.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 20, 2009 at 1:35 am
Yeah, I totally figured out how to do it with the join. Works perfect. Thanks for everybody's help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply