Query doubt - 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

General SQL Server Forums

New to SQL Server Programming

Query doubt

Author

Topic

chechu
Starting Member

9 Posts

Posted - 2011-04-20 : 03:39:01

Hi there,

I need to modify the following query so it counts the number of passengers and type (Adult or Child). The WHERE clause shows the selected information if id=801904 (bookin), and the TipPas=0(adult) or TipPas=1(child)

I have tried it with AND, and OR clauses but it doesn't work properly, see below:

select
count( distinct (ISNULL(pas0.RPX_Nombre,'')+ ISNULL (pas0.RPX_Apellidos,''))) As Adultos,
count( distinct (ISNULL(pas1.RPX_Nombre,'')+ ISNULL (pas1.RPX_Apellidos,''))) As Niños
from Tbl_LineaReserva lin
INNER JOIN tbl_reservaPasajeroGenerico pas0 ON pas0.id_lre = lin.id_lre
INNER JOIN Tbl_ReservaPasajeroGenerico pas1 on pas1.id_lre = lin.id_lre
where
lin.Id_lre='801904' and pas0.RPX_TipPas='0' or pas1.RPX_TipPas='1'

Taking a look into the WHERE clause, if we use all AND, it shows '0' for all passengers if in the booking line there're not Child (TipPas=1), and if we put OR, it doesn't count them properly.

Any ideas how I can control this?

Thanks in advance,

chechu.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 03:43:24

select
count( distinct (ISNULL(pas0.RPX_Nombre,'')+ ISNULL (pas0.RPX_Apellidos,''))) As Adultos,
count( distinct (ISNULL(pas1.RPX_Nombre,'')+ ISNULL (pas1.RPX_Apellidos,''))) As Niños
from Tbl_LineaReserva lin
INNER JOIN tbl_reservaPasajeroGenerico pas0 ON pas0.id_lre = lin.id_lre
INNER JOIN Tbl_ReservaPasajeroGenerico pas1 on pas1.id_lre = lin.id_lre
where
lin.Id_lre='801904' and (pas0.RPX_TipPas='0' or pas1.RPX_TipPas='1')

--------------------------
http://connectsql.blogspot.com/

chechu
Starting Member

9 Posts

Posted - 2011-04-20 : 03:55:58

quote:
Originally posted by lionofdezert

select
count( distinct (ISNULL(pas0.RPX_Nombre,'')+ ISNULL (pas0.RPX_Apellidos,''))) As Adultos,
count( distinct (ISNULL(pas1.RPX_Nombre,'')+ ISNULL (pas1.RPX_Apellidos,''))) As Niños
from Tbl_LineaReserva lin
INNER JOIN tbl_reservaPasajeroGenerico pas0 ON pas0.id_lre = lin.id_lre
INNER JOIN Tbl_ReservaPasajeroGenerico pas1 on pas1.id_lre = lin.id_lre
where
lin.Id_lre='801904' and (pas0.RPX_TipPas='0' or pas1.RPX_TipPas='1')

--------------------------
http://connectsql.blogspot.com/

Hello,

I already tested this, and it shows me same number fo Adults and Childs, when It shouldn't :S

I tried to run separately those queries and they run well, the problem comes when I try to run it in one query that combines different TipPas and one of them is Null or Empty.

I Run this query for checking Adults:

select
count( distinct (ISNULL (pas0.RPX_Nombre,'')+ ISNULL (pas0.RPX_Apellidos,''))) As Adultos
from Tbl_LineaReserva lin
INNER JOIN tbl_reservapasajerogenerico pas0 ON pas0.id_lre=lin.id_lre
where lin.Id_lre=801904 and pas0.RPX_TipPas=0

Output is "3"

I Run this query for Children:

select
count( distinct (ISNULL(pas1.RPX_Nombre,'')+ISNULL(pas1.RPX_Apellidos,''))) As Niños
from Tbl_LineaReserva lin
INNER JOIN Tbl_ReservaPasajeroGenerico pas1 on pas1.ID_LRE = lin.id_lre
where lin.Id_lre=801904 and pas1.RPX_TipPas=1

Output is "0".

The query quoted is a merge of those 2, but it mess ups when counting properly Adults and Childrens.

Btw, thanks for your fast reply.

chechu.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources