February 1, 2015 at 1:29 pm
Hi,
I hope i'm doing this correctly.
I have 3 Tables
TableA - TAID, Name, LastName
TableB - MaleFriendsName, MaleFriendsLastName [One to many]
TableC - FemaleFriendsName, FemaleFriendsLastName [one to many]
A query returns duplicate results from TableB as well as TableC
TableB and TableC have nothing in common and should not interfere with each other.
with TwoTables as (
select
a.QuickSpec as QuickSpecId,
a.BusinessName as SpecBusinessName,
a.Location as SpecLocation,
a.Topic as SpecTopic,
b.SpecIDAnalysis as BId,
b.ImageField as BCol1,
b.ImageInfo as BCol2,
rn = row_number() over (partition by a.QuickSpec order by (select null))
from
QuickSpec a
inner join Analysis b
on (QuickSpec = SpecIDAnalysis)
)
select
QuickSpecId,
SpecBusinessName,
SpecLocation,
SpecTopic,
BId,
BCol1,
BCol2,
c.SpecIDMultiple as CId,
c.Image1 as CCol1,
c.Image2 as CCol2
from
TwoTables tt
left outer join QuickSpecMultiple c
on (tt.QuickSpecId = c.SpecIDMultiple
and tt.rn = 1);
Resultset returns duplicate values on TableB AND only for 1 record in the results [As per Lynn examples]
February 1, 2015 at 2:21 pm
First, the png file really doesn't cut it. You need to post the DDL (CREATE TABLE statements) for the tables involved, sample data for the tables as INSERT INTO statements to populate the tables with sample (NOT production) data, and then the expected results based on the sample data you provide.
Also, be sure to test all your scripts in an empty database to be sure the run without errors. Also make sure you are consistent in the naming of columns including case sensitivity (even in a case insensitive environment).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply