Sql Query Help

[dotnetrockz]

can we use count/having clause  in a join condition inside sql query

 

 

for example
:

i have a table a

 

 

with 2 columns visitor id,parking_id

 

another table b with 2 columns parking_id,parking_type(ee table lo oka visitor_id ki upto 3 parking_id records untayi or may be more) so its table a--->table b(one to many relationship)

 

 

now i have to read data from table b which has more than 3 parking_id's for a single visitor id in table a

 

 

help

[cherlapalli_jailer]

help

[150bryant]

join two tables on the same key..

then retrieve all visitors and parking..

then group by visitors with count(parking_id)

then put having count >3

[Baadshah_Afdb]

join two tables on the same key..

then retrieve all visitors and parking..

then group by visitors with count(parking_id)

then put having count >3

agreed inner query akarledhu 

[150bryant]

nissan oka example ivu...with numbers..may be whole query rayochu..

 

[dotnetrockz]

join two tables on the same key..

then retrieve all visitors and parking..

then group by visitors with count(parking_id)

then put having count >3

Thanks this seems to be working(ippati varaki rasina query anni edo error vatshunde)..atleast this logic is retrieving some records

 

 

SELECT M.VISIT_ID

FROM VISITOR_T M

INNER JOIN PARK_AUDIT_T VA

ON M.PARK_ID=VA.PARK_ID

GROUP BY VISIT_ID

HAVING COUNT(VA.PARK_ID)>3

[Baadshah_Afdb]

Thanks this seems to be working(ippati varaki rasina query anni edo error vatshunde)..atleast this logic is retrieving some records

 

 

SELECT M.VISIT_ID

FROM VISITOR_T M

INNER JOIN PARK_AUDIT_T VA

ON M.PARK_ID=VA.PARK_ID

GROUP BY VISIT_ID

HAVING COUNT(VA.PARK_ID)>3

records display chesi chudu if results are working 

[dotnetrockz]

nissan oka example ivu...with numbers..may be whole query rayochu..

Kinda quer rasanu...

 

coming to numbers

 

table A 

 

 

Visitor_ID    PARK_ID

2500           100

2500            101

2500            102

 

 

 

 

Table B

 

 

PARK_ID PARK_TYPE

100           1

100           2

100           3

101            1

101           2

101           3

102            1

102           2

103           3

[Baadshah_Afdb]

Kinda quer rasanu...

 

coming to numbers

 

table A 

 

 

Visitor_ID    PARK_ID

2500           100

2500            101

2500            102

 

 

 

 

Table B

 

 

PARK_ID PARK_TYPE

100           1

100           2

100           3

101            1

101           2

101           3

102            1

102           2

103           3

i don't see any use of table b table a lone 2500 visitor ki 3 ids vunay kadha?

[dotnetrockz]

i don't see any use of table b table a lone 2500 visitor ki 3 ids vunay kadha?

hmm table b lo more than 3 records kosam search for that 2500

[pachimirchi]

declare @tbl_a table (visitorid int,park_id int)

declare @tbl_b table (park_id int, park_type int)

insert @tbl_b

select 100,1

union

select 100,2

union

select 100,3

union

select 101,1

union

select 101,2

union

select 101,3

union

select 102,1

union

select 102,2

union

select 103,3

 

insert @tbl_a

select 2500,100

union

select 2500,101

union

select 2500,102

 

 

select visitorid,park_id from @tbl_a

where park_id in(

select park_id from @tbl_b 

group by park_id having COUNT(*)>=3)

[Baadshah_Afdb]

hmm table b lo more than 3 records kosam search for that 2500

ok try this:

 

 

select a.visit_id 

from table a, (select count(b.park_id) as count1, b.park_id from table b group by park_id) b

where a.park_id = b.park_id and b.count1>3;

[dotnetrockz]

ok try this:

 

 

select a.visit_id 

from table a, (select count(b.park_id) as count1, b.park_id from table b group by park_id) b

where a.park_id = b.park_id and b.count1>3;

yeah this looks easier...i'll try

[dotnetrockz]

thanks everyone

[Suhaas]

ok try this:

 

 

select a.visit_id 

from table a, (select count(b.park_id) as count1, b.park_id from table b group by park_id) b

where a.park_id = b.park_id and b.count1>3;

 

replace them with just park_id