Query The Same Fİeld Name
-
Hi Member ,
3 table joın query problem city name the same
-
What is the expected outcome of the query?
-
True
-
OK, so you want one row not two.
Something like the second select?
create table #nakliyeFiyat
(ID INT,
gidis INT,
donus INT,
tarih datetime,
fiyat int,
aractipi int
)
create table #aractipleri
(id int,
aractipi varchar(10)
)
create table #il
(id int,
iladi varchar(10),
iladi2 varchar(10)
)
insert into #nakliyeFiyat values
(32,4,6,GETDATE(),0,1),
(31,1,2,GETDATE(),0,2)
insert into #aractipleri values
(1,'pikap'),
(2,'10Teker')
insert into #il values
(1,'ADANA','ADANA'),
(2,'ADIYAMAN','ADIYAMAN')
select * from #nakliyeFiyat asNF
join #aractipleri as at on at.id = asNF.aractipi
join #il as iller ON iller.id = asNF.gidis or iller.id = asNF.donus
select asnf.*, at.*, iller.id, iller.iladi, iller2.id, iller2.iladi from #nakliyeFiyat asNF
join #aractipleri as at on at.id = asNF.aractipi
join #il as iller ON iller.id = asNF.gidis
join #il as iller2 on iller2.id = asNF.donus
Also when you have a spare moment could you please read the link in my signature on posting code and data for the best help, it will greatly help in how to post issues like this as pictures are not always best, especially if we need to mimic your environment on our systems.
-
You didn't properly share the problem. So we all have to guess what exactly your problem is. Its always better to share detail of the problem and your desired output to let us understand what actually is going on.
Its all guess work. but i would like to share it hopefully it will help you.
----------- Sample Data
declare @lookup table
(
id int,
name varchar(10)
)
insert into @lookup
select 1, 'A' union all
select 2, 'A' union all
select 3, 'B'
Declare @table1 table (
colA int,
colB int,
ColC varchar(10)
)
insert into @table1
select 1,3,'Aaa' union all
select 1,2,'Baa' union all
select 2,3,'Caa'
----------- Problem Joinning Single table with multiple columns
--- Problem : this will more rows because of the nature of the join.
--- because ColA, ColB values exists in @lookup table.
--- When you apply OR what you actually asking the db engire to match ColA, OR Match ColB
--- As the data exists for both column sql server will return twice rows.
select *
from @table1 a
join @lookup l on a.colA = l.id OR a.colB = l.id
---- To avoid this issue you have tou reference the @Lookup Table twice.
---- A Table can be joinned multiple time in a single query. The only thing required is to change the Alias name
---- As shown below.
select *
from @table1 a
join @lookup l on a.colA = l.id
join @lookup ll on a.colB = ll.id
hope it helps, If it not then do share your complete problem and desired output.
-
-
Happy to help.
Please do take a look at the posting code/data link in my signature, will help us to help you that bit quicker as we can easily recreate your problem and know how you want the output to be.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply