Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tgabe213
Starting Member
3 Posts |
 Posted - 2008-10-23 : 14:18:25
|
| I've got a few different tables that have some data in them. They all have FK's set up (but without the constraints. I'm new, go easy on me). I have a USERS table. This table can have users that are a Customer, a Referrer, or an Advisor. Each Referrer refers a customer, and that customer has an assigned Advisor. The customer also has personal information stored in other tables. Each referrer has a location. Here is a link to an image from SQL Server Management Studio showing my table setup.[url]http://www.mytestmule.com/projects/10_9_DBDiagram.png[/url]Here is a table I am trying to populate.http://www.mytestmule.com/projects/CurrentReferrals.png[url][/url]While working in Management Studio, I can get all the information I need to display as 1 row of results, but only when I have a "WHERE USER_RELATED.Type = 'Customer' or whatever.How can I get these rows to populate when I have multiple customers/referrers/advisors? The 1 row isn't an issue because of the where clause, but getting the contents of the real data to display is my concern. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-23 : 14:21:32
|
| show some sample data and explain what you want out of them. |
 |
|
|
tgabe213
Starting Member
3 Posts |
Posted - 2008-10-23 : 14:31:27
|
| Here's the data just organized into a spreadsheet.http://www.mytestmule.com/projects/data.pngAnd here it is how 1 referral/customer would be outputted in the table.http://www.mytestmule.com/projects/testdata.png |
|
|
|
tgabe213
Starting Member
3 Posts |
Posted - 2008-10-23 : 14:33:26
|
| My issue isn't getting that 1 row of data into the table because I can use a WHERE clause. The issue is when I want to be able to display a list of ALL customer names, who they were referred by, etc, etc. That will be around 250 customers. |
|
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-23 : 16:54:25
|
| You basically need to join tables related to each other - an inner join if they will always have data and a left join if otherwise. Are you familiar with joins? |
|
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-23 : 16:56:03
|
| Sorry I sent a reponse to a wrong question here. You need a co related query. Are you familiar with that? |
|
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-10-23 : 17:01:43
|
| Something like this may point you in the right direction.http://www.sqlteam.com/article/more-trees-hierarchies-in-sql |
|
|
|
|
|
|
Joining a table to itself, and other tables