Find unique group ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find unique group ID

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2009-05-11 : 09:58:42

Good morning!
I have a table DOC_LIN that has order lines in it. Each order line has a location ID for where it will be picked up, along with the vendor to order from, like so:


DOC |    ITEM    |   VEND |    LOC
------------------------------------
1   |   HOOZIT   |   45   |   STORE
1   |   HOOZIT   |   45   |   TRUCK
2   |   THINGY   |   20   |   STORE
2   |   HOOZIT   |   45   |   STORE

Now, I also have a table LOC_GRP that has locations and the groups they belong to, like so:


GRP    |    LOC   |  SEQ
---------------------------
A      |  STORE   |   1
A      |  WAREHSE |   2
B      |  STORE   |   1
B      |  TRUCK   |   2

From my example since I need to order 2 HOOZIT for STORE and 1 for TRUCK, I need to get the corresponding GRP, in this case 'B'.
If I do:

SELECT distinct(LOC),VEND from DOC_LIN

I get:


LOC   | VEND
-------------
STORE | 45
TRUCK | 45
STORE | 20

How can I get GRP 'B' for VEND 45 based on those LOC's?
I've been playing around with HAVING (Peso helped me with a different issue related to this and it worked great) But I can't seem to figure it out...

TIA,
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 10:23:58

Is this what you want ?


select *
from   LOC_GRP lg
       inner join
       (
           SELECT distinct LOC, VEND from DOC_LIN
       ) d on lg.LOC = d.LOC

KH
[spoiler]Time is always against us[/spoiler]

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2009-05-11 : 12:38:05

Khtan,
Almost: I think I need to explain myself better. When I run your query, I am getting:


GRP | LOC    | VEND
-------------------
B   | STORE  | 20
B   | STORE  | 45
B   | TRUCK  | 45
A   | STORE  | 45

The last row is there since LOC 'STORE' is in both GRP 'A' and 'B'; what I need is GRP 'B' as it contains ALL the locations, where 'A' does not...
Sorry for the confusion!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 14:26:40

[code]
SELECT m.GRP,lg.LOC,dl.VEND
FROM
(
SELECT m.GRP
FROM
(
SELECT l.LOC,g.GRP
FROM (SELECT DISTINCT LOC FROM DOC_LIN WHER VEND=45)l
CROSS JOIN (SELECT DISTINCT GRP FROM LOC_GRP) g
)m
LEFT JOIN LOC_GRP g
ON g.GRP=m.GRP
AND g.LOC=m.LOC
GROUP BY m.GRP
HAVING SUM(CASE WHEN g.GRP IS NULL THEN 1 ELSE 0 END) =0
)r
INNER JOIN LOC_GRP lg
ON lg.GRP=m.GRP
INNER JOIN DOC_LIN dl
ON dl.LOC=lg.LOC
[/code]

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2009-05-11 : 18:21:52

Visakh16,
Bullseye! Thank you for getting me on the right path.
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2009-05-20 : 09:26:12

Well,
I thought I was out of the woods on this, but things aren't happening quite right yet...
Let me explain exactly what my problem is:
We have a procedure that is used to create orders, based on distinct LOC_ID's from an order table ORD_DOC. When we create a purchase order we may have several unique LOC_ID's for each vendor, and we need to find the appropriate location group that they ALL belong to, IF it exists. If not, we just need to exit the code, so an empty result set is fine. The location group table LOC_GRP has GRP_LOC,SEQ_NO, and LOC_ID. What is needed is to find the top 1 (doesn't matter which) GRP_LOC has ALL of the LOC_ID's that are returned from

select distinct(LOC_ID),vend_no
from ORD_DOC
group by vend_no

This may return several LOC_ID's or only 1 but never none for each vendor(there may be one or many vendors). If it returns one location, then there is no allocated workgroup needed, so we bypass this code. When there is more than one, then trouble. I'm trying to avoid going to a cursor to do this...
I have tried visakh16's code, and thought I had it working, but it is still picking the odd row such as in khtan's code. Can someone shed some more light on this? Perhaps this needs a crosstab query to work?
Calling Dr. CrossJoin!

There's never enough time to type code right,
but always enough time for a hotfix...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-05-20 : 10:27:30

Maybe:

SELECT L.*, D.GRP
FROM DOC_LIN L
    LEFT JOIN
    (
        SELECT D1.VEND, D1.GRP
            ,ROW_NUMBER() OVER (PARTITION BY D1.VEND ORDER BY D1.VEND) AS RowNum
        FROM
        (
            SELECT L1.VEND, G1.GRP, COUNT(DISTINCT L1.LOC) AS LocCount
            FROM DOC_LIN L1
                JOIN LOC_GRP G1
                    ON L1.LOC = G1.LOC
            GROUP BY L1.VEND, G1.GRP
        ) D1
            JOIN
            (
                SELECT L2.VEND, COUNT(DISTINCT L2.LOC) AS LocCount
                FROM Doc_Lin L2
                GROUP BY L2.VEND
                HAVING COUNT(DISTINCT L2.LOC) > 1
            ) D2
                ON D1.VEND = D2.VEND
                    AND D1.LocCount = D2.LocCount
    ) D
        ON L.VEND = D.VEND
            AND D.RowNum = 1

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2009-05-20 : 11:39:39

Ifor,
When I first ran your code and it returned all the rows from the DOC table I thought to myself "Ah, this isn't right either" And just before I went to change the top select query, I looked again and sure enough the results are correct. Not only that, but by returning all the rows I can simplify the proc by creating a temp table for purchase order header AND lines all together.
as they say in 'hood:
You Da MAN!

Thanks for your insight!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources