need Help in select query
-
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
-
Could you post the data contained in tables Test1, Test2, Test3 that will give you the output you have posted above?
-
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
-
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
-
Try replacing "join" by "left join" in Phil's query - maybe that's what you want?
-
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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...
-
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. -
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
4 event4
4 event4
4 event4
4 event4
4 event4
8 event12
8 event12
8 event12
8 event12
8 event12
1 event15
1 event15
1 event15
1 event15
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
-
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
-
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
-
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. -
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. -
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply