Page 1 of 1

mysql - union query..want to be join

Posted: Tue May 29, 2007 8:34 am
by kbrown3074
feyd | 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 have the below sql statement as a union which isnt working correctly.  I think that a join will work but Im not exactly sure how to use it.
I have 2 id numbers that pull a person's name from a 2nd table..the name is pulled from the same field for both id numbers.
[syntax="sql"]
Select timeclock.id.name as monname, '' as opname, monlog.monid, monlog.opid,monlog.mondate, monlog.montime, monlog.score, monlog.deductions, monlog.comments, monlog.campaign from timeclock.id,monlog where timeclock.id.operator=monlog.monid and mondate='2007-05-29' and monlog.monid='b5766' and monlog.opid='b6734' UNION select '' as monname,timeclock.id.name as opname, monlog.monid, monlog.opid,monlog.mondate, monlog.montime, monlog.score, monlog.deductions, monlog.comments, monlog.campaign from timeclock.id,monlog where timeclock.id.operator=monlog.opid and mondate='2007-05-29' and monlog.monid='b5766' and monlog.opid='b6734' \G

feyd | Please use[/syntax]

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]

Posted: Tue May 29, 2007 10:50 am
by kbrown3074
Ok..I got a start of a join. The result set should only be 1 record, but it is returning 156 records. Would an inner join with an outer join inside get the desired results maybe?

Code: Select all

Select t1.name as monname,t2.name as opname,m1.monid, m1.opid,m1.mondate, m1.montime, m1.score, m1.deductions, m1.comments, m1.campaign from monlog AS m1,timeclock.id LEFT OUTER JOIN timeclock.id as t1 ON m1.monid=t1.operator LEFT OUTER JOIN timeclock.id as t2 ON m1.opid=t2.operator WHERE m1.mondate='2007-05-29' and m1.monid='b5766' and m1.opid='b6734';
feyd | It's "sql" not "SQL" ;)

Posted: Tue May 29, 2007 5:42 pm
by RobertGonzalez
What are the tables, what fields are related and what do you want from each?

Example answer would be something like:
I want to select all rows from table_users that are in table_data where rel_id (table_users) relates to rel_id (table_data).

Posted: Wed Jun 06, 2007 12:52 pm
by kbrown3074
What I am looking to do is pull just the name from the timeclock.id table. I need to poll against it twice with 2 different fields in the monlog table.

select opid,monid,mondate,montime,score,deductions,comments from monlog table -> select name from id where monlog.opid=opid AND monlog.monid=opid

Code: Select all

SELECT t1.name AS monname,t2.name AS opname,m1.monid, m1.opid,m1.mondate, m1.montime, m1.score, m1.deductions, m1.comments, m1.campaign FROM monlog AS m1,timeclock.id LEFT OUTER JOIN timeclock.id AS t1 ON m1.monid=t1.operator LEFT OUTER JOIN timeclock.id AS t2 ON m1.opid=t2.operator WHERE m1.mondate='2007-05-29' AND m1.monid='b5766' AND m1.opid='b6734';[/syntax="sql"]

Posted: Wed Jun 06, 2007 3:15 pm
by RobertGonzalez
So in response to my last post, in plain english:
I want to select all name from the timeclock table

Is that right?

PS : Will this type of query not work?

Code: Select all

SELECT 
	t1.*, 
	t2.* 
FROM
	`table_name_first` t1 
	INNER JOIN `table_name_second` t2 ON 
	t1.related_field = t2.related_field 
WHERE t1.search_field_one = 'search_term'
#AND t2.search_field_two = 'search_term'
;