August 28, 2013 at 5:21 am
Hi,
The following code is almost working, it selects everything I want but slightly too much. It is duplicating some of the ld_id's as there are multiple records in the ls table against those ld_id's. I would like to remove these duplicates to basically retrun distinct ld_id's. I also want to retain the nulls against the ls_id's as it is important that I know which records do not have data in this column....
SELECT
'thistype'as ATYPE,
ls.ls_id,
ld.ld_id,
ld.name
FROM ld_table AS ld
LEFT OUTER JOIN ls_table AS ls ON ld.ld_id = ls.ld_id
--WHERE (ld.name LIKE '%' + @NAME + '%' OR @NAME IS NULL OR @NAME = '')
--AND (ld.as_id = @ID OR @ID IS NULL OR @ID = '')
ORDER BY ld.name asc
I have tried various joins and subselects but either they returned the duplicates or didn't return the ld records without a ls_id....
I hope you follow. Basically I want the distinct values from the ld_table with the ls_id populated from the ls_table.
August 28, 2013 at 5:38 am
It would be helpfull if you can provide some sample data.
From your description it looks like the [ls_table] has duplicate values of [ld_id]. When joining both tables, these duplicate values result in duplicate rows from the [ld_table].
August 28, 2013 at 5:50 am
Yes that is the case:
LS_TABLE:
ls_id L001 L002 L003
ld_id A001 A001 A002
name bob brian alex
stuff blah blah blah
LD_TABLE
ld_id A001 A002 A003
name king Qan Left
stuff blah blah blah
more blah blah blah
So I want 1 row returned for every record in LD_TABLE but I only need to know if data exists against that record in LS_TABLE. (Basically so I have an identifier, I now realise I don't actually need the ID just now). Does that make sense?
I am writing some code which highlights if an LD_TABLE record has a related LS_TABLE record and if it does I want to highlight that record.
August 28, 2013 at 5:54 am
Right, I had an epiphany....as I don't care about the actual value of the ls_id all I need to do is return the max ID so one record populates..
SELECT
'thistype' AS ATYPE,
max(ls.ls_id),
land.ld_id,
land.name
FROM LD_TABLE AS ld
LEFT JOIN LS_TABLE AS ls ON ld.ld_id = ls.ld_id
GROUP BY ld.ld_id, ld.name
August 28, 2013 at 6:07 am
Below are two solutions.
The first solution returns all columns from both [ld_table] and [ls_table] with the minimum values from [ls_tabe]. The rows include the ones from [ld_table] without a matching record in [ls_table].
The second only returns the columns and rows from [ld_table] with matching rows in [ls_table].
create table #ls_table (ls_id char(4), ld_id char(4), value_1 char(4))
create table #ld_table (ld_id char(4), value_2 char(4))
insert into #ls_table
values ('L001', 'A001', 'bail')
, ('L002', 'A001', 'alex')
, ('L003', 'A002', 'abby')
insert into #ld_table
values ('A001', 'klmn')
, ('A002', 'wxyz')
, ('A003', 'abcd')
select *
from #ld_table
left outer join (select ld_id, min(ls_id) as ls_id, min(value_1) as value_1 from #ls_table group by ld_id) as ls_table
on #ld_table.ld_id = ls_table.ld_id
select *
from #ld_table
where ld_id in (select ld_id from #ls_table)
drop table #ls_table
drop table #ld_table
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply