Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 21st, 2004, 05:50 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Mysql Sorting results from multiple tables

I'm really struggling to get my head around joins and was wondering if someone could point me in the right direction. I have 1 table that has a primary key and then maybe 2 or 3 other tables that have a foreign key.

The tables with the foreign key are identical (currently) and basically I want to do a select that gets all the data from the 3 foreign tables, does a WHERE to only get the records after the present time (from a field containing the date) and then does a sort on that time field so we can take the 5 records with entries nearest to the future.

So for example maybe it's like...

TABLE A
my_primary_key

TABLE B
my_foreign_key
my_date

TABLE C
my_foreign_key
my_date

TABLE D
my_foreign_key
my_date

I've looked at UNION which forces the data into one table but then I can't do the WHERE things I want to do. I tried Joins but I couldn't figure how to join the tables together. If anyone's a SQL buff and can help me out quickly I'd be most greatful,

cheers,

Dave
Reply With Quote
  #2 (permalink)  
Old October 21st, 2004, 06:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Dave,

It should be like this.
Code:
SELECT * 
FROM A, B, C, D 
WHERE A.PrimaryKey = B.ForiegnKey and A.PrimaryKey = C.ForiegnKey and 
    A.PrimaryKey = D.ForiegnKey and TABLE_THAT_HAS_DATEFIELD.DATEFIELDNAME = getdate()
    Not sure about your table structure, so you can specify the TableName.ColumnNames separated by COMMA, in place of * in the select statement. May be you got to make changes in the query to get that work the way you wanted.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #3 (permalink)  
Old October 22nd, 2004, 03:22 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply. I think that's almost there but the trouble is the date is stored in a field in each of the 3 foreign tables! I need to join the data kind of into one table and then do a sort on the "date column", hope that makes sense! Only getting future records in mysql is easy because I can then do [datecol] > NOW(). But it's just getting the data from all 3 of the foreign tables and then doing the sort.


thanks again,

Dave
Reply With Quote
  #4 (permalink)  
Old October 25th, 2004, 08:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Dave,

You can probably post the structure of those 3 tables involved and mention about the columns that are needed to be put together in a single table, and mention about the columns that are used to interlink the tables etc... That should give us an idea of what exactly you are trying to achieve. Else our suggestion would always go on assumptions and may never get closer to what you expect.

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #5 (permalink)  
Old October 25th, 2004, 08:30 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply...

Reply With Quote
  #6 (permalink)  
Old October 25th, 2004, 08:37 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply...

Ok imagine this set-up:

TABLE A
table_a_id
table_a_title

TABLE B
table_b_id
table_a_id
table_b_datetime

TABLE C
table_c_id
table_a_id
table_c_datetime

TABLE D
table_d_id
table_a_id
table_d_datetime

OK please don't ask me why anyone would design a table like this because I know it breaks all 3rd normal form. OK each data item has one row in Table A and a row in one of either B, C or D.

I need to find a way of creating a join so I can order the data in B, C, D so that it is in date order and only data in the future (using col datetime in B, C, D).

The output I'm looking for is:
table_a_id
table_a_title
date_time (from the row in B, C or D)

I've tried to get my head around this from SQL website but struggling. I think the UNION kind of works but I can't then do the order/where I need.

If anyone could help, much apreciated,

cheers,

Dave

Reply With Quote
  #7 (permalink)  
Old October 25th, 2004, 09:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Dave,

Not sure on what order you are expecting this to be.
Code:
(Select table_a_id, table_a_title, table_b_datetime as DateCol
from TABLE_A a, TABLE_B b where a.table_a_id = b.table_a_id order by DateCol)
UNION
(Select table_a_id, table_a_title, table_c_datetime
from TABLE_A a, TABLE_C c where a.table_a_id = c.table_a_id order by DateCol)
UNION
(Select table_a_id, table_a_title, table_d_datetime
from TABLE_A a, TABLE_D d where a.table_a_id = d.table_a_id order by DateCol)
Order by DateCol
Use the last order by in BLUE if required.

Hope this is what you are looking for.
Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #8 (permalink)  
Old October 25th, 2004, 10:33 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Vijay you're a star. Had a play around with the SQL and it's doing exactly what I wanted. I feel like I'm going to be using similar SQL quite a lot in the future so much apreciated.

thanks again,

Dave

:)

Reply With Quote
  #9 (permalink)  
Old October 25th, 2004, 10:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Dave,

Glad that you are happy with it. Actually, there were discussions about using UNION and ORDER BY in this forum/SQL server forum. Had you searched for it before posting it here, you would have solved it on your own. But, without having the table structure it wouldn't have been possible. So it is always easy to understand things in such cases, if the information is available as **much** as possible.

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Listing results from joined tables henry-horse Classic ASP Basics 5 March 9th, 2007 07:08 AM
sorting tables enhancement edwards_ BOOK: Professional JavaScript for Web Developers ISBN: 978-0-7645-7908-0 8 August 9th, 2006 03:08 PM
Sorting Tables and Safari HomersGhost BOOK: Professional JavaScript for Web Developers ISBN: 978-0-7645-7908-0 4 March 18th, 2006 07:09 PM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Display results from multiple tables Librarian Classic ASP Databases 6 July 6th, 2004 11:28 PM



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


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