sql forum or help with sql query
I am trying to work on joining two tables. The part that has thrown me for a loop is that the two tables don't have one unique item that makes a row unique. It is accomplished by using several rows. Suppose I have tableOne and tableTwo. On a unique index tableOne has the columnns tableOne.colA tableOne.colB tableOne.colC and tableOne.colD
in tableTwo there is the following tables in it's unique index.
tableTwo.colA, tableTwo.colB and tableTwo.colC.
I have been trying something like the following :
Select tableTwo.ColA, tableTwo.Colx, tableTwo.colY from tableOne inner join tableTwo on tableOne.ColA=tableTwo.colA and tableOne.ColB=tabletwo.ColB and tableOne.ColC=tableTwo.colC where tableOne.colA='value'
Any suggestions or thoughts ?
thanks in advance for looking at it. If any clarification or further information is required please let me know.
[901 byte] By [
Aknibbsa] at [2007-11-27 6:38:37]
# 1
The question is how to join using two or more columns?select m.F1, n.F2from table1 m, table2 nwhere m.F1 = n.F1 and m.F3 = n.F4Table aliases (m and n) make the field names unique.
# 2
> The question is how to join using two or more
> columns?
>
> select m.F1, n.F2
> from table1 m, table2 n
> where m.F1 = n.F1 and m.F3 = n.F4
>
> Table aliases (m and n) make the field names unique.
Two parts to it the first part is yes joining on multiple columns, but the second part is also having those values match a third value.
Basically I want to get the rows out of tables A and B where A.F1 = B.F1 ='value'
where value is a parameter passed into the preparedStatement.
Please correct me if I am wrong but I would have thought that putting A.F1 = B.F1 would have uniquely identified each column as belonging to that particular table (the tables being compared are not identical).
# 3
> Two parts to it the first part is yes joining on multiple columns, but the > second part is also having those values match a third value.
> Basically I want to get the rows out of tables A and B where A.F1 = B.
> F1 ='value'
> where value is a parameter passed into the preparedStatement.
SELECT m.F1, nF2 FROM table1 m, table2 n
WHERE m.F1 = n.F1 AND m.F3 = n.F4 AND m.F1=?
or
SELECT m.F1, nF2
FROM table1 m JOIN table2 n ON m.F1 = n.F1 AND m.F3 = n.F4
WHERE m.F1=?
# 4
If that works great but if someone could explain why using the full name doesn't distinguish the two I would be greatly appreciative. Wouldn't using the table name along with the column name distinguish it from another table name and column name (I am assuming they are different tables. I could see the necessity of giving them each a name if they were the same table ).
# 5
huh? like this?SELECT table1.F1, table2.F3 FROM table1, table2WHERE table1.F1 = table2.F1of course it also works. using alias is optional. in case your table name is long enoght, it will help simplify your query.
# 6
> huh? like this?
> > SELECT table1.F1, table2.F3 FROM table1, table2
> WHERE table1.F1 = table2.F1
>
>
> of course it also works.
>
> using alias is optional. in case your table name is
> long enoght, it will help simplify your query.
Thanks for the input. No offence meant to you but I don't recognize the name, so I'm not sure of your knowledge in this area, that and the fact that jschell also had aliases in his post, so I'm going to ask the question again. Why would you have to use aliases on tables if you are only using each one once ?
# 7
> If that works great but if someone could explain why> using the full name doesn't distinguish the two I> would be greatly appreciative. It does. I just find that using the full name less readable.
# 8
> > If that works great but if someone could explain
> why
> > using the full name doesn't distinguish the two I
> > would be greatly appreciative.
>
> It does. I just find that using the full name less
> readable.
Thanks, much appreciated. Seems to be performing in a reasonable amount of time now.
