[MSSQL] filter in een JOIN

[Verwijderd]

Bij het gebruik van een LEFT JOIN loop ik tegen een probleem aan, waar ik niet uit kom...

Van table1 wil ik een compleet overzicht met alleen de koppeling uit table2 met de jongste datum/tijd.
Hieronder de export van een voorbeeld:

SQL:

CREATE TABLE table1 (
    id Int IDENTITY NOT NULL,
    name Char(50), 
    CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED (
      id
    )
)
COMMIT;

CREATE TABLE table2 (
    id Int IDENTITY NOT NULL,
    table1_id Int,
    in_date_time DateTime NOT NULL, 
    CONSTRAINT PK_table2 PRIMARY KEY CLUSTERED (
      id
    )
)
COMMIT;
ALTER TABLE dbo.table2
  ADD CONSTRAINT DF_table2_in_date_time DEFAULT getdate() FOR in_date_time
COMMIT;

INSERT INTO table1(id, name) VALUES (1, 'sander');
INSERT INTO table1(id, name) VALUES (2, 'willem');
INSERT INTO table1(id, name) VALUES (3, 'paul');
INSERT INTO table1(id, name) VALUES (4, 'dick');

INSERT INTO table2(id, table1_id, in_date_time) VALUES (2, 1, '12/09/2008 16:05:21.000');
INSERT INTO table2(id, table1_id, in_date_time) VALUES (3, 1, '12/09/2008 16:10:21.000');
INSERT INTO table2(id, table1_id, in_date_time) VALUES (4, 1, '12/09/2008 16:30:21.000');
INSERT INTO table2(id, table1_id, in_date_time) VALUES (5, 2, '12/10/2008 10:05:21.000');
INSERT INTO table2(id, table1_id, in_date_time) VALUES (6, 2, '12/10/2008 10:03:21.000');
INSERT INTO table2(id, table1_id, in_date_time) VALUES (7, 3, '12/07/2008 15:05:21.000');

Onderstaande query heb ik wel, maar daarin zie je dubbele records uit table1:

SQL:

SELECT t1.*, t2.in_date_time 
  FROM table1 AS t1 
    LEFT JOIN table2 AS t2 
    ON t1.id = t2.table1_id

Uitkomst van bovenstaande query:

id	name	in_date_time
1	sander	09/12/2008 16:05:21
1	sander	09/12/2008 16:10:21
1	sander	09/12/2008 16:30:21
2	willem	10/12/2008 10:05:21
2	willem	10/12/2008 10:03:21
3	paul	07/12/2008 15:05:21
4	dick	null

Heeft iemand een idee hoe deze query uit te breiden zodat ik alleen de records terug krijg uit table1 en de jongste datum/tijd (in_date_time) uit table2?

Onderstaand resultaat moet er eigenlijk uit komen:

id	name	in_date_time
1	sander	09/12/2008 16:30:21
2	willem	10/12/2008 10:05:21
3	paul	07/12/2008 15:05:21
4	dick	null

Alvast bedankt!

[RobIII]

Kijk eens naar Aggregate functies en Hoe werkt dat GROUP BY nu eigenlijk?

SQL:

SELECT t1.id, t1.name, max(t2.in_date_time)
  FROM table1 AS t1 
    LEFT JOIN table2 AS t2 
    ON t1.id = t2.table1_id
GROUP BY t1.id, t1.name

[ Voor 60% gewijzigd door RobIII op 10-12-2008 11:52 ]

[Acid__Burn]

Verwijderd schreef op woensdag 10 december 2008 @ 11:46:
Bij het gebruik van een LEFT JOIN loop ik tegen een probleem aan, waar ik niet uit kom...

Van table1 wil ik een compleet overzicht met alleen de koppeling uit table2 met de jongste datum/tijd.
Hieronder de export van een voorbeeld:

SQL:

*knip*

Ik heb dit probleem onlangs ook moeten oplossen. Gebruik je SQL 2000 of 2005? Voor 2005 heb je namelijk de CROSS APPLY functie. Daar zou ik eens naar kijken als ik jou was. Zo heb ik het probleem opgelost...

[Verwijderd]

Beide SQL versies moet ondersteund worden, zowel 2000 als 2005.

[RobIII]

Verwijderd schreef op woensdag 10 december 2008 @ 12:01:
Beide SQL versies moet ondersteund worden, zowel 2000 als 2005.

Lees mijn post eens; wat die CROSS APPLY van Acid__Burn in dit topic te zoeken heeft is me een raadsel... En die zaken die ik noem werken al sinds het stenen tijdperk.

[ Voor 11% gewijzigd door RobIII op 10-12-2008 12:08 ]

[EfBe]

Je kunt de table2.in_date_time vervangen door een scalar subquery. Dit kan handiger zijn dan een groupby, wanneer de select list erg groot is:

SQL:

SELECT t1.*, 
(
    select  TOP 1 t2.in_date_time 
    FROM    table2 t2 
    WHERE   t2.id = t1.id
    ORDER BY t2.in_date_time DESC
) as in_date_time
FROM table1 AS t1 

(werkt niet op CE. Cross apply heeft er niets mee te maken. Cross apply heb je alleen nodig wanneer je 2 sets joint (A en B ) en je wilt B filteren op de aggregated data van A)

[ Voor 21% gewijzigd door EfBe op 10-12-2008 12:12 ]

[RobIII]

EfBe schreef op woensdag 10 december 2008 @ 12:10:
Je kunt de table2.in_date_time vervangen door een scalar subquery. Dit kan handiger zijn dan een groupby, wanneer de select list erg groot is:

Zou je dat uit kunnen leggen? Volgens mij compliceert het alleen maar de query
Of begrijp ik je verkeerd en bedoel je met 'select list' het aantal velden dat je selecteert uit t1?

[Verwijderd]

"EfBe" de sub-query werkt goed bij deze 2 voorbeeld tabellen, maar gaat niet op in mijn situatie.
In deze test situatie wordt er 1 veld teruggegeven. Maar in mijn situatie worden er meerdere velden geresulteerd.

[RobIII]

Verwijderd schreef op woensdag 10 december 2008 @ 12:22:
"EfBe" de sub-query werkt goed bij deze 2 voorbeeld tabellen, maar gaat niet op in mijn situatie.
In deze test situatie wordt er 1 veld teruggegeven. Maar in mijn situatie worden er meerdere velden geresulteerd.

Heb je nou inmiddels al eens gewoon de links die ik je gaf gelezen

[Verwijderd]

Dit heb ik inderdaad gelezen, thanx!
De query die ik in mijn situatie heb is complexer dan het voorbeeldje dat ik hier omschreef, dus vandaar dat het iets langer duurt om het toe te passen

[Verwijderd]

In m'n query heb ik het nu voor elkaar dat ik de complete tabel1 terug krijg, uitgebreid met de velden uit table2, zonder dubbele records.

Maar in mijn huidige situatie heeft table2 2 extra integer velden die linken naar 2 andere tabellen.
De velden van de huidige recordset moeten worden uitgebreid met een aantal velden uit deze 2 andere tabellen.

Wanneer ik hier een JOIN gebruik, krijgt m'n huidige recordset weer dubbele records.
Is hier een oplossing voor, volgens mij heeft hier een group by geen zin?

Bedankt allemaal, voor de hulp zover!

[RobIII]

Als je nou eens gewoon de échte situatie (tabellen en queries) post in plaats van abstract zitten wezen; dan kunnen wij je vast helpen.

[Verwijderd]

Hieronder de tabel structuur:

SQL:

CREATE TABLE dbo.link_visitor (
    id Int IDENTITY NOT NULL,
    visit_id Int,
    location_id Int,
    vehicle_id Int,
    item_id Int,
    badge_id Int,
    comment VarChar(250),
    in_user VarChar(50),
    in_date_time DateTime NOT NULL, 
    CONSTRAINT PK__link_visitor__047AA831 PRIMARY KEY CLUSTERED (
      id
    )
)
GO

CREATE TABLE dbo.person (
    id Int IDENTITY NOT NULL,
    person_category_id Int NOT NULL,
    firstname VarChar(250) NOT NULL,
    lastname VarChar(250) NOT NULL,
    birthdate VarChar(25) NOT NULL,
    telephonenumber1 VarChar(50) NOT NULL,
    telephonenumber2 VarChar(50) NOT NULL,
    mobilephonenumber1 VarChar(50) NOT NULL,
    mobilephonenumber2 VarChar(50) NOT NULL,
    faxnumber VarChar(50) NOT NULL,
    emailaddress VarChar(250) NOT NULL,
    semaphonenumber VarChar(50) NOT NULL,
    company VarChar(50) NOT NULL,
    address VarChar(50) NOT NULL,
    zipcode VarChar(50) NOT NULL,
    city VarChar(50) NOT NULL,
    district VarChar(50) NOT NULL,
    country VarChar(50) NOT NULL,
    comment Image,
    in_user VarChar(50) NOT NULL,
    in_date_time DateTime,
    mut_user VarChar(50) NOT NULL,
    mut_date_time DateTime,
    person_code VarChar(50) NOT NULL,
    job_description VarChar(250) NOT NULL,
    sort_prio Int,
    person_photo Image,
    person_status VarChar(25),
    building VarChar(50),
    department VarChar(50),
    room_number VarChar(10),
    identification_type Int,
    identification_number VarChar(50),
    gender Int,
    marital_status Int,
    date_employ DateTime,
    date_dismissal DateTime, 
    CONSTRAINT PK__person__4E88ABD4 PRIMARY KEY CLUSTERED (
      id
    )
)
GO

CREATE TABLE dbo.visitor_registration (
    id Int IDENTITY NOT NULL,
    datetime_appointment DateTime,
    datetime_arrival DateTime,
    datetime_leaving DateTime,
    visit_host_person_id Int,
    visit_visitor_person_id Int,
    visit_reason VarChar(250),
    visit_note VarChar(250),
    is_present Int NOT NULL,
    is_gone Int NOT NULL,
    in_user VarChar(50),
    in_date_time DateTime,
    mut_user VarChar(50),
    mut_date_time DateTime,
    datetime_appointment_end DateTime,
    project_id Int, 
    CONSTRAINT PK__visitor_registra__00AA174D PRIMARY KEY CLUSTERED (
      id
    )
)
GO

CREATE TABLE dbo.badge (
    id Int IDENTITY NOT NULL,
    badge_number VarChar(25),
    badge_description VarChar(50),
    badge_status VarChar(25),
    badge_active Int NOT NULL,
    badge_returned Int NOT NULL, 
    CONSTRAINT PK__badge__7CD98669 PRIMARY KEY CLUSTERED (
      id
    )
)
GO

Onderstaand de query die ik tot dusver heb:

SQL:

(SELECT
       b.*,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 0 AND vr.is_gone = 0 AND b.badge_returned = 0) AND b.id = lv.badge_id) AS BadgeAssigned,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 1 AND vr.is_gone = 0 AND b.badge_returned = 0) AND b.id = lv.badge_id) AS BadgeGiven,
       pvr.is_gone,
       pvr.is_present,
       pvr.in_date_time,
       p.firstname,
       p.lastname,
       p.birthdate,
       p.building,
       p.department,
       p.room_number,
       ph.firstname AS host_firstname,
       ph.lastname AS host_lastname,
       ph.birthdate AS host_birthdate,
       ph.building AS host_building,
       ph.department AS host_department,
       ph.room_number AS host_room_number,
       ph.telephonenumber1 AS host_phonenumber,
       ph.mobilephonenumber1 AS host_mobilenumber

 FROM badge AS b, person AS p
      LEFT JOIN visitor_registration AS pvr ON p.id = pvr.visit_visitor_person_id
      LEFT JOIN link_visitor AS plv ON plv.visit_id = pvr.id
      LEFT JOIN person AS ph ON ph.id = pvr.visit_host_person_id
 WHERE plv.badge_id = b.id AND
       b.badge_active = 1 AND
       b.badge_returned = 0)

UNION

(SELECT
       b.*,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 0 AND vr.is_gone = 0) AND b.id = lv.badge_id) AS BadgeAssigned,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 1 AND vr.is_gone = 0) AND b.id = lv.badge_id) AS BadgeGiven,
       0, 0, null,'', '', '', '', '', '', '', '', '', '', '', '', '', ''
FROM badge AS b WHERE b.badge_returned = 1 OR b.id NOT IN
     (SELECT badge_id FROM link_visitor WHERE badge_id IS NOT NULL)) ORDER BY b.id

We hebben dus een 4 tal tabellen:
badge = passen voor de bezoekers
visitor_registration = bezoek registraties
person = bezoeker/gastheer
link_visitor = koppel tabel tussen visitor_registration en badge

Uit deze query moet dus een overzicht komen van alle passen, hierbij rekening houdend met de jongste datum/tijd in het veld in_date_time veld in de visitor_registration tabel. Inclusief de volgende informatie:
- bezoek informatie
- bezoeker informatie
- gastheer/vrouw informatie

Met de huidige query (zie bovenaan) krijg ik het complete overzicht maar er komen dubbele records voor.

Ik hoop dat het een en ander duidelijk is...

[RobIII]

Ik zie geen group by en aggregates?

[Verwijderd]

Nee klopt, onderstaande query moet het zijn, mijn excuses...

SQL:

(SELECT
       b.*,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 0 AND vr.is_gone = 0 AND b.badge_returned = 0) AND b.id = lv.badge_id) AS BadgeAssigned,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 1 AND vr.is_gone = 0 AND b.badge_returned = 0) AND b.id = lv.badge_id) AS BadgeGiven,
       pvr.is_gone,
       pvr.is_present,
       max(pvr.in_date_time),
       p.firstname,
       p.lastname,
       p.birthdate,
       p.building,
       p.department,
       p.room_number,
       ph.firstname AS host_firstname,
       ph.lastname AS host_lastname,
       ph.birthdate AS host_birthdate,
       ph.building AS host_building,
       ph.department AS host_department,
       ph.room_number AS host_room_number,
       ph.telephonenumber1 AS host_phonenumber,
       ph.mobilephonenumber1 AS host_mobilenumber

 FROM badge AS b, person AS p
      LEFT JOIN visitor_registration AS pvr ON p.id = pvr.visit_visitor_person_id
      LEFT JOIN link_visitor AS plv ON plv.visit_id = pvr.id
      LEFT JOIN person AS ph ON ph.id = pvr.visit_host_person_id
 WHERE plv.badge_id = b.id AND
       b.badge_active = 1 AND
       b.badge_returned = 0

 GROUP BY b.id, b.badge_number, b.badge_active, b.badge_description, b.badge_returned, b.badge_status,  pvr.is_gone, pvr.is_present,
       p.firstname, p.lastname, p.birthdate, p.building, p.department, p.room_number, p.telephonenumber1, p.mobilephonenumber1,
       ph.firstname, ph.lastname, ph.birthdate, ph.building, ph.department, ph.room_number, ph.telephonenumber1, ph.mobilephonenumber1
 )

UNION

(SELECT
       b.*,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 0 AND vr.is_gone = 0) AND b.id = lv.badge_id) AS BadgeAssigned,
       (SELECT COUNT(*) FROM link_visitor AS lv JOIN visitor_registration AS vr ON lv.visit_id = vr.id WHERE lv.badge_id IS NOT NULL AND (vr.is_present = 1 AND vr.is_gone = 0) AND b.id = lv.badge_id) AS BadgeGiven,
       0, 0, null,'', '', '', '', '', '', '', '', '', '', '', '', '', ''
FROM badge AS b WHERE b.badge_returned = 1 OR b.id NOT IN
     (SELECT badge_id FROM link_visitor WHERE badge_id IS NOT NULL)) ORDER BY b.id

[EfBe]

RobIII schreef op woensdag 10 december 2008 @ 12:14:
[...]

Zou je dat uit kunnen leggen? Volgens mij compliceert het alleen maar de query
Of begrijp ik je verkeerd en bedoel je met 'select list' het aantal velden dat je selecteert uit t1?

Als je grouped moet je alle velden in de select list ook in de group by list zetten. Met een scalar query hoeft dat niet, je hebt immers geen group by. Het is wel zo dat een group by efficienter kan zijn.

@TS: ik snap niet waarom de scalar niet zou kunnen?

[RobIII]

EfBe schreef op donderdag 11 december 2008 @ 09:00:
Als je grouped moet je alle velden in de select list ook in de group by list zetten. Met een scalar query hoeft dat niet, je hebt immers geen group by. Het is wel zo dat een group by efficienter kan zijn.

Dat begreep ik al ja, maar gevoelsmatig lijkt de group by me sowieso efficiënter terwijl jij aangeeft dat 'ie efficiënter kan zijn.

[EfBe]

RobIII schreef op donderdag 11 december 2008 @ 10:22:
[...]

Dat begreep ik al ja, maar gevoelsmatig lijkt de group by me sowieso efficiënter terwijl jij aangeeft dat 'ie efficiënter kan zijn.

Meer voor het ingeven van de query, niet voor het runnen van de query (daarom zei ik ook 'handiger' )

[ Voor 4% gewijzigd door EfBe op 11-12-2008 12:59 ]