SQL Questions, Selecting data from two tables ordered by date

[phaxmohdem]

I've been at this so long my brain is starting to leave me. I've been having a heck of a time wrapping my head around this seemingly simple SQL problem. (MySQL)

I need to select the following data from to tables:

table1 ==> c_id(unique, PK), b_id, title, text, picture_info, start_date, end_date, added_date

table2 ==> n_id(unique, PK), b_id, sometext, posted_date

Each table basically describes a different object (say apples and oranges) I'm trying to combine data from both tables into one result set WHERE b_id equals a value passed to the query from a PHP script, and order it by date (descending) (added_date & posted date)

So I need a line of apples and oranges arranged by date. Seems straight-forward, enough, I"m just not having any luck with joins right now.

 

[ForumMaster]

i'm assuming that b_id is equal? do you want an inner join or outer?

general syntax would be:

select a.c_id,a.b_id,a.title,a.text,a.picture_info,a.start_date,
a.end_date,a.added_date,
b.n_id,b.some_text,b.posted_date from table1 a,table2 b
where a.b_id = b.b_id and a.b_id = 123
order by a.added_date desc,b.posted_date

now say you wanted an outer join:

select a.c_id,a.b_id,a.title,a.text,a.picture_info,a.start_date,
a.end_date,a.added_date,
b.n_id,b.some_text,b.posted_date from table1 a,table2 b
where a.b_id = b.b_id (+) and a.b_id = 123
order by a.added_date desc,b.posted_date

should be start_date. for some reason it writes star t_date. don't know why.

 

[DT4K]

Is there any actual relationship between the two tables?
Do either or both tables have more than one record with the same b_id?
Maybe I'm confused, but if there are multiple records in each table with the same b_id, then you are going to get a lot more records than you're looking for. Each record in table 1 is going to be joined with every record in table 2 that has the same b_id you are querying for.

Are you sure you want a join and not a union of some sort? Doesn't seem to make sense unless there is actually a relationship that connects one "apple" with a particular "orange".

Also, if you are joining the tables, each record will have an "added date" and a "posted date". How will you determine which one to sort by?

 

[Ka0t1x]

It sounds like a job for a union rather than a join... seeing as the data is completely separate.

Join: Apples WITH Oranges (data will be merged together)
Union: Apples AND Oranges (data is separate)

 

[DT4K]

For the union, you'd just have to add some null values to the second half of your query to make everything match up with the first part (assuming you actually need to get data that is in the first table and not in the second).
With a union, the sorting wouldn't be an issue either because posted_date would be going in the same column as added_date.

Seems like something like this might work.

select c_id, b_id, title, text, picture_info, start_date, end_date, added_date
from table1
union
select n_id, b_id, null, sometext, null, null, null, posted_date
order by added_date desc

 

[phaxmohdem]

Thank you all so much. Just got it working using the UNION suggestion. I actually tried a union as my first attempt, but it threw an error about having an uneven number of fields between the two tables. I had no idea about that nifty 'null' filler trick.

Can't thank you all enough.