Creating a recordset from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Creating a recordset from two tables

Post by thdrought »

Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I am trying to modify a PHP page that currently pulls data out of a mySQL DB table. I would like the recordset to actually pull data from two tables, but the two tables only have 1 field in common, the ticket_ID field. 

Here are the statement pulling data from the one table...

Code: Select all

$sql = sprintf("SELECT th.thread_id, th.thread_date, th.thread_subject, th.thread_cc, th.is_agent_message, a.address_address, pgu.name_first, pgu.name_last FROM (ticket t, thread th, address a, public_gui_users pgu) WHERE th.ticket_id = t.ticket_id AND th.ticket_id = %d AND a.address_id = th.thread_address_id AND pgu.public_user_id = a.public_user_id AND th.thread_type = 'email' ORDER BY th.thread_date ASC ", $ticket_id);
$res = $cerberus_db->query($sql);
The data I would like to pull from the second table and append to the above recordset as would be...

Code: Select all

("SELECT tt.thread_time_id, tt.thread_time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary, tt.thread_time_date_billed, tt.thread_time_created_date FROM (thread_time_tracking tt, public_gui_users pgu) WHERE tt.ticket_id = t.ticket_id AND tt.ticket_id = %d ORDER BY tt.time_date ASC", $ticket_id);
I am unsure how to do this as the fields are completely different. I guess I could call each seperate and just diplay each recordset individually, but would rather mix the two and display both results together by date.

Suggestions would be appreciated. Thanks.


Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

It is one heck of a query, but try this...

Code: Select all

<?php
$sql = "SELECT th.thread_id, th.thread_date, th.thread_subject, th.thread_cc, th.is_agent_message, 
	a.address_address, 
	pgu.name_first, pgu.name_last, 
	tt.thread_time_id, tt.thread_time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary, tt.thread_time_date_billed, tt.thread_time_created_date 
	FROM ticket t 
		INNER JOIN thread th ON th.ticket_id = t.ticket_id 
		INNER JOIN address a ON a.address_id = th.thread_address_id 
		INNER JOIN public_gui_users pgu ON pgu.public_user_id = a.public_user_id 
		INNER JOIN thread_time_tracking tt ON tt.ticket_id = t.ticket_id
	WHERE th.ticket_id = $ticket_id 
	AND tt.ticket_id = $ticket_id 
	AND th.thread_type = 'email' 
	ORDER BY th.thread_date ASC, tt.time_date ASC"; 
?>
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

You hacking the cerberus code? :) -- I had to do that for my company a few months back.
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

jamiel wrote:You hacking the cerberus code? :) -- I had to do that for my company a few months back.
YES! You have to hack the code. It has great potential, but, until that potential is finally realized... :?

I've made about 5 major changes to the 3.1 code, and have another few too do. Even found some errors that I've resolved on my site and I've posted on their forums. Waiting to see if they fix it too.
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

Everah wrote:It is one heck of a query, but try this...
First, thank you for helping. I tried the code above and entered 1 ticket number. This particular ticket has 8 records in the first "ticket reply" table and 3 records in the "ticket time tracking" table. When I ran the code, it combined both and gave me 24 records returned, instead of the 11 I was expecting.

What I was hoping to do was to have 11 records, and where the fields are not the same between the two tables, they would be blank. I haven't worked with SQL in quite a few years, but still couldn't think of how to accomplish this task.

The only thing I could come up with in theory was creating an array off the first tables recordset, then adding the new fields for the second table to the array, then running the second tables query to populate them. But, as confusing that is too me, I don't know whether it would work, or how to write something like that.

Any other suggestions or tricks ( or array code :D )
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You know, I think a Union query might work for you. Inner Join returns common results. Outer Join returns uncommon results. Union I believe returns all results from one and appends that return to the return of the other.
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

A 'Union' query was my first instinct too, and I tried it. It failed saying that the fields were different. After more research, I found Unions would only work if all the fields were the same.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

You can "cheat" with a union query:

Code: Select all

mysql> SELECT * FROM table1;
+--------+--------+--------+--------+
| field1 | field2 | field3 | field4 |
+--------+--------+--------+--------+
|      1 |      2 |      4 |      4 |
|      2 |      7 |      2 |      4 |
|      3 |      7 |      3 |      5 |
|      4 |      4 |      3 |      5 |
+--------+--------+--------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM table2;
+--------+---------+--------+
| fielda | fieldb  | fieldc |
+--------+---------+--------+
|      1 | Hello   | Hi     |
|      2 | Goodbye | Bye    |
|      3 | Cheers  | Thanks |
+--------+---------+--------+
3 rows in set (0.00 sec)

mysql> SELECT field1, field2, field3, field4, '', '', '' FROM table1
    -> UNION ALL
    -> SELECT '', '', '', '', fielda, fieldb, fieldc FROM table2;
+--------+--------+--------+--------+---+---------+--------+
| field1 | field2 | field3 | field4 |   |         |        |
+--------+--------+--------+--------+---+---------+--------+
| 1      | 2      | 4      | 4      |   |         |        |
| 2      | 7      | 2      | 4      |   |         |        |
| 3      | 7      | 3      | 5      |   |         |        |
| 4      | 4      | 3      | 5      |   |         |        |
|        |        |        |        | 1 | Hello   | Hi     |
|        |        |        |        | 2 | Goodbye | Bye    |
|        |        |        |        | 3 | Cheers  | Thanks |
+--------+--------+--------+--------+---+---------+--------+
7 rows in set (0.01 sec)
Notice that I've "padded" the selection fields in the queries so that the same number of fields appear in both queries. This allows you to make the union.

Don't know whether this is any use to you?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That's a neat little trick GM. Thanks for that.
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

GM wrote:You can "cheat" with a union query:
Well, that is a lot of use to me. I had to make a few mods to get it to work. It's just a starting point right now, and Ill need to clean it up before making it live. But, here's what I learned from this. for each field thats blank, I had to define it with ' ' as name_first in the first select statement. Otherwise, it didn't work.

Now though, the field in the second select statement is only as large as what was passed to it from the first. Meaning, each '' field, if I add 1 space within the quote, I get 1 character returned in the second select statement. 2 space = 2 characters and so on.

Is it possible to predefine the fields with a varchar(50) within the first select statement? This is a weird situation, I know, but, Im getting closer thanks to all your help.

Code: Select all

(SELECT tt.thread_time_id as thread_id, tt.ticket_id as ticket_id, tt.thread_time_date as time_date, tt.thread_time_hrs_spent, tt.thread_time_hrs_chargeable, tt.thread_time_hrs_billable, tt.thread_time_hrs_payable, tt.thread_time_working_agent_id, tt.thread_time_summary as thread_subject, tt.thread_time_date_billed, tt.thread_time_created_by_id, tt.thread_time_created_date, ' ' as thread_cc, '1' as is_agent_message, ' ' as address_address, ' ' as name_first, ' ' as name_last
FROM (ticket t, thread_time_tracking tt) 
WHERE tt.ticket_id = t.ticket_id AND tt.ticket_id = 19)
UNION ALL
(SELECT th.thread_id as thread_id, th.ticket_id as ticket_id, th.thread_date as time_date, '', '', '', '', '', th.thread_subject as thread_subject, '', '', '', th.thread_cc as thread_cc, th.is_agent_message as is_agent_message, a.address_address as address_address, pgu.name_first as name_first, pgu.name_last as name_last 
FROM (ticket t, thread th, address a, public_gui_users pgu)
WHERE th.ticket_id = t.ticket_id AND th.ticket_id = 19 AND a.address_id = th.thread_address_id AND pgu.public_user_id = a.public_user_id AND th.thread_type = 'email')
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

Well, I got it to work just fine now. Some of the fields that I was having problems with, see above, have been either eliminated or combined with other fields. Here's the results. Thanks everyone. :D

Code: Select all

(SELECT 
	't' as thread_type,
	tt.thread_time_id as thread_id, 
	tt.ticket_id as ticket_id, 
	tt.thread_time_created_date as time_date, 
	tt.thread_time_hrs_spent, 
	tt.thread_time_hrs_chargeable, 
	tt.thread_time_hrs_billable, 
	tt.thread_time_hrs_payable, 
	tt.thread_time_summary as thread_subject, 
	tt.thread_time_date_billed, 
	tt.thread_time_date, 
	'  ' as thread_cc, 
	'1' as is_agent_message, 
	u.user_email as address_address, 
	u.user_display_name as name_first 
FROM (
	ticket t, 
	thread_time_tracking tt,
	user u
	) 
WHERE 
	tt.ticket_id = t.ticket_id AND 
	tt.ticket_id = 22 AND
	u.user_id = tt.thread_time_working_agent_id
)
UNION ALL
(SELECT 
	'e' as thread_type,
	th.thread_id as thread_id, 
	th.ticket_id as ticket_id, 
	th.thread_date as time_date, 
	'', 
	'', 
	'', 
	'',
	th.thread_subject as thread_subject, 
	'', 
	'', 
	th.thread_cc as thread_cc, 
	th.is_agent_message as is_agent_message, 
	a.address_address as address_address, 
	CONCAT(pgu.name_first,' ',pgu.name_last) as name_first
FROM (
	ticket t, 
	thread th, 
	address a, 
	public_gui_users pgu
	)
WHERE 
	th.ticket_id = t.ticket_id AND 
	th.ticket_id = 22 AND 
	a.address_id = th.thread_address_id AND 
	pgu.public_user_id = a.public_user_id AND 
	th.thread_type = 'email'
)
ORDER BY time_date
Last edited by thdrought on Wed Jul 12, 2006 1:20 pm, edited 1 time in total.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Now that is a query. I'd be interested to see the execution time on that thing.
thdrought
Forum Newbie
Posts: 8
Joined: Tue Jul 11, 2006 10:04 am

Post by thdrought »

Everah wrote:Now that is a query. I'd be interested to see the execution time on that thing.
How do I get that info? I only have access to phpMyAdmin screen. Is there something I can run with the query to display it? Then I'll do a before and after.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If you run the query in phpMyAdmin it should return the execution time above the result set.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Also, if you execute "EXPLAIN SELECT ......" it will show you all the steps that the DBMS uses to resolve the query. It is very useful for highlighting areas where it would be useful to create new indexes.
Post Reply