Lostaunaum Lostaunaum - 4 months ago 8
SQL Question

Inner Join one Column multiple search Query

I know how to do a basic Inner Join query. However because of my data structure I need to get values from multiple tables in order to get the results I need. As of now in order for me to find an offense record I have to pass this query

SELECT * FROM OFFENSE where TRS_IDN IN
(SELECT TRS_IDN FROM CCH_PUBLIC.dbo.TRS where TRN_IDN IN
(select TRN_IDN from CCH_PUBLIC.dbo.TRN where IND_IDN =
(SELECT TOP 1 IND_IDN FROM CCH_PUBLIC.dbo.PERSON WHERE PER_IDN = @PER_IDN)))


This results in this:

OFF_IDN TRS_IDN AGY_TXT DOO_DTE AON_COD AOL_TXT LDA_COD GOC_COD ADN_COD ADD_TXT ADA_DTE REF_TXT IPN_NBR ICA_NBR DMV_COD

23808207 26154505 TX2270000 5/13/2013 54040014 DRIVING WHILE INTOXI MA 205 5/14/2013 TX227013A 1320573


In the
CCH_PUBLIC.dbo.TRN
table there is a column called
TRN_IDN
. The objective is to inner join the
CCH_PUBLIC.dbo.OFFENSE
table with
CCH_PUBLIC.dbo.TRN
and add the SINGLE COLUMN called
TRN_IDN
. How can I do this

Answer

I am not sure what the question is but why dont you do the following to clean up the syntax

SELECT top 1 o.* ,trs.TRN_IDN
FROM OFFENSE o
inner join CCH_PUBLIC.dbo.TRS  trs on trs.TRS_IDN = o.TRS_IDN 
inner join CCH_PUBLIC.dbo.TRN  trn on trn.TRN_IDN = trs.TRN_IDN 
inner join CCH_PUBLIC.dbo.PERSON  p on p.IND_IDN = trn .IND_IDN 
where p.PER_IDN = @PER_IDN