Retrieiving another row in the same DB

[suklee]

I have this code working:

SELECT sv.FieldValue, s.SubmissionId
FROM SUBMISSION_VALUES sv, SUBMISSIONS s
WHERE s.SubmissionId = sv.SubmissionId AND s.FormId=2 AND sv.FieldName='First'

1. what do the letters sv and s mean after the table names?

2. I need to retrieve another value from the table SUBMISSION_VALUES. Specifically I need the FieldValue WHERE FieldName='Last'.

How do I add this to my SQL select statement?

 

[Hmongkeysauce]

1. It's an alias. So in your WHERE clause, instead of saying SUBMISSIONS.SubmissionId = SUBMISSION_VALUES.SubmissionId, you can use the alias sv and s instead
2. instead of AND sv.FieldName = 'First', do: AND sv.FieldName in ('First', 'Last')

 

[suklee]

Thanks for the explanation, I understand the alias now..

I now need to output the name as "Last, First"... but this code seems to output Last and First on their own lines. Any ideas?

while($r = mysql_fetch_assoc($rez))
$items .= "\n".$r['SubmissionId'] . '|' . $r['FieldValue'];

 

[KLin]

Originally posted by: Kai920
I have this code working:

SELECT sv.FieldValue, s.SubmissionId
FROM SUBMISSION_VALUES sv, SUBMISSIONS s
WHERE s.SubmissionId = sv.SubmissionId AND s.FormId=2 AND sv.FieldName='First'

1. what do the letters sv and s mean after the table names?

2. I need to retrieve another value from the table SUBMISSION_VALUES. Specifically I need the FieldValue WHERE FieldName='Last'.

How do I add this to my SQL select statement?

1. Just like Hmongkeysauce said, it's an alias. It allows you to use shorter names for tables in your SQL statement. Saves typing time.

2. use the In clause like he said too.

 

[KLin]

Originally posted by: Kai920
Thanks for the explanation, I understand the alias now..

I now need to output the name as "Last, First"... but this code seems to output Last and First on their own lines. Any ideas?

while($r = mysql_fetch_assoc($rez))
$items .= "\n".$r['SubmissionId'] . '|' . $r['FieldValue'];

Isn't \n a line feed?

 

[suklee]

yeah, sorry I'm fetching this into another script that builds the drop down list.

so what is the table structure like when I build it using the IN clause?

Do I end up having two rows?

First
Last

I need to return $items in this format:

ID#|Last, First
ID#|Last, First

etc.

 

[KLin]

Sounds like you need to look at the logic of how you're building the strings. If the first and last name values are separate records, you need to somehow get those values into variables IMO.

 

[BoberFett]

You can concat in SQL, just use:

table.LastName + ', ' + table.FirstName

 

[KLin]

Originally posted by: BoberFett
You can concat in SQL, just use:

table.LastName + ', ' + table.FirstName

From the sound of it, he doesn't have separate fields for first and last names. He just has fields called "fieldvalue" and "fieldname".

 

[suklee]

I managed to get some support from the script developer. basically it looks like he used an array to hold the values, and did two separate selections to populate the array..