PDA

View Full Version : ###Serious query problem###



ShMiL
08-16-2002, 09:15 PM
look at those two tables:
http://www.arava.co.il/~shmil/question.gif

I have been trying to solve this problem for months but no luck.
I need to query this DB so that it would contain this:
1. Select date from "tbl1" and all fields from "tbl2" which are connected to tbl2 ON tbl1.id2join=tbl2.id and where the field "word" = to 'Jamey Foxx' and order it by date.
2. Select all fields from "tbl2" where the field "word" = to 'Jamey Foxx' and order by length.
3. UNION 1 and 2 (above) into one recordset.

I tried and tried and no luck...
can someone please help with this query?
Thanks,
ShMiL

Roy Sinclair
08-16-2002, 10:02 PM
select
t1.date
,t2.*
from tbl1 t1
join tbl2 t2 on t1.id2join = t2.id
where t1.word = 'Jamey Foxx'
order by date
union
select
null
,*
from tbl2
where word = 'Jamey Foxx'
order by [length]


This should do it though in practice I would select each field individually instead of using the *.

ShMiL
08-16-2002, 11:49 PM
I get Syntax error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
This is my query, exactly as you wrote:

select t1.date,t2.* from tbl1 t1 join tbl2 t2 on t1.id2join = t2.id where t1.word = 'Jamey Foxx' order by date union select null,* from tbl2 where word = 'Jamey Foxx' order by [length]

irfan_haider
08-17-2002, 06:03 AM
Hello ShMiL
Try this if you still have problem
select t1.date , t2.*
from table tbl1 t1, tbl2 t2
where t1.word ='Jamey Foxx'
order by t1.date, t1.length;

ShMiL
08-17-2002, 11:09 AM
select t1.date , t2.*
from table tbl1 t1, tbl2 t2
where t1.word ='Jamey Foxx'
order by t1.date, t1.length;

Is no good because it won't select any records where tbl2.word = 'Jamey Foxx' Only the ones from tbl1 will be chosen.

but thanks anyways.

whammy
08-18-2002, 03:59 AM
SELECT t1.date,t2.* FROM tbl1 t1 JOIN tbl2 t2 ON(t1.id2join = t2.id) WHERE t1.word = 'Jamey Foxx' ORDER BY t1.date, t2.length DESC

I don't see why that wouldn't work, but I can't test it out right now.

It also makes it easier to type the pertinent things in the SQL statement in caps, at least for me so I can see that stuff at a glance.

P.S. With that SQL statement you are getting one recordset, that's the purpose of the join.

ShMiL
08-18-2002, 08:08 AM
Still not working, the same 'error in FROM clause'...
PLEASE
HELP!

whammy
08-19-2002, 01:14 AM
Do this:



SELECTQUERY = "SELECT t1.date,t2.* FROM tbl1 t1 JOIN tbl2 t2 ON(t1.id2join = t2.id) WHERE t1.word = 'Jamey Foxx' ORDER BY t1.date, t2.length DESC"
Response.Write(SELECTQUERY) : Response.End


That's usually the best way to debug a SQL statement, to see what's really going on (besides using SQL Query Analyzer, that is).

irfan_haider
08-19-2002, 09:37 AM
select t1.date , t2.*
from table tbl1 t1, tbl2 t2
where t1.id2join = t2.id
and t1.word ='Jamey Foxx'
order by t1.date, t1.length;


Yes My Fault I didnt join the tables. I hope above will work.

ShMiL
08-19-2002, 10:47 AM
I seem to get the same error for all of the query you wrote.
Could you think of a reason it would work for you and not work for me?
Can someone please make a little example so I can test it here, at my machine?

Thanks alot to you all!

allida77
08-19-2002, 03:18 PM
Just for reference:

http://www.indus-soft.com/winsql/

Is an excellent SQL building & debugging tool.




SQL = "SELECT " & _
"t1.date, " & _
"t2.* " & _
"FROM " & _
"tbl1 t1 " & _
"INNER JOIN tbl2 t2 " & _
"ON(t1.id = t2.id2join) " & _
"WHERE " & _
"t1.word = 'Jamey Foxx' "


That is my attempt. I did not test this but let me know if there is an error message.

ShMiL
08-19-2002, 06:02 PM
I tried this:


select tbl1.datr,tbl2.* from tbl1 inner join tbl2 on tbl1.id2join = tbl2.id where tbl1.word = 'Jamey Foxx' order by tbl1.datr UNION SELECT 0,tbl2.* FROM tbl2 INNER JOIN tbl1 ON tbl2.id=tbl1.id2join WHERE tbl2.word = 'Jamey Foxx' and tbl1.datr=0 order by [length]
(I guess [date] as a reserved word caused the problems)
But now I have a bigger problem, I get an error that says the UNION can't be used with MEMO fields.
And I have to use MEMO fields...
What should I do?

Thanks.

whammy
08-20-2002, 04:18 AM
I think the one I used should work without UNION, did you try changing date to something else?

I still didn't test it though since I don't have access at home :-/

ShMiL
08-20-2002, 11:22 AM
Whammy:
What about condition number 2?
I need tbl2.word to be = to 'Jamey Foxx' too!

raf
08-20-2002, 12:45 PM
about this last question :

why don't you use an "or" statement to combine the conditions ?

(their's also a small error in your request : the result of meeting one of the two conditions will result in data from the same variables, so what you defined in your orriginal post for 1 and 2 isn't possible : tbl1.date will also be selected for the record that meet condition 2)

i'll try to set 2 simmilar tables up to check a working sql statement (if I can find the time)

ShMiL
08-20-2002, 12:52 PM
I do want to combine, to union, but I had a problem with MEMO fields when using UNION.
Whammy said that his statement should work, so I wrote that it's not what I need...
I still can't get this solved. The UNION won't work with MEMO and I have to use memo's.

Anyone????

Thanks

raf
08-20-2002, 02:12 PM
joining memo's is impossible (as far as i looked it up)
buth why do you need memo's ? can't you use 'text' variables ?

anyway, if you need memo's, its probably best to create a "duplicate variable" in those tables, containing the same data as the tbl2.id and the tbl1.id2join variables, and define them as number or text variables. you could then use these duplicate-variables for this select query.


I set up two similar tables + an asp-page and tested the following sql-statement. it worked (on text and number variables)

sql="SELECT Tbl1.date, tbl2.*, Tbl1.word, Tbl1.date FROM Tbl1 INNER JOIN tbl2 ON Tbl1.id2join=tbl2.id WHERE Tbl1.word='Jamey Foxx' Or tbl2.word='Jamey Foxx' ORDER BY Tbl1.date asc"

allida77
08-20-2002, 02:59 PM
You do not want to have duplicate data in you db. Here it what I found about Unions and Memo fields


The UNION query has the limitation of not being able to handle Memo fields or strings longer than 255 characters in either MS Access 97 or 2000. Thus even if you have queries where to Text data types are concatenated, it may happen that the result is longer than 255 characters and a strange error will be produced.

You are going to have to do this without the UNION. I do not see anything with Whammy's SQL ecxept add this to compare

"AND t2.word = 'Jamey Foxx'"
the word field in table 2

ShMiL
08-21-2002, 07:21 PM
do you mean this:

SELECT tbl1.datr,tbl2.* FROM tbl1 INNER JOIN tbl2 ON(tbl1.id2join = tbl2.id) WHERE tbl1.word = 'Jamey Foxx' and tbl2.word = 'Jamey Foxx' ORDER BY tbl1.datr, tbl2.length DESC

This returns no records in the RS.

whammy
08-21-2002, 07:31 PM
Then it's working if you're not getting an error; there's just no records that match that criteria...

ShMiL
08-21-2002, 08:34 PM
but it will still return an empty RS even if I had 'Michael Jay Foxx' on tbl2.
What I want is to include the joined values id there is 'Michael Jay Foxx' on tbl1. AND, with NO join to tbl1, to get the records which have 'Michael Jay Foxx' on tbl2.word (with no connection at all to tbl1).
PLEASE HELP!

Roy Sinclair
08-21-2002, 11:03 PM
Have you tried creating a VIEW which has the combined tables you are looking for and then select against the view? That might get you around the UNION vs MEMO problem.

ShMiL
08-22-2002, 12:52 AM
I don't understand what you're suggesting. What is a VIEW?
thanks.

Roy Sinclair
08-22-2002, 03:58 AM
Originally posted by ShMiL
I don't understand what you're suggesting. What is a VIEW?
thanks.

Apparently it's something that Access doesn't support, in a good database product it's a great way to make a virtual table that contains selected fields from one or more other tables.

ShMiL
08-22-2002, 11:10 AM
So what can I do????
PLEASE HELP!

Roy Sinclair
08-23-2002, 09:54 PM
Originally posted by ShMiL
So what can I do????
PLEASE HELP!

Submit two queries, get two recordsets in a single recordset object and use the NextRecordset method of the recordset object to switch to the second recordset when the first reaches EOF.

ShMiL
08-23-2002, 10:20 PM
I really would rather not to query twice, but if you say it's the only way, I guess it is...
Can you please explain a bit more about your suggestion?
Thanks alot to the folks who answered in this thread!
:) :) :) :)

Roy Sinclair
08-23-2002, 11:13 PM
Originally posted by ShMiL
I really would rather not to query twice, but if you say it's the only way, I guess it is...
Can you please explain a bit more about your suggestion?
Thanks alot to the folks who answered in this thread!
:) :) :) :)

Take the first suggestion I gave and simply remove the word UNION from between the two select statements. Pass them both as a single SQL text to Access and you should end up getting a recordset object with two recordsets in it.