How to join a table with many rows to a table with only one row without any duplicate data

Question

I am having trouble where my INNER JOIN duplicates data. I have a fault table and a media table which stores image filepaths.

Table 1: fault

Table 2: media

A user uploads their fault into table: fault, and then up to 7 images to support their application in to table: media. At present when 5 images say are uploaded, the fault data is repeated 5 times.

SELECT * FROM fault INNER JOIN media ON media.syncReportID = fault.faultID where faultID = {0}

This is ok but when you inner join something, everything is returned for every item in the joined table.

This SO suggests: DISTINCT- I get no exception but it does not work for me.

SELECT DISTINCT * FROM fault INNER JOIN media ON media.syncReportID = fault.faultID where faultID = {0}

This SO article here suggets: OUTER APPLY and GROUP BY but being honest I do not understand the complex syntax in these articles.

Can someone advise which SQL command would best suit the ability to allow many images to one story without duplicating the story many times over.

Is this even possible here?

Thanks in advance!

Uh, what table has duplicates? What table doesn't? Sample data and desired results would really help. — Gordon Linoff, Jul 26 '17 at 23:15
I suspect you want to do 2 separate queries - one to show the fault information, another that gets all the related images. This way you'll always get what you need. — trailmax, Jul 26 '17 at 23:35

score 1 · Answer 1 · answered Jul 26 '17 at 23:16

1

I suspect that you want:

SELECT f.*, m.*
FROM fault f CROSS APPLY
     (SELECT TOP 1 m.*
      FROM media m
      WHERE m.syncReportID = f.faultID
     ) m
WHERE faultID = {0};

You would use OUTER APPLY if you always want exactly one row returned, even when there are no matching entries.

answered Jul 26 '17 at 23:16

Gordon Linoff

1,242,037
58
646
786

Thanks. This returns just one image though, not all related images – davvv Jul 26 '17 at 23:26

score 0 · Answer 2 · answered Jul 26 '17 at 23:26

0

You may need to specify the fields you want to be distinct like :

 SELECT DISTINCT fault.faultId,...... FROM fault INNER JOIN media ON media.syncReportID = fault.faultID where faultID = {0}

answered Jul 26 '17 at 23:26

Eid Morsy

966
6
8

How to join a table with many rows to a table with only one row without any duplicate data

2 Answers2