Hello,


In my site, I have a section called Jobs. In this section, I have a list of records (job posts) that registered clients have posted.

I can't figure out how to display the job postings with the coresponding record from another table.


Output:

date: 04/25/02
company: TD Canada Trust
position: Bank Manager

responsability: In charge of RSPs, Loans, etc..

qualifications: 2 years experience required.

tel: 555-555-5555
fax: 555-555-5555


date, position, responsability, qualifications belong to tbl_jobs.
company name, tel, fax belong to tbl_company.

It's very important that I figure this out so if anyone has any idea, please let me know.

Thank you very much.
Tony



___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Oh boy you must be new to UD4! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>


Edited by - akc on 29 Apr 2002 20:04:54

I'm not new to UD4, I'm new to the DB side of it! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> . I really need to figure this out. Do you have any ideas?

I don't want to place a loop in the repeated region because thats not efficient.


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Oh boy you must be new to UD4! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>


Edited by - akc on 29 Apr 2002 20:04:54
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Have you set up your recordset properly? I think you'd need the Advanced View for anything more than one table.

The SQL statement needs to be something like:
<pre id=code><font face=courier size=2 id=code>
SELECT tbl_jobs.field_name1, tbl_jobs.field_name2, tbl_company.field_name3, tbl_company.field_name4
FROM tbl_jobs, tbl_company
WHERE tbl_jobs.some_id = tbl_company.some_id
AND tbl_jobs.some_id = MMColParam
</font id=code></pre id=code>

The syntax is TableName.FieldName, particularly important if the two tables happen to have identical field names.

Another way is to JOIN the two tables.

That's what I'm not sure about. Can I use SQL within the repeated region or in the beginning? (where UD puts the recordsets)

I'm 100% lost. Anybody wanna take a look at my code? Pretty please! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>



<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Have you set up your recordset properly? I think you'd need the Advanced View for anything more than one table.

The SQL statement needs to be something like:
<pre id=code><font face=courier size=2 id=code>
SELECT tbl_jobs.field_name1, tbl_jobs.field_name2, tbl_company.field_name3, tbl_company.field_name4
FROM tbl_jobs, tbl_company
WHERE tbl_jobs.some_id = tbl_company.some_id
AND tbl_jobs.some_id = MMColParam
</font id=code></pre id=code>

The syntax is TableName.FieldName, particularly important if the two tables happen to have identical field names.

Another way is to JOIN the two tables.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Can I use SQL within the repeated region or in the beginning? (where UD puts the recordsets)
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Sure you can put SQL anywhere <img src=../images/dmxzone/forum/icon_smile_evil.gif border=0 align=middle>, but if you want it to work with UD4 and you don't like those red exclamation "oh-uh" marks, it's best to modify the SQL statement from within the data binding panel. The Repeat Region should not be relevant to filtering the recordset, which I believe is the key area that needs work. (But then I'm only guessing what your code looks like with my amazing powers of extrasensory perception.)

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Anybody wanna take a look at my code?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Why not.

What DB are you using Blood? Sometimes its best to do your SQL at the DB side, this would be a Query in Access (View in UD4).

The Query builder writes the SQL for you,...

:: Son, im Thirty.... ::

I'm using MS Access. I haven't tried editting the SQL string yet but to me, i don't think it will work. It might, but I don't think everyone understands what exactly I'm trying to do! heheh.. because it doesn't make sense to me cause I don't fully understand DB programming! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>


If I was to use this code:

<pre id=code><font face=courier size=2 id=code>
SELECT tbl_jobs.field_name1, tbl_jobs.field_name2, tbl_company.field_name3, tbl_company.field_name4FROM tbl_jobs, tbl_companyWHERE tbl_jobs.some_id = tbl_company.some_idAND tbl_jobs.some_id = MMColParam
</font id=code></pre id=code>

wouldn't it fetch all the records one time when the Page is loaded? If yes, then how will it display different values of different keys all together? (I think the problem is me! ehhee)

A guy from work is telling me that I have to put my recordset in the repeated region. Is that where I have to put this code? If so, how?



Sorry guys for not understanding! <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>



Thanks,
Tony

___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>


Edited by - bloodtrain on 30 Apr 2002 15:28:22

You could follow Leed's advice on trying your hand at setting up a View using the recordset panel. But it seems to me that you can't wait to try handcoding SQL! The fact is, if you are using UD's click-'n-drag approach, UD will insert its code in the right place (usually) -- and that includes the SQL statement. You just need to supply it with the SQL in the Advanced recordset panel. The exact SQL statement may take some experimentation at first (I think most of us started out doing that), and though some people (such as Leed) are very good at customizing code for others to the extent that you only have to change a thing or two, you may still need to tweak things a bit. Sometimes you don't know if something will absolutely work until it's been tried (and once it's worked, it's good to go back and figure out why). Sorry for the lecturing.

Example UD4 recordset code:

&lt;%
set rstAccepted = Server.CreateObject("ADODB.Recordset"
rstAccepted.ActiveConnection = MM_My_Amazing_STRING
rstAccepted.Source = "SELECT AbsID, Title, Filename, AbsStatus, PaperStatus, PFilename FROM Abstract WHERE AbsStatus = 3 ORDER BY Title ASC"
rstAccepted.CursorType = 0
rstAccepted.CursorLocation = 2
rstAccepted.LockType = 3
rstAccepted.Open()
rstAccepted_numRows = 0
%&gt;

But won't that just retrieve all records where absStatus = 3 ? That won't work for me because "Equal to" value is not always the same; it depends on who posted it.

Much like this message board - you can see everyones profile summary (from the little icons) from the poster id that must be stored in the "Messages" table.

Am I being silly? It's one of those moments when you think no one is understanding you but you're not sure as well! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Example UD4 recordset code:

&lt;%
set rstAccepted = Server.CreateObject("ADODB.Recordset"
rstAccepted.ActiveConnection = MM_My_Amazing_STRING
rstAccepted.Source = "SELECT AbsID, Title, Filename, AbsStatus, PaperStatus, PFilename FROM Abstract WHERE AbsStatus = 3 ORDER BY Title ASC"
rstAccepted.CursorType = 0
rstAccepted.CursorLocation = 2
rstAccepted.LockType = 3
rstAccepted.Open()
rstAccepted_numRows = 0
%&gt;
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Edited by - bloodtrain on 01 May 2002 19:00:13

I finally got it! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> (and the crowd cheers)

Thanks everyone for helping me!


Tony

___________________
Lunch is on me! <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>