May 7, 2012 at 8:51 pm
I'm pretty new to this, so sorry if this is a silly question.
I'm querying 2 tables and have joined a field in each. The join property is
Include ALL records from 'qryUC' and only those records from 'tblAAA
where the joined fields are equal.
However, what I really want, instead of equal is instr(field in qryUC, field in tblAAA) > 0.
May 7, 2012 at 8:56 pm
It will give more light if you post the query also.
May 7, 2012 at 9:31 pm
Sure, thanks. But it's still being designed (badly!)
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC INNER JOIN tblAAA ON qryUC.Serial = tblAAA.Prefix
WHERE (((qryUC.Lyrics) Like "*climb*"));
So it's where instr(qryUC.Serial,tblAAA.Prefix) > 0.
I'm not even sure if I should have the join.
May 7, 2012 at 11:26 pm
From what I understand.....I think you are looking for a Left Outer Join.
I recently saw this very good explanation on JOINS. I think you should check the following link...you might find it very helpful:
May 7, 2012 at 11:58 pm
Hi Vinu, I had a look but its a little too advanced for me.
Where has has, e.g.
Select *
From Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.Col1 = t2.Col2
What are the table names and field names? From what I know (and looking at the last line)
t1 and t2 are table names and Col1 and Col2 are fields names. Is that right?
So what is 'table1" and 'Table2" and the significance of the space after the name ?
Thanks.
May 8, 2012 at 12:31 am
kirkm 55368 (5/7/2012)
Hi Vinu, I had a look but its a little too advanced for me.Where has has, e.g.
Select *
From Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.Col1 = t2.Col2
What are the table names and field names? From what I know (and looking at the last line)
t1 and t2 are table names and Col1 and Col2 are fields names. Is that right?
So what is 'table1" and 'Table2" and the significance of the space after the name ?
Thanks.
The query above is simply returning all the records from table1 (whatever table that may actually be) and those records from table2 where the value in table1 Col1 equals the value in Table2 Col2. Where there is no matching value in table2, the query returns null values for the columns for Table2. The t1 and t2 are table aliases for Table1 and Table2.
Does this help you some? If anything is still unclear, please ask.
May 8, 2012 at 1:04 am
Do you mean?
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC INNER JOIN tblAAA ON qryUC.Serial > 0 OR tblAAA.Prefix > 0
WHERE (((qryUC.Lyrics) Like "*climb*"));
or
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC INNER JOIN tblAAA ON COALESCE(qryUC.Serial, tblAAA.Prefix) > 0
WHERE (((qryUC.Lyrics) Like "*climb*"));
Either would "work" as a query but I'm not sure which is correct based on the information given.
May 8, 2012 at 1:05 am
kirkm 55368 (5/7/2012)
Include ALL records from 'qryUC' and only those records from 'tblAAAwhere the joined fields are equal.
This is the definition of a left outer join which you are already discussing.
May 8, 2012 at 2:20 am
Slightly unsure what best to do, I'm well out of my depth here !
> This is the definition of a left outer join which you are already discussing.
That's not really what's wanted because the joined fields are not the same, one is Instr of the other.
SSCEight, Hard to say if either are (which isn't the whole thing, I hope to build up to that slowly as I figure it all out). My database engine must be different to yours as the first gives "Data type mismatch in criteria expression." and the second "Undefined function 'COALESCE' in expression."
Lynn, I follow most of that, but haven't heard of aliases. Are both Table1 and t1 names needed? Are the names somehow defined in the Table, and spaces not critical?
May 8, 2012 at 2:27 am
kirkm 55368 (5/8/2012)
That's not really what's wanted because the joined fields are not the same, one is Instr of the other.
I've had to look this up. Its been some time since I used vbscript. The equivalent of instr in sql is CHARINDEX.
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC q
INNER JOIN tblAAA a ON CHARINDEX(q.Serial, a.Prefix) > 0
WHERE q.Lyrics Like "%climb%";
I've never joined a table in this method but it should work but will likely be inefficient. Can you post the table definitions and let us know what you are trying to achieve and on what database platform?
May 8, 2012 at 3:52 am
kirkm 55368 (5/7/2012)
Hi Vinu, I had a look but its a little too advanced for me.Where has has, e.g.
Select *
From Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.Col1 = t2.Col2
What are the table names and field names? From what I know (and looking at the last line)
t1 and t2 are table names and Col1 and Col2 are fields names. Is that right?
So what is 'table1" and 'Table2" and the significance of the space after the name ?
Thanks.
Here, Table1 should be your table 'qryUC'[/b] from which you want all the records an Table2 is the other table 'tblAAA' from which you only want the recods where the Joined fields are equal.
't1' and 't2' are not table names they are Aliases of the tables Table1 and Table2. You are right about the Columns.
May 8, 2012 at 10:04 pm
Thank you, Vinu
>Can you post the table definitions and let us know what you are trying to achieve and on what database platform?
I'm not sure what table definitions to give. I'm using Microsoft Excel and Access. There's an ADO connection between them with the Tables in Access and the SQL generated in Excel. The recordset method is used to to populate a worksheet, which is the source for a multi column list box.
The query output/purpose is Recordings info, where the lyrics are such are such.
There's one final stage I'm stuck on. There's 3 tables involved and my question with 2 tables was to hopefully get that working and then apply the third.
I found CHARINDEX isn't used, similarly aliases (?). Instr is OK. I converted your example to
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC
INNER JOIN tblAAA ON INSTR(qryUC.Serial, tblAAA.Prefix) > 0
WHERE qryUC.Lyrics Like "%climb%"
And that works!! Thanks! It got me past a big hurdle and helped a lot. I think all I need for part one is
SELECT qryUC.Serial, qryUC.Title, qryUC.Artist, qryUC.Lyrics
FROM qryUC
WHERE qryUC.Lyrics Like "%climb%"
But for the next part, tblAAA, tblBBB, tblCCC all have a Prefix field (which is contained in qryUC.Serial)
I want to include data from some fields in those into the query output. (Is that possible?)
It's further complicated by tblBBB and tblCCC. They don't hold all the data, some is in another table, with both Prefix fields the same.
I wonder if this has helped ? I'm kind of on the edge here, know what I want but not how to get there or explain it too well !
May 8, 2012 at 11:23 pm
kirkm 55368 (5/8/2012)
But for the next part, tblAAA, tblBBB, tblCCC all have a Prefix field (which is contained in qryUC.Serial)I want to include data from some fields in those into the query output. (Is that possible?)
It's further complicated by tblBBB and tblCCC. They don't hold all the data, some is in another table, with both Prefix fields the same.
I wonder if this has helped ? I'm kind of on the edge here, know what I want but not how to get there or explain it too well !
Explaining your problem is not as hard as you think Kirk. This is how you do it:
1. Read the link in my Signature.
2. Post DDL of the Tables - TblAAA, TblBBB, TblCCC like explained in the link in my signature. This will help the people that read your thread know what kind of tables they are actually working with. By DDL, I mean the Create Table Script for the three tables.
3. Post some sample Data like explained in the link in my signature. Its very simple. This is so that the people reading your thread can have a look at what you are actually looking. This could give a rocket boost to the process of finding a solution to your Requirement.
4. And, finally you need to post the Expected Result Set that you intend to have from the Sample Data. That's even easier than a walk in the park. You just need to copy and paste the required results from the Sample Data in a Relational Format.
Try it and you'll see that the people on this Forum are so freakin' smart that they'll come up with umpteen no. of solutions for you in no time at all. 😉 😀
This is how you can Help us in Helping you. 🙂
May 8, 2012 at 11:58 pm
Hi Vinu
I will certainly do so, and update with better if I can.
Try it and you'll see that the people on this Forum are so freakin' smart that they'll come up with umpteen no. of solutions for you in no time at all. 😉 😀
That sounds beaut. Thanks for the pointers,
Cheers - Kirk
May 9, 2012 at 1:03 am
kirkm 55368 (5/8/2012)
Hi VinuI will certainly do so, and update with better if I can.
Try it and you'll see that the people on this Forum are so freakin' smart that they'll come up with umpteen no. of solutions for you in no time at all. 😉 😀
That sounds beaut. Thanks for the pointers,
Cheers - Kirk
You're Welcome.
CHEERS!!!!:-)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply