p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Mysql Sorting results from multiple tables (http://p2p.wrox.com/showthread.php?t=20234)

dcb22 October 21st, 2004 05:50 PM

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

happygv October 21st, 2004 06:15 PM

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

dcb22 October 22nd, 2004 03:22 AM

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

happygv October 25th, 2004 08:16 AM

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

dcb22 October 25th, 2004 08:30 AM

Thanks for the reply...


dcb22 October 25th, 2004 08:37 AM

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


happygv October 25th, 2004 09:08 AM

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

dcb22 October 25th, 2004 10:33 AM

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

:)


happygv October 25th, 2004 10:57 AM

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


All times are GMT -4. The time now is 02:44 PM.

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