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
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]
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?
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';
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).
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
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"]