0

Post Updated to show actual DB info/Tables/Columns etc.

I'm a newbie at SQL, but learning fast. I've looked at a few Youtube explanations as well as some answers on this forum but I cannot relate them to my situation. I have a database, with three tables that I want to extract data from. All three tables have column that is constant ie. contains a unique reference number.

Under the DB "tree" I have:

nice_interactions
nice_storage_center

In nice_interactions I have three tables that I require info from:

dbo.tblInteraction77
dbo.tblParticpant77
dbo.tblRecording77

The Columns in dbo.tblInteraction77 that I require are:

[iInteractionID] - *this is the unique field amongst all tables*      
[dtInteractionGMTStartTime]
[dtInteractionGMTStopTime]
[biInteractionDuration]

The Columns in dbo.tblParticpant77 that I require are:

[iInteractionID]
[nvcStation]
[iSwitchID]
[tiDeviceTypeID]
[nvcCTIAgentName]

In nice_storage_center I have one table that I require data from:

dbo.tblStorageCenter77

The Columns from dbo.tblStorageCenter77 are:

[iInteractionID]
[iLoggerID]
[iLoggerResource]

Does that make sense ? I have managed to query two tables using WHERE, but trying to switch to a JOIN of some sort is not easy for me looking at text or videos etc.

Thanks in advance for any help you can give.

Regards

Netrix

Netrix
  • 13
  • 3
  • 2
    You should show the query that you have tried. `JOIN` seems appropriate in this case. – Gordon Linoff Feb 08 '19 at 20:59
  • The issue is that I cannot relate the information given in the Youtube videos or the forum answers to my situation – Netrix Feb 08 '19 at 21:05
  • An `INNER JOIN` will exclude non-matching records. `LEFT JOIN` includes the records in the `FROM` table and ONLY the records from the `LEFT JOIN` that match the first table. `RIGHT JOIN` does the opposite, and honestly can usually be rewritten to be a `LEFT JOIN` to make more sense. – Shawn Feb 08 '19 at 21:13
  • It is not really clear how the tables are related and what you want to select. It seems there is an interaction table. Then there is a participants table where each participants belongs to an interaction and you can link many participants to one interaction. Then there are recordings you are not interested in. And then there is a storage table linking multiple loggers to multiple interactions (so one logger can be linked to many interaction and one interaction can be linked to many loggers). Now what do you want to select? What shall the expected result look like? Show sample data and result. – Thorsten Kettner Feb 08 '19 at 23:13
  • Here are three questions that all show sample table data and the expected result. Have a look at them and see how this makes us understand what the people are actually asking and what they want to achieve. Do the same with your request. https://stackoverflow.com/questions/9171963/sql-join-two-table , https://dba.stackexchange.com/questions/175786/join-multiple-tables-for-aggregates , https://dba.stackexchange.com/questions/11367/how-to-join-two-table-and-show-one-query-result-in-mysql . – Thorsten Kettner Feb 08 '19 at 23:23

2 Answers2

0

You seem to be looking for a simple JOIN between all 4 tables :

SELECT
    i.iInteractionID,
    i.dtInteractionGMTStartTime,
    i.dtInteractionGMTStopTime,
    i.biInteractionDuration,
    p.nvcStation,
    p.iSwitchID,
    p.tiDeviceTypeID,
    p.nvcCTIAgentName,
    c.iLoggerID,
    c.iLoggerResource
FROM 
    dbo.tblInteraction77 i
    INNER JOIN dbo.tblParticpant77    p ON p.iInteractionID = i.iInteractionID
    INNER JOIN dbo.tblRecording77     r ON r.iInteractionID = i.iInteractionID
    INNER JOIN dbo.tblStorageCenter77 c ON c.iInteractionID = i.iInteractionID

This will bring up all records in tblInteraction77 that have (at least) a corresponding record in other tables, based on the UniqueRef of each table. It should return the columns you expect.

Records that do not exist in all 4 tables will not show up in the results. You can switch the INNER JOINs to LEFT JOINs to make the relation optional.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you for your time - still struggling to be honest. I will try to post the exact example I am trying to sort out – Netrix Feb 08 '19 at 21:37
  • Yes @Netrix, my answer was just based on the description of your data stuctures. If you show us actual sample data and expected results, we will be able to provide a more accurate answer... – GMB Feb 08 '19 at 21:45
  • @Netrix : I update my answer since you changed your question... Let me know if this hels more. – GMB Feb 09 '19 at 00:34
  • Sorry for the delay in replying ... back at work now. Thank you for your time in providing the query @GMB - although when running it I get an error: 'code' Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.tblInteraction77'. – Netrix Feb 11 '19 at 09:39
  • @Netrix : well, you provided us with this table name... Maybe you need to change it to the real name of the table ? – GMB Feb 11 '19 at 10:15
  • your query has an " i " after the table name (after the FROM) is that correct ? – Netrix Feb 11 '19 at 10:39
  • I removed the " i " and adapted the query slightly (I did have an error in my initial post) and it is almost perfect, so thanks again - I say "almost" because I am getting more than one row returned with the same info in it. Is there a way I can re post my query along with an example of the output ? I can't do it in these comments ? – Netrix Feb 11 '19 at 10:51
  • a tad more progress - I now realise that the " i " should have been there and that I did have a typo in one of the names (I mis-spelled Particpant) but I am still getting two identical rows fro every record in the DB – Netrix Feb 11 '19 at 14:23
  • @Netrix : yes, `i` is a table alias for `dbo.tblInteraction77`. If you have records that are totally similar, one solution to remove duplicates would be to replace `SELECT` with `SELECT DISTINCT`. Let me know if it works for you. – GMB Feb 11 '19 at 16:12
  • thank you - not tried the "DISTINCT" option yet, but have another question .... I need to add a column form a different database on the same server - I have read that I need to use the full path for the table, eg: [nice_interactions].[dbo].[tblParticipant77].[nvcAgentId]. This has worked but I need to tweak it, is there a way to repost my current problem without overwriting the initial post ? – Netrix Feb 13 '19 at 11:37
0

JOIN is what your looking for since you are going between tables, inner Join is only used between the single table itself. I'd maybe be able to give you some more help if you could show the question at hand.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31