11 Replies - 351 Views - Last Post: 11 July 2014 - 09:20 AM Rate Topic: -----

#1 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Joining 3 tables

Posted 11 July 2014 - 07:43 AM

Hi all,

I have 3 tables like below:

Table 1
ID    Name    City
1     Peter     NewYork
2     Tim       Chicago
3     John      Michigan
4    Kevin      Washington



Table 2
ID    MilesWonThisMonth     MilesWonLastMonth      Table1ID
1     1000                            1010                             1
2     1100                            1110                             2
3     1109                            1210                             3
4     2180                            2010                             4
5     3100                            4010                             5



Table 3
ID    MilesStart     MilesEnd      Prize
1     1000            2000            Broze
2     2001            3000            Silver
3     3001            4000            Gold
4     4001            5000            Car




I expect results like below if suppose Peter is selected:
ID    MilesStart     MilesEnd      Prize
1     1000            2000            Broze



I am getting results till 2 tables level but not when I am trying for the 3rd one !

This is my query for 2 tables which is working:
select Table2.ID, Table2.MilesWonThisMonth, Table2.MilesWonLastMonth 
from Table1, Table2 
where Table1.Name = "Peter" AND Table1.id = Table2.Table1ID;



I am new to complex queries so little bit puzzled !

Any help will be greatly appreciated..

Thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Joining 3 tables

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9284
  • View blog
  • Posts: 34,797
  • Joined: 12-June 08

Re: Joining 3 tables

Posted 11 July 2014 - 07:47 AM

What is the relation to table3? I do not see a clear indicator of what you want to happen.
Was This Post Helpful? 0
  • +
  • -

#3 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 07:55 AM

@Moi123_1
The problem is with Table2 which can contain duplicated values for same name.
But Table3 has a Non redundant set of ranges and has the information that I want to show as final results.

The only relation I can explain with Table3 is the Range of values.
Which means once I get the range from Table2 then I want to go to the Non redundant range of Table3.
Which contains the prize or award for winning the number of Miles.
Was This Post Helpful? 0
  • +
  • -

#4 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 08:04 AM

I think its the 2nd part which is not working .

That means connecting Table2 -- > Table3 using range of values;

I was testing this query but it doesn't work:

select Table3.ID, Table3. MilesStart, Table3. MilesStop, Table3.Prize from Table2,Table3 WHERE Table2. Table1ID = "1" AND Table2. MilesWonThisMonth  BETWEEN Table3.MilesStart AND Table3.MilesStop;


Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9284
  • View blog
  • Posts: 34,797
  • Joined: 12-June 08

Re: Joining 3 tables

Posted 11 July 2014 - 08:08 AM

You should really think about formatting your query so folks can read it at a glance.. and also ditch the comma join and spell out 'join'.

A join isn't going to be the direct answer here. You'll need to think about making a custom column up to dispense the value from the third table. You should have gotten an inkling of that when you mentioned there isn't a clear key relation.
Was This Post Helpful? 0
  • +
  • -

#6 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 08:15 AM

creating custom column how ? @_@
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9284
  • View blog
  • Posts: 34,797
  • Joined: 12-June 08

Re: Joining 3 tables

Posted 11 July 2014 - 08:24 AM

One way is to hard code it using case statements.
select *
, case 
	when b.MilesWonThisMonth >= 1000 and b.MilesWonThisMonth <= 2000 then 'Broze'
	when b.MilesWonThisMonth >= 2001 and b.MilesWonThisMonth <= 3000 then 'Silver'
	when b.MilesWonThisMonth >= 3001 and b.MilesWonThisMonth <= 4000 then 'Gold'
	when b.MilesWonThisMonth >= 4001 and b.MilesWonThisMonth <= 5000 then 'Car'
end as medal
from #names  a 
join #Entries b
	on a.id = b.nameid


id          name                 city                 ID          nameId      MilesWonThisMonth MilesWonLastMonth medal
----------- -------------------- -------------------- ----------- ----------- ----------------- ----------------- ------
1           Peter                NewYork              1           1           1000              1010              Broze
2             Tim                Chicago              2           2           1100              1110              Broze
5             Foo                Bar                  5           5           3100              4010              Gold


Another option is to use a select (with a between) as a column.

Example:

select *
, (select Prize from #ranges where b.MilesWonThisMonth between MilesStart and MilesEnd) as nar
from #names  a 
join #Entries b
	on a.id = b.nameid


id          name                 city                 ID          nameId      MilesWonThisMonth MilesWonLastMonth nar
----------- -------------------- -------------------- ----------- ----------- ----------------- ----------------- --------------------
1           Peter                NewYork              1           1           1000              1010              Broze
2             Tim                Chicago              2           2           1100              1110              Broze
5             Foo                Bar                  5           5           3100              4010              Gold

Was This Post Helpful? 2
  • +
  • -

#8 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 08:32 AM

Thanks @modi123_1
I will work around this.
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9284
  • View blog
  • Posts: 34,797
  • Joined: 12-June 08

Re: Joining 3 tables

Posted 11 July 2014 - 08:43 AM

Work around? Those are two perfectly fine solutions.
Was This Post Helpful? 1
  • +
  • -

#10 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 09:01 AM

I am getting The error below:

ERROR 1242 (21000): Subquery returns more than 1 row


Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9284
  • View blog
  • Posts: 34,797
  • Joined: 12-June 08

Re: Joining 3 tables

Posted 11 July 2014 - 09:04 AM

It's a little difficult to know the error if you haven't posted the code too.
Was This Post Helpful? 1
  • +
  • -

#12 Dnamonk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 15-June 14

Re: Joining 3 tables

Posted 11 July 2014 - 09:20 AM

It works! Thanks @modi123_1
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1