combining tables - 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)

combining tables

Author

Topic

hanavan
Starting Member

25 Posts

Posted - 2008-03-11 : 18:52:05

I have a database, and I need data from 3 tables.
From the first table I just need the primary key: issueID.

I can make a left outer join with my second table so I can have this result.

select A.issueID, B.projectID
from issuerequest as A left outer join projects as B on (A.issueID=B.referenceID)
group by A.issueID

gives:
100 | null
101 | P003
102 | P002
103 | null
...

When the value in the second column is null, I need to check the value
in the 3th table.

The join between the first and the thirth is:

select C.issueID, min(D.project) as Project
from issuerequest as C left outer join ProductCustomer
on (C.customer = D.customerID) and
(C.product=D.produktID)
where actuellproduct='1'
group by C.issueID

this gives a result as:
100 | P002
100 | P003
101 | P004
102 | P002
103 | null
...

What I actually want is just 2 columns with
in the first IssueID and in the second Project
and no duplicate data. Everything (except null) is more
important then the same value in column 3.

100 | P002
101 | P003
102 | P002
103 | null

I've tried with except, but can't get all duplicate
data out.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 22:01:33

[code]

Select a.issueID,Coalesce(b.projectID,c.project) as ProjID
FROM issuerequest a
Left join projects b
on a.issueID=b.referenceID

left join (Select customerID,min(project) as Project
FROM productcustomer
Group by customerID) c
on a.Customer = c.CustomerID and a.Product = c.ProductID
and actuellproduct ='1'
[/code]

You can join to both Table 2 and Table 3 in one Select. in this case I subquery to do the min(project) and alias as 'c' to use the column.

The coalesce function will pick the first non-null value in the expression, so if b.ProjectId is null, it will go to c.Project

Maybe that will help?

Poor planning on your part does not constitute an emergency on my part.

hanavan
Starting Member

25 Posts

Posted - 2008-03-12 : 04:34:46

Thanks for your help,
couldn't have solved it without. Never heard of the
coalesce function.

I didn't have an issueID in the productcustomer table,
so couldn't use that but with adding customer and product it
was solved.

Select a.issueID,Coalesce(b.projectID,c.project) as ProjID
FROM issuerequest a
Left join projects b
on a.issueID=b.referenceID

left join (Select min(project) as Project, customerID, productID
FROM productcustomer
Group by customerID, productID
where actuellproduct ='1') c
on a.Customer = c.CustomerID and a.Product = c.ProductID

select a.anfrageID, Coalesce(min(b.projektnummer),min(c.projekt)) as ProjectID
From mphotlineanfrage as a
left join mpprojektbuchungserfassung as b
on (a.anfrageID= b.quellreferenz)

left join (select min(projekt) as projekt, adresse, produktID
from mphotlineproduktkunde
where produktaktuell='-1'
group by adresse, produktID)as c
on (a.adresse = c.adresse) and (a.produktID = c.produktID)
group by anfrageID
order by anfrageID

hanavan
Starting Member

25 Posts

Posted - 2008-03-12 : 05:13:29

double post

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources