p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   MySQL (http://p2p.wrox.com/forumdisplay.php?f=99)
-   -   joining 3 tables (http://p2p.wrox.com/showthread.php?t=18578)

koskalgr September 18th, 2004 05:08 AM

joining 3 tables
 
hello
i have these tables

properties
 id
 pname
 areaid
 type

areas
 areaid
 aname

prefectures
 preid
 pname

How can i select the rows from properties that are located in preid=3 (for example) and are of type '2' (again for example)

any help appreciated


gokul_blr September 20th, 2004 02:26 AM

Explain about your table relations... between 1 & 3 or 2 & 3



koskalgr September 20th, 2004 05:22 AM

sorry, i forgot a field in areas table !

properties table is related to areas table using the areaid field in properties [many] and areaid field in areas [one]

then, areas table is related to prefectures table using the preid field in areas [many] and preid field in prefectures [one]

the tables

properties
 id
 pname
 areaid
 type

areas
 areaid
 aname
 preid

prefectures
 preid
 pname



Anantsharma September 28th, 2004 02:55 AM

Select Pro.* from Properties Pro Inner Join Areas AR on
Pro.areaid=Ar.areaid Inner Join Preference Pre on
AR.Preid=Pre.Preid
Where Pre.Preid= 3 AND
Pro.Type= 2

I feel that u r having redundant Preid colum. In Area Table there is a preid. If so, u don't have to Join Third table Preference. The Query can be :-

Select Pro.* from Properties Pro Inner Join Areas AR on
Pro.areaid=Ar.areaid Where AR.Preid= 3 AND
Pro.Type= 2


Hope this helps

B. Anant

Anantsharma September 29th, 2004 11:32 PM

HI again,

I am usd to use T-SQL so I am sorry. Please avoid using INNER word to Join. just use JOIN and at the end of Query a semicolon is require with MySql.

B. Anant

ajmelo March 24th, 2006 09:52 AM

Hello all
I'm a novice in SQL and want to do something like this:

I have 3 tables with the fields above
<TRUKS>
   TRAILER_NO (char)
   DATE (date)
   STATE (CHAR)
<TESTS>
   TRAILER_NO (char)
   PDT30 (float)
   PDT60 (float)
   REJECTED (boolean)
   PROTECT (boolean)
<SUPLIER>
   TRAILER_NO (char)
   DT30 (float)
   DT60 (float)
   PROTECT (boolean)

so i want to join these 3 tables whit fields TRUKS.TRAILER_NO, TESTS.PDT30, TESTS.PDT60, SUPLIER.DT30, SUPLIER.DT60, who meet the criteria as follows:
1-TRUKS.TRAILER_NO=TESTS.TRAILER_NO OR
  TRUKS.TRAILER_NO=SUPLIER.TRAILER_NO
2-TRUKS.DATE>=any_date1 AND TRUKS.DATE<=any_date2
3-TRUKS.ESTADO="F"
4-TESTS.REJECTED=False AND TESTS.PROTECT=False
5-SUPLIER.PROTECT=False

an example
<TRUKS>
TRAILER_NO DATE STATE
   21 xx F
   22 xx F
   23 xx F
   24 xx F
<TESTS>
TRAILER_NO PDT30 PDT60 REJECTED PROTECT
   21 x0 x1 False False
   22 x2 x3 False False
   22 x4 x5 True False
   24 x6 x7 False False
<SUPLIER>
TRAILER_NO DT30 DT60 PROTECT
   21 y0 y1 False
   22 y2 y3 False
   23 y4 y5 False

so the result i want is
TRAILER_NO PDT30 PDT60 DT30 DT60
   21 x0 x1 y0 y1
   22 x2 x3 y2 y3
   23 y4 y5
   24 x6 x7

help appreciated
Thank you all



All times are GMT -4. The time now is 08:23 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.