very simple sql question (i hope)

[TomParker1]

i have 3 tables

TCustomers with CustomerID and CustomerName coluimns
TCustomerGroups with CustomerGroupID and CustomerGroupName columns
TCustomerGroups_Customers with CustomerID and CustomerGroupID

I want to generate a list of all the CustomerNames and their CustomerGroupName. Not all customers have a group and I don't care to display those customers. Basically I'm just looking to get a list identical to TCustomerGroups_Customers except with the actual names replacing the IDs.

 

[Markbnj]

This smells like homework. You need to join the three tables together on CustomerID and CustomerGroupID. A default inner join will drop the rows that don't match in all three tables. Take a stab at it, and post your code if you can't get it.

 

[TomParker1]

This smells like homework. You need to join the three tables together on CustomerID and CustomerGroupID. A default inner join will drop the rows that don't match in all three tables. Take a stab at it, and post your code if you can't get it.

so you're not going to help because you think it is homework? sigh

the ACTUAL table names are T071_CUST_CATEGORIES with C071_CUST_CATEGORY_ID and C071_CUST_CATEGORY_NAME and T073_CUSTOMER_CATEGORY with C073_CATEGORY_CUSTOMER_ID and C073_CUSTOMER_ID.

I am the IT Manager for a small manufacturing company. We use an exceedingly mediocre Order Management and Inventory system that "integrates" (somewhat) with QuickBooks, but the developer went belly up. Thankfully I was able to find the database password embedded in a FedEx ShipManager XML file that they provided us with in the past.

I am now trying to generate some more advanced reports that the program itself does not give us, so I am starting to learn SQL. I simplified the Table and Column names for the purposes of making it easier for the forum readers. From my reading on JOIN, it is for use with two tables, but I have three tables involved here.

 

[bobross419]

You'd be surprised at the number of "homework" posts appear by new members. There are plenty of knowledgeable people here that are more than willing to help, but rarely will they do your (home)work for you. If you post up some queries that you've tried you are more likely to get responses on what to modify.

Mark already pointed you in the right direction to get you started... give it a try, if you can't get it to work then post the query you are dealing with and someone (not me 😀 ) will tell you what you are doing wrong.

I'm probably as versed in SQL as you are and found out how to join 3 or more tables in 10 seconds...

 

[TomParker1]

your results are different from mine

i don't remember exactly what i searched for but i did not include "inner"

anyway the results i found said to use a tempdb, but i was sure there was a more efficient way to do it in "one step". thanks for the link

 

[TomParker1]

using your link, I was able to get the data I needed. Thanks!

here is the SQL I used:

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomers[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]CompanyName[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]GroupName[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]ParentID[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups_Customers [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]inner [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomers.[/SIZE][SIZE=2]CustomerID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups_Customers[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]CustomerID [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]join[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TCustomerGroups_Customers.GroupID = [/SIZE][SIZE=2][SIZE=2][COLOR=black]TCustomerGroups.GroupID[/COLOR][/SIZE][/SIZE]

now i want to make things a liiiiiitle more complicated. i got my list of results consisting of three colums:

CompanyName
GroupName
and ParentID

what is ParentID? I did not include it in the original problem. ParentID is also part of TCustomerGroup_Customers and indicates whether a Group is a child (a subgroup) by referencing the GroupID of its parent. I'd like to print out the Name of the ParentGroup if the ID is not 0 (indicating that it is a top level group). Is there a way to add this extra step into the existing query? Or will I have to use a temporary table?

edit: figured it out by googling "join table to itself" and learning about self joins. my problem was that it wasn't letting me join a table to itself because it has the same name, so I just have to use the AS syntax to give the table an alias when joining it to itself

thanks!

 

[Markbnj]

your results are different from mine

i don't remember exactly what i searched for but i did not include "inner"

anyway the results i found said to use a tempdb, but i was sure there was a more efficient way to do it in "one step". thanks for the link

You don't need a temp table. It's a simple inner join. And sorry for my assumption earlier, but when someone with a few posts asks about a simple problem involving tables with names like Customer, and CustomerGroup, probably 90% of the time it's homework 🙂.

select CustomerGroupName, CustomerName
from TCustomerGroups a
inner join TCustomerGroups_Customers b on b.CustomerGroupId = a.CustomerGroupId
inner join TCustomers c on c.CustomerId = b.CustomerId
order by CustomerGroupName

Edit: you replied while I was posting. Yep, looks like you have it almost. You can either use "join" or "inner join" since inner is the default, but you can't use "innerjoin".