Find all lines where a value exists in one line

Question

Here is my query

        select order_no, pkg_no, zone_no
        from T_DETAIL_ITEM a 
        where order_no = 495
        order by order_no, pkg_no

For a given package I have zone number = 0

What I need to do is return all the lines with the pkg_no = 1597. Because one where exists with a zero zone.

I tried a few different 'where exists' lines and it isn't working.

score 2 · Accepted Answer · answered Feb 14 '20 at 17:22

2

Try to self join. This way you can put your requirement in the second table "instance" but retrieve everything from that table matches based on another common field.

select distinct a.order_no, a.pkg_no, a.zone_no
        from T_DETAIL_ITEM a 
        join T_DETAIL_ITEM b on b.pkg_no = a.pkg_no
        where b.zone_no = 0
        order by a.order_no, a.pkg_no

answered Feb 14 '20 at 17:22

jean

4,159
4
31
52

I'm a little lost with one thing, when i pull the zone numbers for table a, i get numbers ... 33, 23, 295 etc. When i do the zone numbers for table b they are all zeros. why is this. This isn't entirely correct here. – YelizavetaYR Feb 14 '20 at 18:36
@YelizavetaYR zone numbers will be always zero just because that's what we put in the `WHERE` clause (for b). All other zones number will be fetched since that line contains the same pkg number for one occurrence of a ZERO zone number. – jean Feb 14 '20 at 18:46
https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram – jean Feb 14 '20 at 18:57

score 2 · Answer 2 · answered Feb 14 '20 at 17:36

2

The accepted answer is good, but I had saw that you noted you tried EXISTS, so I wrote this example up using that method.

SELECT *
FROM T_Detail_Item  d
WHERE exists (SELECT * FROM T_Detail_Item  dx WHERE dx.pkg_no = d.pkg_no AND dx.zone_no = 0)

answered Feb 14 '20 at 17:36

Jordan Ryder

2,336
1
24
29

score 1 · Answer 3 · answered Feb 14 '20 at 17:28

One way is to self reference the table in a left join and only include those with a zone_no=0, within the join clause. The filter out non-matching records by excluding records that do not match from the left join, T2.pkg_no = NULL.

SELECT
    T1.pkg_no, T1.zone_no
FROM
    T_DETAIL_ITEM  T1
    LEFT OUTER JOIN T_DETAIL_ITEM T2 ON T2.pkg_no = T1.pkg_no AND T2.zone_no = 0
WHERE
    NOT T2.pkg_no IS NULL

score -1 · Answer 4 · answered Feb 14 '20 at 17:24

-1

If I correctly understood the question, you need something like this BEGIN

declare @str varchar(max);

set @str='';

SELECT  @str=@str+ [YOURCOLUMB]

  FROM [YOURTABLE]

SELECT @str

END

answered Feb 14 '20 at 17:24

Marco

127
6

Find all lines where a value exists in one line

4 Answers4