Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
Hi Folks,
I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
On Jun 21, 12:15 pm, Stephen
wrote: Hi Folks, I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! What you may want is an OR. SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE (Wearer.GTypRcID01 = Garment.GTypRcID OR Wearer.GTypRcID02 = Garment.GTypRcID) AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) This could return 2 rows for each Wearer record. If you only want 1 row for each wearer record, try joining to 2 instances of Garment. You could do: SELECT DISTINCT Wearer.FrstName, Wearer.LastName, GA.GarmDesc,, GB.GarmDesc Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment GA, Garment GB, Wearer WHERE Wearer.GTypRcID01 = GA.GTypRcID AND Wearer.GTypRcID02 = GB.GTypRcID AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
I need to return both rows for each wearer record, but when I try running the
query with the OR (which I did try before posting)my query hangs and hangs until I have to end the task, that's what brought me to thinking about the joins. unfortnuately I'm not very proficient in SQL and creating a workable join is a little bit beyond me. " wrote: On Jun 21, 12:15 pm, Stephen wrote: Hi Folks, I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! What you may want is an OR. SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE (Wearer.GTypRcID01 = Garment.GTypRcID OR Wearer.GTypRcID02 = Garment.GTypRcID) AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) This could return 2 rows for each Wearer record. If you only want 1 row for each wearer record, try joining to 2 instances of Garment. You could do: SELECT DISTINCT Wearer.FrstName, Wearer.LastName, GA.GarmDesc,, GB.GarmDesc Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment GA, Garment GB, Wearer WHERE Wearer.GTypRcID01 = GA.GTypRcID AND Wearer.GTypRcID02 = GB.GTypRcID AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
Retraction...
It takes over 3 minutes to return the 32 records associated with this singe order number. there has to be a most efficient way of doing this! "Stephen" wrote: I need to return both rows for each wearer record, but when I try running the query with the OR (which I did try before posting)my query hangs and hangs until I have to end the task, that's what brought me to thinking about the joins. unfortnuately I'm not very proficient in SQL and creating a workable join is a little bit beyond me. " wrote: On Jun 21, 12:15 pm, Stephen wrote: Hi Folks, I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! What you may want is an OR. SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE (Wearer.GTypRcID01 = Garment.GTypRcID OR Wearer.GTypRcID02 = Garment.GTypRcID) AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) This could return 2 rows for each Wearer record. If you only want 1 row for each wearer record, try joining to 2 instances of Garment. You could do: SELECT DISTINCT Wearer.FrstName, Wearer.LastName, GA.GarmDesc,, GB.GarmDesc Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment GA, Garment GB, Wearer WHERE Wearer.GTypRcID01 = GA.GTypRcID AND Wearer.GTypRcID02 = GB.GTypRcID AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) Pete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
I found my answer!!! It turns out i was able to reference some unique values
in both tables in a simple expanded where clause that takes all my necessities ANd parameters into account without having to resort to any joins. And it runs lightning fast!!! My solution... SELECT DISTINCT Order.OrdrDesc, Wearer.FrstName, MWearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment Garment, Order Order, Wearer LMWearer WHERE Wearer.RcID = Garment.WearRcID AND Order.RcID = Wearer.OrdrRcID AND ((Wearer.OrdrRcID=?) AND (Order.RcID=?) AND (Wearer.Aloc01<0) AND (Wearer.ChrgQnty01<0) AND (Wearer.Nact=0)) Thanks for all the help!!! "Stephen" wrote: Retraction... It takes over 3 minutes to return the 32 records associated with this singe order number. there has to be a most efficient way of doing this! "Stephen" wrote: I need to return both rows for each wearer record, but when I try running the query with the OR (which I did try before posting)my query hangs and hangs until I have to end the task, that's what brought me to thinking about the joins. unfortnuately I'm not very proficient in SQL and creating a workable join is a little bit beyond me. " wrote: On Jun 21, 12:15 pm, Stephen wrote: Hi Folks, I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! What you may want is an OR. SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE (Wearer.GTypRcID01 = Garment.GTypRcID OR Wearer.GTypRcID02 = Garment.GTypRcID) AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) This could return 2 rows for each Wearer record. If you only want 1 row for each wearer record, try joining to 2 instances of Garment. You could do: SELECT DISTINCT Wearer.FrstName, Wearer.LastName, GA.GarmDesc,, GB.GarmDesc Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment GA, Garment GB, Wearer WHERE Wearer.GTypRcID01 = GA.GTypRcID AND Wearer.GTypRcID02 = GB.GTypRcID AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
!@#$ Query with Joins???
On Jun 21, 1:41 pm, Stephen wrote:
Retraction... It takes over 3 minutes to return the 32 records associated with this singe order number. there has to be a most efficient way of doing this! "Stephen" wrote: I need to return both rows for each wearer record, but when I try running the query with the OR (which I did try before posting)my query hangs and hangs until I have to end the task, that's what brought me to thinking about the joins. unfortnuately I'm not very proficient in SQL and creating a workable join is a little bit beyond me. " wrote: On Jun 21, 12:15 pm, Stephen wrote: Hi Folks, I'm trying to create a query that might require a join of somekind but after reading a number of web articles on joins I'm now more confused than ever... I have two tables that contain the data I need. The First table has two columns that relate to one column in my second table. I need to be able to pull about four or five values from my second table, and one value from my first table as it relates to the coresponding values from both. Since that probably makes little to no sense... SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE Wearer.GTypRcID01 = Garment.GTypRcID AND Wearer.GTypRcID02 = Garment.GTypRcID AND ((LMWearer.OrdrRcID=269) AND (LMWearer.Nact=0)) but when I do it this way I get no results. when i go with either Wearer.GTypRcID01 = Garment.GTypRcID or Wearer.GTypRcID02 = Garment.GTypRcID I get pants or shirts related to the wearer but I need to retrieve both. Please help before I split my head open from banging it against my keyboard :) Thanks to All!!! What you may want is an OR. SELECT DISTINCT Wearer.FrstName, Wearer.LastName, Garment.GarmDesc, Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment, Wearer WHERE (Wearer.GTypRcID01 = Garment.GTypRcID OR Wearer.GTypRcID02 = Garment.GTypRcID) AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) This could return 2 rows for each Wearer record. If you only want 1 row for each wearer record, try joining to 2 instances of Garment. You could do: SELECT DISTINCT Wearer.FrstName, Wearer.LastName, GA.GarmDesc,, GB.GarmDesc Wearer.Labl, Wearer.Aloc01, Wearer.ChrgQnty01 FROM Garment GA, Garment GB, Wearer WHERE Wearer.GTypRcID01 = GA.GTypRcID AND Wearer.GTypRcID02 = GB.GTypRcID AND ((Wearer.OrdrRcID=269) AND (Wearer.Nact=0)) Pete- Hide quoted text - - Show quoted text - Do you have indexes on the GTypRcID in the Garment table and ordrRcId in wearer? How big are the 2 tables. 3 minutes is long for something simple but then I have been working on some queries today that take 45 minutes. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query Joins | Excel Discussion (Misc queries) | |||
Excel query via ODBC, left joins on multiple tables | Excel Discussion (Misc queries) | |||
MS SQL Server Joins Concept | Excel Worksheet Functions | |||
Outer Joins | Excel Discussion (Misc queries) | |||
Multiple joins in an Excel Query | Excel Programming |