January 31, 2012 at 4:22 pm
Ok...I have 2 tables....Projects and contactrecord_project
Projects table has these columns and there is example data below
---------------------------------------------------------------------------
Project_name_id Project name
1 Demo Project 1
Contactrecord_project has these columns and there is example data below
--------------------------------------------------------------------------
Contactrecord_project_id Contactrecord_project_nm project_support
5003 1 2
What I need to do is populate some checkboxes and radio buttons on a form with values from the database.
So my projects table currently has 6 records in it and I would need to display all of those then I need to join the Contactrecord_project table to it for the current record I'm displaying.
if i try something like this:
select * from projects
INNER JOIN contactrecord_project ON projects.project_name_id = contactrecord_project.contactrecord_project_nm
where contactrecord_project_id = '5142'
I only get 3 records return because that person is only associated with 3 of the 6 projects.
Can someone tell me if I can do this in one sql statement.
Thanks
January 31, 2012 at 4:37 pm
steve 22165 (1/31/2012)
Ok...I have 2 tables....Projects and contactrecord_projectProjects table has these columns and there is example data below
---------------------------------------------------------------------------
Project_name_id Project name
1 Demo Project 1
Contactrecord_project has these columns and there is example data below
--------------------------------------------------------------------------
Contactrecord_project_id Contactrecord_project_nm project_support
5003 1 2
Just an FYI, it's easier if you set up sample schema and data like you'll find in my first signature, both for us and for you. We'll probably need to see your actual data and proc to help you out here.
So my projects table currently has 6 records in it and I would need to display all of those then I need to join the Contactrecord_project table to it for the current record I'm displaying.
if i try something like this:
select * from projectsINNER JOIN contactrecord_project ON projects.project_name_id = contactrecord_project.contactrecord_project_nm
where contactrecord_project_id = '5142'
A where clause will limit in some way. Your example record above cannot succeed against the where clause as listed.
I only get 3 records return because that person is only associated with 3 of the 6 projects.
What, in the statement above, restricted the results to a person? That looks like you limited it to the Identity field, but I'm not sure what these fields are without seeing the schema.
Can someone tell me if I can do this in one sql statement.
Most likely, but we're going to need a bit more from you to help you out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 4:47 pm
Hi, Thanks for looking....I'm new here and did not know about the Forum Etiquette...sorry
In the where clause the 5142 is an id of the person.....so that is why is restricts it to those 3 records....
January 31, 2012 at 5:08 pm
steve 22165 (1/31/2012)
Hi, Thanks for looking....I'm new here and did not know about the Forum Etiquette...sorryIn the where clause the 5142 is an id of the person.....so that is why is restricts it to those 3 records....
Then you can't see anyone else's. That's the entire purpose of the where clause, to restrict the records to a usable set. Remove that and you'll get all your records.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2012 at 5:52 pm
I guess I'm not explaining it well enough....If I remove the where statement I get back too many items.
I have a table(projects) that lets say has 6 rows...I want all of these returned in the query
My projects table is simple: 2 columns
project_name_id which is a "int" column type and stores the id of the project
project_name which is a varchar and stores the name of the project
The contactrecord_project table stores which people belong to which projects and their support level
It contains only 3 columns
contactrecord_project_id which stores the id of the person...a number like 5004
contactrecord_project_nm which stores the project id...a number like 5
project_support stores the id for the level of support...a number like 2
I'm trying to generate a query that will return all 6 projects and then select the projects that a person is flagged with in the contactrecord_project table
I apologize I have not output my tables in the format required for the forums, Today was my first day of posting here.
Does this help?
February 1, 2012 at 12:59 am
sartis (1/31/2012)
I'm trying to generate a query that will return all 6 projects and then select the projects that a person is flagged with in the contactrecord_project tableI apologize I have not output my tables in the format required for the forums, Today was my first day of posting here.
Does this help?
Unfortunately, not really.
To rephrase what you said, you want a query that'll return all 6 records in your projects table. That means no where clause.
THEN you want it to do some selection based on a person. That's usually a where clause. You're going to need to describe in more detail what you want, preferably with hard data. It sounds like you want two completely different recordsets, an 'all' recordset, and a subset that only returns the one associated to a particular person.
Now, marking them, I don't understand. SQL Server doesn't 'mark' anything, though you can have it put up another column with a calculated indicator of some kind.
Btw, about the data. If you can provide us sample data and exactly what you want from the result set of that data, we can fill in the middle, usually with only a question or two of clarification.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2012 at 12:39 pm
Hey Craig, So i moved my where statement into my join statement and I got it to work correctly, thanks for trying to help.
February 1, 2012 at 1:48 pm
sartis (2/1/2012)
Hey Craig, So i moved my where statement into my join statement and I got it to work correctly, thanks for trying to help.
:blink: I'm glad that made a difference for you. I'm not sure how, but it may just be I never really understood the problem. Thanks for the feedback however. Good luck!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply