beginner query ...i'm lost !

Question

beginner query ...i'm lost !

christophe.bernard-680841

Say Hey Kid

Points: 700
More actions
January 22, 2009 at 3:24 am

#127483

Hi,
I work with sql server 2005 and i'm a beginner with t sql !
since yesturday i try do write a query but i can't argh !!!!
I have 2 table :
table 1 : id, numero (int), version (int), history (int)
table 2 : id, numero (int), statut (int)
i want to GET all information from the table 1 with a select max version and a statut = 9
but if information has statut = 3 i want to get version - 1 because the line can have the statut 9 before !
here is it my query but of course i still block for to search how get the version - 1 !
SELECT distinct dp.descriptionPosteId, dp.numero, dp.version, dp.desPosGen
, dpf.DescriptionPosteNumero as fluxNumero, dpf.statut
FROM table1 dp
INNER JOIN table2 dpf on dp.numero = dpf.descriptionPosteNumero
--filtre
WHERE dp.DescPostType = 0
AND dp.version = (SELECT MAX(version) FROM table1 dp2 WHERE dp2.HistoryParent = dp.HistoryParent)
AND (
(dpf.statut = 9)
or
(dpf.statut = 3 and (????)
)
order by dp.descriptionPosteId
Thanks for your time
christophe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'm not sure I fully understand your requirements.

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

christophe.bernard-680841 Say Hey Kid Points: 700 More actions · Answer 2

Hi,

here is it the table definition:

table1 => description_poste

field => descriptionPosteId (pk), numero (int), version (int), desPosGen (int), history (int)

table description_poste_flux

field => descriptionPosteFluxId (pk), descriptionPosteNumero (int), statut (int)

I want to have all information from table description_poste BUT only the max version with statut = 9 !

all right !

if statut = 3 i must to execute a other query for to have the version - 1 because before to have statut 3 she has statut 9

i try but until now i block for to have statut 3 and doing version - 1

my code :

SELECT distinct dp.descriptionPosteId, dp.numero, dp.version, dp.desPosGen
, dpf.DescriptionPosteNumero as fluxNumero, dpf.statut
FROM DESCRIPTION_POSTE dp
INNER JOIN DESCRIPTION_POSTE_FLUX dpf on dp.numero = dpf.descriptionPosteNumero
--filtre
WHERE
dp.version = (SELECT MAX(version) FROM DESCRIPTION_POSTE dp2 WHERE dp2.HistoryParent = dp.HistoryParent)
AND (
(dpf.statut = 9)
or
(dpf.statut = 3 and dp.version = (SELECT MAX(version -1) FROM DESCRIPTION_POSTE dp3 where dp3.historyparent = dp.HistoryParent))
)
order by dp.descriptionPosteId desc

thanks for your comment

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The best way to post the table definitions is as create statements, so that the people who are trying to help you can easily create those tables to test out the queries.

Please provide sample data. The article I referred you to describes the best way. In essence, so it's easy to insert into the tables to test the queries.

Based on the sample data posted, please post the resultset that you want. Explanations are easy to misunderstand, data is not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Omoba SSCommitted Points: 1910 More actions · Answer 4

I'm not clear on your explanations either, but from the little i understand your problem is with the second part of your query (statut = 3)

based on that u might consider breaking up the query like this:

SELECT distinct dp.descriptionPosteId, dp.numero, dp.version, dp.desPosGen

, dpf.DescriptionPosteNumero as fluxNumero, dpf.statut

FROM DESCRIPTION_POSTE dp

INNER JOIN DESCRIPTION_POSTE_FLUX dpf on dp.numero = dpf.descriptionPosteNumero

--filtre

WHERE

dp.version = (SELECT MAX(version) FROM DESCRIPTION_POSTE dp2 WHERE dp2.HistoryParent = dp.HistoryParent)

AND (dpf.statut = 9)

order by dp.descriptionPosteId desc

SELECT distinct dp.descriptionPosteId, dp.numero, dp.version, dp.desPosGen

, dpf.DescriptionPosteNumero as fluxNumero, dpf.statut

FROM DESCRIPTION_POSTE dp

INNER JOIN DESCRIPTION_POSTE_FLUX dpf on dp.numero = dpf.descriptionPosteNumero

--filtre

WHERE

dp.version = (SELECT MAX(version -1) FROM DESCRIPTION_POSTE dp2 where dp2.historyparent = dp.HistoryParent)

and (dpf.statut = 3)

order by dp.descriptionPosteId desc

if that gives you what you expect, then linking them wouldn't be much of a problem, but like GilaMonster said, the create statements and data would go a long way in helping to figure out what you need to get done and how to go about it.

------------------------------------------------------------------------
All it takes, is a step in the right direction, your feet will manage to find the way. I didn't say it'll be easy!!![font="Comic Sans MS"]:cool:[/font]