combine queries

[splitzer]

Hi,
I've trouble combining queries. I'm not trying to join the tables, but get results based on WHERE statement.

Query one:
<cfquery name="notes_get" datasource="#APPLICATION.ds#" cachedWithin="#CreateTimeSpan(0, 0, 2, 0)#">
SELECT *
FROM db_new.notes
WHERE myID = #ARGUMENTS.myID#
</cfquery>

Query two:
<cfquery name="favorite_sites" datasource="#APPLICATION.ds#" cachedWithin="#CreateTimeSpan(0, 0, 2, 0)#">
SELECT *
FROM db_new.favorite_sites
WHERE myID = #ARGUMENTS.myID#
</cfquery>


What i've tried is:
<cfquery name="uniqueQuery" datasource="db" cachedWithin="#CreateTimeSpan(0, 0, 2, 0)#">
SELECT *
FROM db_new.favorite_sites, db_new.notes
WHERE db_new.notes.myID = 2 OR db_new.favorite_sites.myID = 2
</cfquery>

What it does it, shows join query. I dont want repeated data to combine it with other table. I want exactly as it was working as individual query.

Thanks for any help!

[Dan_Bracuk]

You might have some luck with a union query. But then again, you might not. I'm not sure what you are trying to accomplish.

[BKBK]

I am ignoring cachedWithin because it seems to be redundant in your code. I am also assuming that myID is a key, and so is unique. Then one can combine Query One and Query Two thus

<cfquery name="uniqueQuery" dbtype="query">
SELECT *
FROM favorite_sites, notes_get
</cfquery>

[splitzer]

quote:

---

Originally posted by: BKBK
I am ignoring cachedWithin because it seems to be redundant in your code. I am also assuming that myID is a key, and so is unique. Then one can combine Query One and Query Two thus

<cfquery name="uniqueQuery" dbtype="query">
SELECT *
FROM favorite_sites, notes_get
</cfquery>

---

I know this method, but what i'm really trying to do is filter the results too. Thats why i was trying WHERE db_new.notes.myID = 2 OR db_new.favorite_sites.myID = 2, but that didnt work, since its actually joining the queries instead of filtering it.

sorry, i couldnt find this thread.

Let me make couple of things clear.

-The column myID is not unique
-unique column is "id"
-since myID is not unique, it contains multiple values that i'm trying to filter. In this case, "2", so all the results containing 2 should come up instead of repeating it again and again through join query.

Why i'm trying to do this?:
Well, i have pages that are reaching around 13-15 queries. That is one page only, someone suggested that i should combine the queries as much as i can. But, for some reason i cant get it to work. So, if i can probably reduce it to 6-7 queries, it will reduce the execution time.

[efecto747]

If notes and favorite_sites have no relation then trying to combine them into one query doesn't really seem to have much advantage. You could use a UNION query as Dan suggested which will certainly give you something along the lines of what you're after but it you'd have to consider what performance gain you might expect from such a move - I'd imagine the differences in performance would be almost non-existent.

Are you sure your performance is suffering because of the queries? Have you turned on debugging and looked at the total execution time of all queries? I'd be looking at other aspects of the page generation before trying to combine queries - especially if the data isn't related.

[splitzer]

quote:

---

Originally posted by: efecto747
If notes and favorite_sites have no relation then trying to combine them into one query doesn't really seem to have much advantage. You could use a UNION query as Dan suggested which will certainly give you something along the lines of what you're after but it you'd have to consider what performance gain you might expect from such a move - I'd imagine the differences in performance would be almost non-existent.

Are you sure your performance is suffering because of the queries? Have you turned on debugging and looked at the total execution time of all queries? I'd be looking at other aspects of the page generation before trying to combine queries - especially if the data isn't related.

---

I have tried as Dan and you said, UNION query. But, thats not working either. The queries are not really affecting that much, but i have to use this method for my application to get things work. If i can get this to work, i'll be able to use it atleast 3-4 times at different places.

One of the reaons i'm trying to do this:
First query gets results, sorted by date, (table 1)
Second query, use the same order of results to get results from table 2, and output in the same order (table 2)
final output, show output sorted by date. Which was the first query results order.

So, i want everything to be sorted by the table1 results.

I think if i combine the queries into one, it would be much easier, but i cant seem to find a solution.

Take a look at this too:
http://i84.imagethrust.com/images/5lkk/view-image/table-1.html
http://i84.imagethrust.com/images/5lkl/view-image/table-2.html

I first compare profileID and myID, then compare the results with ID.

[BKBK]

Splitzer wrote:
I know this method, but what i'm really trying to do is filter the results too.

The method does filter the results by ID!

[BKBK]

Splitzer wrote:
I tried the query again, but the query shows repeated results. The data is being repeated upto 3-4 times. I think what its doing its actually joining similar columns.

I don't think so. I now think there is a simpler explanation. You are probably not using myID as a primary key, and have more than one row containing the same value. Looks like there is a mistake on my side, too. I should have specified the list of columns by name. It would, for example, avoid repeating the data in columns that are common to both queries.

Let's have another go. I am still assuming that myID is a primary key. Then the queries Notes_get and Favorite_sites would each return at most one row. The data in Notes_get's row, if any, would correspond to the data in Favorite_sites' row, if there is any. The following query will therefore return one row, at most. Use the actual column names in place of columnA1, columnB1, and so on.

<cfquery name="uniqueQuery" dbtype="query">
SELECT notes_get.myID, notes_get.columnA1, notes_get.columnA2, favorite_sites.columnB1, favorite_sites.columnB2
FROM notes_get, favorite_sites
</cfquery>

[Dan_Bracuk]

You say you want your results sorted but neither query has an order by clause.

[splitzer]

quote:

---

Originally posted by: Dan Bracuk
You say you want your results sorted but neither query has an order by clause.

---

Yea, i know. I was refering to this query:
<cfquery name="wall" datasource="#APPLICATION.ds#">
SELECT *
FROM db_new.wall, db.user_registration
WHERE profileID = #URL.id# OR myID = #URL.id# OR user_registration.id = #URL.id#
ORDER BY date DESC
</cfquery>

As you can see in the images i posted. I try to sort columns by date, but what it also does is that it repeats so many results by itself for some reason which i dont even have.

BKBK:
I tried the query again, but the query shows repeated results. The data is being repeated upto 3-4 times. I think what its doing its actually joining similar columns.
<cfquery name="uniqueQuery" dbtype="query">
SELECT *
FROM favorite_sites, notes_get
</cfquery>

[efecto747]

There's nothing in your query above that says how the two tables are related. When you query more than one table, you need to specify somewhere in the query how the tables are related either through a JOIN or, more simply, in the WHERE clause by specifying something like "WHERE tableA.fieldA = tableB.fieldB". This is probably one reason why you're not getting the results you expect.

There are also several other things I noticed with your queries which should be addressed: Avoid use of the asterisk * in your SELECT statement, specify each field you want to select. If they're from multiple tables, then prefix the field with the table name - this applies anywhere in the query that you use field names.

You also say you tried a UNION query but it that didn't work - can you post an example of the UNION query you tried? I still believe its possibly your best option.

cheers

[splitzer]

effecto747,
I tried what you mentioned, use WHERE tableA.fieldA = tableB.fieldB. But the problem is that the column "myID" is not unique, and it does have multiple results.

BKBK:
I did mention that colum "myID" is NOT unique. It does have multiple rows. I only have access to these tables now, here are the links:
http://i84.imagethrust.com/images/5lkk/view-image/table-1.html
http://i84.imagethrust.com/images/5lkl/view-image/table-2.html


Please, refer to these tables from now, as everyone can seeTable 1 column "id" IS unique and Table 2 column "id" is unique.

TABLE 2: So, i have to use WHERE statement in order to filter column "myID" = 2, plus sort the results by "date" ORDER BY date DESC. Those are the two requirements.

TABLE 1: Also, filter results from Table 1, id = 2.

I hope i made things clear now.

[efecto747]

Can you post a copy of the UNION query you tried? I still think this is your best option.

[BKBK]

Splitzer wrote:
unique column is "id" ... I did mention that colum "myID" is NOT unique.

OK. I combed back and found it.

You yourself have spelt it out in detail. Doesn't this do it?

<cfquery name="uniqueQuery" dbtype="query">
SELECT *
FROM notes_get, favorite_sites
/* filter results from Table 1, id = 2 */
WHERE notes_get.id = 2
/* TABLE 2: ... filter column "myID" = 2 */
AND favorite_sites.myID = 2
/* TABLE 2: ... plus sort the results by "date" ORDER BY date DESC */
ORDER BY favorite_sites.date DESC
</cfquery>

[splitzer]

Everyone on this forum was right, BKBK, effecto747, and Dan about the UNION and the query provided.

The final result: What i'm trying to do cant be done with a single query, so the problem now is the same problem. BKBK, you did made me realize that the query we were using is correct, but its not the result i've expecting since it must filter the results based on the previous query results.

Let me try layout the problem:
How can i keep the same order from the result of a query, than use these results in another query, but still maintain the results sorted by previous query by the date.

But, the problem cant be done in one query, since it has to use results before it can plug in data to second query.

I'm really tired working on this problem for 2 days now. I'll try to work on more code, and i'll post it here or in a new thread.

[CF_Oracle]

To maintain sort order of first query in a second one uyou can use loop.

[splitzer]

quote:

---

Originally posted by: CF_Oracle
To maintain sort order of first query in a second one uyou can use loop.

---

can you please give an example?

[CF_Oracle]

Query1
SELECT *
FROM Table1
WHERE ....
ORDER BY Column1

<cfloop query="Query 1">
Query2
.......
WHERE

Table2.Column2="#Query1.Column1#"
.....
</cfloop>

[splitzer]

I think this will work, but cfquery inside cfloop, wouldnt that cause any problems later on? I'll be having around max of 10 results. So, it would be repeating the query 10 times, do you think it will cause any problems?

[CF_Oracle]

We got sometimes 100 and more records from several joint tables within loop under 2 seconds.
In your case of just one table I would not worry.
Also, you can use components instantiated inside loop for the same results but would not expect much gain in performance.

[Newsgroup_User]

splitzer wrote:
> I think this will work, but cfquery inside cfloop, wouldnt that cause any
> problems later on? I'll be having around max of 10 results. So, it would be
> repeating the query 10 times, do you think it will cause any problems?
>

It is generally not a best preforming issue and can cause scalling
problem of this is expected to be a high traffic function, but sometimes
it is an very acceptable solution for a given requirement as long as its
limitations are understood.

[splitzer]

CF_Oracle, that did solve the problem. Thanks everyone for helping out!