need Help in select query

Question

Post reply

need Help in select query

Smruti-255597

Old Hand

Points: 301
More actions
September 5, 2005 at 12:22 am

#165874

Hi Friends
I have some problem in select query. Please help me
I have 3 tables
Test1(city,name) Two columns contain 10 records. No column is primary key
Test2(Rollno,city,joindt,standard) four columns containing 20 records of duplicate city,rollno
Test3(Rollno,no_of_Games,remarks) contain 15 records, NO column is set primary key.
I want to get the following result
city    Name            Participate
*************************
city1   Name1              5 games
city1   name2               10 games
city1   name3                0 game
city2   name4                0 games
city3    name5               4 games
city3     name6               5 games
i want to retrieve the name of all the students according to the city
I reminding that tables are not follow normalisation
So I get The problem.So please help me anyone
Than'x in Advance

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

Could you post the data contained in tables Test1, Test2, Test3 that will give you the output you have posted above?

Smruti-255597 Old Hand Points: 301 More actions · Answer 2

Than'x Jesper

I am Giving u the details

Test1

**********

City1 Name1

City2 name2

city1 Name3

city4 Name4

City2 Name6

City1 Name7

-------

Test2

*************

01 City1 10/08/05 xxx

02 City2 10/8/05 yyy

03 city1 11/8/05 xxx

04 City3 11/8/05 xxx

--------

Test3

***********

01 5 G

02 7 v

03 0 g

-----

Can u Understand now .

Than'x vary much

I want the deatils according to the city.

The city Who contain the name then that should be display

and Those city which has no record that also dispaly null value

Do u get me

Than'x

I am Trying on this query since for last three days

So pl help me

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

I'm having a bit of trouble working out what you want. Is it something like this?

select t1.city, t1.name, sum(no_of_games)

from test1 t1

join test2 t2 on t1.city = t2.city

join test3 t3 on t2.rollno = t3.rollno

group by t1.city, t1.name

Regards

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Smruti-255597 Old Hand Points: 301 More actions · Answer 4

Thanks Mr Phil Parkin

I am Very Thankful to u.

I want the report in which

I want to see all the city and belonging names.

Those city have the name and also those city Which have no names

Suppose

city1 has 3 names and city5 has no names

So I want that

CIty1 name1

City1 name2

city3 name3

City5 NULL

Thank's that u have given the query.

This query (still I have not tried) Will return All the names But not according to the city i.e it also display city5 three times

Than'x

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

Try replacing "join" by "left join" in Phil's query - maybe that's what you want?

Phil Parkin SSC Guru Points: 246991 More actions · Answer 6

I would have thought that the GROUP BY clause would ensure that city5 would not be displayed 3 times - ** please test and post the results along with the desired results.

As I do not join on test1.city, I can't see that using a left join is going to fix things.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 7

You are right Phil, my query won't work, but it can easily be fixed, I think.... On the other hand, I am not sure either what the desired output is, so I will just wait until that becomes clear...

David Burrows SSC Guru Points: 65144 More actions · Answer 8

I think it should be one of these two... maybe

select t1.city,

case when max(t3.no_of_games) is null then null else t1.[name] end as [name],

sum(isnull(t3.no_of_games,0)) as [Participate]

from Test1 t1

left outer join Test2 t2 on t2.city = t1.city

left outer join Test3 t3 on t3.rollno = t2.rollno

group by t1.city, t1.[name]

select t2.city,t1.[name], sum(t3.no_of_games) as [Participate]

from Test2 t2

inner join Test3 t3 on t3.rollno = t2.rollno

left outer join Test1 t1 on t1.city = t2.city

group by t2.city, t1.[name]

Far away is close at hand in the images of elsewhere.
Anon.

Smruti-255597 Old Hand Points: 301 More actions · Answer 9

Than'x Mr David And Mr Phil

Ur query is right but it does not give me my desired output. I here by sends the data and please give the sol. The tables are as follows

*************Test1(CityId,City)

2 city1

2 city2

2 city3

2 city4

3 city5

4 city6

4 city7

5 city8

6 city8

7 city8

7 city9

7 city10

***********Test2(EventId,Place,Events)

event1 city1 abc

event2 city1 abc

event3 city1 abc

event6 city4 abc

event7 city5 abc

event8 city4 abc

event9 city4 abc

event10 city4 abc

event11 city1 abc

event12 city1 abc

event13 city1 abc

event15 city15 abc

event16 city15 abc

event17 city15 abc

******************Test3(CityId,Eventid)

2 event1

2 event2

2 event3

5 event4

5 event6

5 event7

10 event8

10 event9

4 event4

8 event12

1 event15

My desired output is That I want all the City Form table Test1 and no_of_Events Form Table test2 According to the City

The Query u have given this gives the Result But the total no of events are more than the no of events in table test2

Sp pl Help me and I hope that this Data Gives u a better Idea

Thank U

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 10

Smruti, it's a bit hard to follow you now that you have changed the column names of your tables

You have stated 3 tables with data above, that's fine. Could you state the desired output of the query on the same form, instead of describing it in words? Then I am sure that someone can help you write the query

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

Agreed, this makes no sense and is very confusing.

Like the cities supplied as example:

2 city1

2 city2

2 city3

2 city4

Four cities with the same ID..?

IMO, what needs to be done before attempting any queries, is to clean, scrub and 'fix' these tables! You must have primary keys in place for these tables to give any sort of sensible results. By declaring and placing primary keys, you'll also get rid of duplicates in the process.

You won't be able to create any reliable SQL statements on tables in this shape.

/Kenneth

David Burrows SSC Guru Points: 65144 More actions · Answer 12

Agree also, the nearest I came up with is

select t1.City, count(*) as [no_of_events]

from (select distinct c.City from Test1 c) t1

inner join Test2 t2 on t2.Place = t1.City

group by t1.City

order by t1.City

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65144 More actions · Answer 13

or

select t1.City, count(t2.place) as [no_of_events]

from (select distinct c.City from Test1 c) t1

left outer join Test2 t2 on t2.Place = t1.City

group by t1.City

order by t1.City

if all the cities need to be shown

Far away is close at hand in the images of elsewhere.
Anon.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 14

Hi Guys,

If the City's are grouped (which is how I am reading this) say by Region - something like that. Wouldn't it be as easy to use two key columns e.g:

*************Test1(CityId, RegionID, City)

1 1 city1

2 1 city2

3 1 city3

4 1 city4

5 2 city5

6 3 city6

7 3 city7

8 4 city8

9 5 city8

10 6 city8

11 6 city9

12 6 city10

Just a thought. Makes more sense to me......

Have fun

Steve

We need men who can dream of things that never were.