resultset data question 2

[noclist]

I'm working on a query and I need it to return 2 versions of the same column, so I'm trying to nest the 2nd version in to the first to return the results.

Here are my queries:

select modifications.timestamp, clients.name, remote_sites.name, modifications.name, oiversions.name
from remote_sites, modifications, clients, oiversions
where modifications.remote_site_id = remote_sites.id
and modifications.timestamp like '2007%' and remote_sites.client_id = 5
and clients.id = remote_sites.client_id and modifications.from_version = oiversions.id
order by modifications.timestamp, remote_sites.name asc

and

select oiversions.name
from remote_sites, modifications, clients, oiversions
where modifications.remote_site_id = remote_sites.id
and modifications.timestamp like '2007%' and remote_sites.client_id = 5
and clients.id = remote_sites.client_id and modifications.to_version = oiversions.id

the second of which returns a result i would like returned in the first.

So, I tried nesting the 2 queries like so:

select modifications.timestamp, clients.name, remote_sites.name, modifications.name, oiversions.name, (select oiversions.name
from remote_sites, modifications, clients, oiversions
where modifications.remote_site_id = remote_sites.id
and modifications.timestamp like '2007%' and remote_sites.client_id = 5
and clients.id = remote_sites.client_id and modifications.to_version = oiversions.id)
from remote_sites, modifications, clients, oiversions
where modifications.remote_site_id = remote_sites.id
and modifications.timestamp like '2007%' and remote_sites.client_id = 5
and clients.id = remote_sites.client_id and modifications.from_version = oiversions.id
order by modifications.timestamp, remote_sites.name asc

But I get the error "cannot retrieve resultset data"

Would anyone be able to tell me what I'm doing wrong? I've checked the syntax many times over and can't find a problem, unless my nesting isn't right. Thanks in advance.

 

[LKBrwnDBA]

1) It's aggravating to try and decipher the non-formatted spaguetti code you posted.

2) Try using a join of the two queries instead of the in-line query.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

You may try this:

SELECT M.timestamp, C.name, R.name, M.name, O1.name, O2.name
FROM remote_sites R
INNER JOIN modifications M ON R.id = M.remote_site_id
INNER JOIN clients C ON R.client_id = C.id
INNER JOIN oiversions O1 ON M.from_version = O1.id
INNER JOIN oiversions O2 ON M.to_version = O2.id
WHERE M.timestamp like '2007%' AND R.client_id = 5
ORDER BY M.timestamp, R.name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noclist]

Well, oiversions is only one table so having oiversions2.name wouldn't be giving me any data would it?
Is there a way I could use a UNION ALL to join the 2 queries?
I've tried with no success to simply add UNION ALL between the 2 to join them but it gives me a "incorrect usage of union and order by" error.

 

[PHV]

Did you try my suggestion ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noclist]

You're right, worked perfectly. Thanks so much. Do you think you could explain what is happening in that code to me? I'm having trouble following it.

 

[PHV]

I've simply used two instances of the oiversions, one for from_version and the other for to_version.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noclist]

got it, you've been a huge help. thanks again.

 

[noclist]

Hey one more question, how would I go about displaying results of a field that is blank? Specifically oiversions fields, currently any blank fields are omitted from the results.

 

[PHV]

You may consider a LEFT OUTER JOIN

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=4785

Look at left outer joins

"NOTHING is more important in a database than integrity." ESquared

 

[noclist]

Ok, now I'm in the process of displaying the query results via PHP. I'm having trouble with the conventions of of PHP as I'm fairly new to it. How do I construct the inner joins and left joins in a PHP document? Also, when displaying the results with an echo, how do I specify fields with identical names that come from different tables? It seems to only let me print one of each unique field ID. Thanks in advance.

 

[PHV]

Use aliases

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noclist]

Do you happen to have a further explanation of aliases? A search didn't bring up anything useful.

Here's what I have so far:
--------------------------------------------------------
$query = "SELECT modifications.timestamp, clients.name, remote_sites.name, modifications.name, oiversions.name, idgversions.name

FROM modifications, remote_sites, clients, oiversions, idgversions

WHERE modifications.timestamp LIKE '2007%'
AND remote_sites.client_id = clients.id
AND remote_sites.client_id = 5
AND remote_sites.id = modifications.remote_site_id
AND modifications.from_version = oiversions.id
AND modifications.from_version = idgversions.id";
--------------------------------------------------------

I have it displaying properly as well. the only thing I'm unsure of is how to make more than one instance with PHP of oiversion and now idgversion, for the to_versions of each of those tables. Any insight greatly appreciated.

 

[JarlH]

Column aliases:[tt]
SELECT t1.name AS t1_name,
t2.name AS t2_name,
etc...[/tt]

Used to rename columns in the select list!


Table aliases:[tt]
SELECT some columns
FROM verylongschemaname.verylongtablename1 AS t1 JOIN
verylongschemaname.verylongtablename2 AS t2
ON t1.col1 = t2.col2[/tt]

Typically used to simplify query writing when having long table names.

 

[noclist]

thanks so much.