SQL Query - one column must be different, a restricted column must be the most recent

Question

I have a CATEGORIES table that references an ITEMS table (one to many). Each element table can have several barcodes in the BARCODES table (one for many).

The idea is that at some point a new barcode can be added to the element, but the old data is stored in the BARCODES table, so if the search is performed in order with the data for the last year, then the element and category the old barcode can be found quickly.

Hope this makes sense - it's a bit of a simplification.

An example of my tables:

CATEGORIES TABLE
ID   NAME        CODE    ACCOUNTING_REFERENCE
1    Beverages   BEV     Stock_Beverages
2    Pies        PIE     Stock_Pies
3    Chips       CHP     Stock_Chips

ITEMS TABLE
ID   CATEGORY_ID  DESCRIPTION                 BASE_COST
1    1            Red Bull (single)           $4.50
2    2            Ponsonby Pie - Mince Cheese $2.99
3    1            Coke Can (single)           $3.50
4    2            Big Ben - Steak Pepper      $1.99

BARCODES TABLE
ID  ITEM_ID  BARCODE   ACTIVE_FROM
1   1        XSD123    2009/10/11
2   2        AXF123    2009/10/12
3   3        XYZ234    2009/10/11
4   1        NEW001    2010/01/05
5   1        NEW002    2010/01/05*

• , - . , . , - , , "", . , - , .

. SQL , . , - . .

.

, - . , - , , , ( ) BARCODE.ID .

, , - - , .

:

ITEM_ID   DESCRIPTION                  BASE_COST  CATEGORY_NAME CATEGORY_CODE  ACCOUNTING_REFERENCE  BARCODE
1         Red Bull (single)            $4.50      Beverages     BEV            Stock_Beverages       NEW002
2         Ponsonby Pie - Mince Cheese  $2.99      Pies          PIE            Stock_Pies            AXF123
3         Coke Can (single)            $3.50      Beverages     BEV            Stock_Beverages       XYZ234
4         Big Ben - Steak Pepper       $1.99      Pies          PIE            Stock_Pies            <null>

, BARCODE , .

, : , SQL-, , , .

, ITEM_ID . , ITEM_ID, PK/FK, , .

, sql, . , . , - , . , - .

, , .

Gabe , - . , , , -, .

, , . , , . , , , .

. , -, , - , , .

()

. , , .

, simon, SQL, - .

- , , . - , , .

.

Answer 1

, . :

 select *
from ITEMS
join BARCODES as B on ITEMS.ID = B.ITEM_ID
join CATEGORIES on CATEGORIES.ID = ITEMS.CATEGORY_ID
where B.ACTIVE_FROM =
    (select max(ACTIVE_FROM)
     from BARCODES as B2
     where B2.ITEM_ID = B.ITEM_ID)

, - , -, . ANSI SQL :

 select *
from ITEMS
LEFT OUTER join BARCODES as B on ITEMS.ID = B.ITEM_ID
join CATEGORIES on CATEGORIES.ID = ITEMS.CATEGORY_ID
where B.ACTIVE_FROM =
    (select max(ACTIVE_FROM)
     from BARCODES as B2
     where B2.ITEM_ID = B.ITEM_ID)
    AND B.ID =
    (SELECT MAX(ID)
     FROM BARCODES AS B3
     WHERE B3.ITEM_ID = B.ITEM_ID)
    OR ACTIVE_FROM IS NULL

Answer 2

- :

use tempdb
go
if exists (select 1 from sys.objects where name = 'barcodes')
    drop table barcodes
if exists (select 1 from sys.objects where name = 'items')
    drop table items
if exists (select 1 from sys.objects where name = 'categories')
    drop table categories
go
create table categories (
    id int primary key,
    [name] nvarchar(30),
    code char(3),
    accounting_reference nvarchar(30)
)
create table items (
    id int primary key,
    category_id int foreign key references categories (id),
    description nvarchar(50),
    base_cost money
)
create table barcodes (
    id int primary key,
    item_id int foreign key references items (id),
    barcode varchar(10),
    active_from datetime
)
go
insert into categories (id, [name], code, accounting_reference)
select 1, 'Beverages', 'BEV', 'Stock_Beverages' union all
select 2, 'Pies', 'PIE', 'Stock_Pies' union all
select 3, 'Chips', 'CHP', 'Stock_Chips'

insert into items (id, category_id, description, base_cost)
select 1, 1, 'Red Bull (single)', 4.5 union all
select 2, 2, 'Ponsonby Pie - Mince Cheese', 2.99 union all
select 3, 1, 'Coke Can (single)', 3.50 union all
select 4, 2, 'Big Ben - Steak Pepper', 1.99

insert into barcodes (id, item_id, barcode, active_from)
select 1, 1, 'XSD123', '2009/10/11' union all
select 2, 2, 'AXF123', '2009/10/12' union all
select 3, 3, 'XYZ234', '2009/10/11' union all
select 4, 1, 'NEW001', '2010/01/05' union all
select 5, 1, 'NEW002', '2010/01/05'

;with x as (
    select item_id, max(active_from) active_from, max(id) id
    from barcodes
    group by item_id
),
y as (
    select item_id, barcode
    from barcodes
    where exists (select 1 from x where item_id = barcodes.item_id and id = barcodes.id and active_from = barcodes.active_from)
)
select t1.id item_id, t1.description, t1.base_cost, t2.name category_name, t2.code category_code, t2.accounting_reference, t3.barcode
from items t1 left join categories t2 on (t1.category_id = t2.id)
    left join y t3 on (t1.id = t3.item_id)