!@#$ Query with Joins???

[Stephen]

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!!!

 

[par_60056]

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

 

[Stephen]

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.

 

[Stephen]

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]

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!!!

 

[par_60056]

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!






- 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.