SQL joins and logic

Question

I am working with a SharePoint web part that I am designing in C#. I need to query a SQL Server 2005 database to return the names of machines that are associated with a particular customer, selected by the user.

The problem is that there are different types of machines, and the names themselves are stored in several other tables. Here is the structure as it pertains to this question:

I have a table "customerInfo" which contains the ID and name of each customer. The ID is the primary key.

A table "machineIDs" links the customer IDs to the corresponding machine IDs. The machine ID is the primary key.

I have two tables for two types of machines, "machine1" and "machine2." machine1 contains the name of the machine itself, but machine2 only contains the ID of the type of machine it is. With both of these tables, the machine ID is the primary key.

Then I have a table "specialtyMachine." This table contains the name of the machine and the ID of this type of machine. The primary key here is a type ID, which identifies the type of machine, whereas the machine ID identifies the specific machine.

What I need to do is pull the machine name from machine1 and specialtyMachine, given just the customer ID. I'm not sure whether it's possible to do this with a complex join directly within the query, or if I will need to apply logic in the web part to pull this information together.

I would also like to know how I can think about this in terms of a generic model. I'd like to be able to recognize this and apply it generically in the future, as I'm sure it's a fairly common problem that I just haven't dealt with yet. Thank you.

Answer 1

Will your query pass in Customer Name or Customer Id and expect a result?

Is there a crosswalk table between CustomerInfo and Machine1 or does Machine1 have a FK back to CustomerInfo.Id? Same question for Machine2

Answer 2

You want to union two queries.

SELECT c.ID, m1.Name
FROM customerinfo AS c
JOIN machine1 AS m1
ON m1.customerinfoID = c.ID
WHERE c.ID = @customerID

UNION ALL
SELECT c.ID, sm.Name
FROM customerinfo AS c
JOIN machine2 AS m2
ON m2.customerinfoID = c.ID
JOIN specialtyMachine AS sm
ON sm.TypeID = m2.TypeID
WHERE c.ID = @customerID

Answer 3

Your data model sounds a little strange but there is no generic model as far as I can see, what you are talking about is a standard relational database.

To do the query a complex just create the different parts of query in a modular way as shown below (if I'm wrong about where the keys are then the modular way will make it easy to modify for your use.)

First the machine mapping would look something like this:

Select m2.mid as mID, m1.name as mName
from specialtyMachine sm
left join machine2 m2 on sm.mid = m2.mid 
left join machine1 m1 on sm.machinename = m1.machinename

You can run and test this to see a mapping of machines to ids.

Now we join in the customerinfo:

select * 
from customerinfo c
left join 
(
   Select m2.mid as mID, m1.name as mName
   from specialtyMachine sm
   left join machine2 m2 on sm.mid = m2.mid 
   left join machine1 m1 on sm.machinename = m1.machinename
) t on c.mid = t.mid

This will work fine (and is good for testing you have the relations right), but not good because the subquery is performed for every row in the outer query, better to "unwind" it like so

select * 
from customerinfo c
   left join machine2 m2 on c.mid = m2.mid 
   left joint specialtyMachine sm on m2.mid = sm.mid
   left join machine1 m1 on sm.machinename = m1.machinename

caveat : I did not test this, there might be typos.

Answer 4

Based on your new schema information I'm guessing this is what you want:

select *
from customerinfo c
left join machineids mids on c.customerid = mids.customerid
left join machine1 m1 on mids.machineid = m1.machineid
left join spcialtymachine sm on mids.machineid = sm.machineid -- or m1.machineid = sm.machineid
left join machine2 m2 on sm.typeid = m2.typeid

note: if you are using this in a stored procedure with an @customerid as a parameter you can add a where c.customerid = @customerid to the end.