Multiple Table Select

Hey everyone, I have a question regarding an SQL query. I'm working on a Web App in .NET at the moment and part of the project is to produce a report of all the information about the clients. Now, there are 4 tables in question: Client, Details, Appointments and RefItem. Now, a lot of the information stored in the first 3 tables are IDs and the names corresponding to those IDs are in the RefItem table.

The RefItem table has the columns ItemID, GroupID, Name, HelpText and Active. For example, a row in the Appoinment table might contain the ID 150 under the AppointmentStatusID. In RefItem, the ID 150 corresponds with "Scheduled". So I have



This works fine for selecting one name, but my report requires pretty much every name. I was told I'd had to use INNER JOIN or LEFT OUTER JOIN but I can't seem to figure it out. If anyone has any info, please let me know!! Thanks

I would simply use the following code for such joining. (I never liked the outer inner left right joining it might be better but it doesn't make sense when debugging sql syntax as it doesn't say much about the join. So I would do the following:


this way one can easily see what columns that are joined and one doesn't need a whole lot of complex JOIN commands. (OK it might be extra code and the Carthesian Product might be more time consuming but it works.)

So if i get it right your code would be something like this

Is this what you wanted?

Yes and No. The problem is, and I've had this problem before, the RefItem.ItemID is a whole range of different values and if I do the query like you have above, it will only match with the first regular join I do. The rest won't return anything. E.g. : Your code above will return the name of the appointment status, but won't return anything else because it will still keep looking for the same ID that the appointmentstatusID is. It's kinda difficult to explain, needless to say that I've tried out the method suggested by you above and it didn't work. It appears that INNER JOIN is the only way to go...unfortunately.

I dont understand what it is you want to join but if you specify it further like if you tell em what fields the table have and what fields should be joined i might be able to help.

Do you want to join the RefItem.ItemID to several different tables? ie something like this?:


Or am I wrong please try to explain further.

I managed to get it working, here is a brief description of what I did:



There was a lot more items to be selected but I kept it short for clarity. I performed multiple joins on the ReferenceItem table as I was getting the name for different IDs all the time. If I had one join, I would only have been able to select one name, based on one ID. It's a little complicated...