[Resolved] Join on 1st record - 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)

[Resolved] Join on 1st record

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-24 : 11:03:29

I have a query that joins 2 tables (on db2/400):

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   shkcoo,
           shdoco,
           shdcto,
           shmcu, 
           shan8,
           shshan,
           shpa8,
           shvr01,
           shptc,
           shtxa1,
           shexr1,
           shaft,
           wajobn
from       VGISOEDTA.F42019 as f1
inner join VGIWRQLIB.WRQASI as f2 on f2.wajob = f1.shvr01
order by   shkcoo, 
           shdoco '')'

I join on job number from table f1 to table f2. Table f2 could have multiple records for same job number. I only need to join on first record found to get the job name. How can I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 11:58:46

when you say first record on what basis you want it? unless you specify it based on order of unique valued column there's no concept of first and last in sql table.probably you can give some sample data to illustrate your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-24 : 12:18:02

In f1 I have a job number that is unique.

In f2 I could have several records for same job number. What I am looking for is job name which is the same for all f2 records. Below I have illustrated that there could be multiple records for job number 123, they all have the same job name. I just need one hit on f2 to get job name

F2

Job Number 123
Ticket Number 200
Job Name ABC

Job Number 123
Ticket Number 564
Job Name ABC

Job Number 123
Ticket Number: 897
Job Name": ABC

I tried this:

from       VGISOEDTA.F42019 as f1
left join (
            select distinct wajob
            from VGIWRQLIB.WRQASI
           )
            as f2 on f2.wajob = f1.shvr01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:26:10

it should be like


SELECT *
FROM f1
INNER JOIN (SELECT JobNumber,MAX(TicketNumber) AS Latest
            FROM f2
            GROUP BY JobNumber)ftmp
ON ftmp.JobNumber = f1.JobNumber
AND ftmp.Latest = f1.TicketNumber

this will give latest ticket associated with job in f2 (ie 897 for job 123). if you want earliest ticket use MIN instead of MAX

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-08-24 : 12:30:11

Got it working this way:

from       VGISOEDTA.F42019 as f1
left join (
            select distinct wajob, wajobn
            from VGIWRQLIB.WRQASI
          ) 
            as f2 on f2.wajob = f1.shvr01

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:31:56

why do you need wajobn from f2? doent look like you're using it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources