Link to home
Start Free TrialLog in
Avatar of Jacques Geday
Jacques GedayFlag for Canada

asked on

Nested Select - Jet SQL

Hi Experts,
Trying to figure out this but unable. I will summarize as real data much more complex.

Table = Details mid
fields = over 60 among which the following: Vessel, Voyage, BL

the table key is an autonumber. for the same Vessel, Voyage and BL I could have several records.

WHAT I NEED
is 1 SQL statement  that can pull ALL the fields in the table that meet the following criteria:
Vessel + str(Voyage) = 'ABC 123' and count(BL)>1

How to achieve this ??
I was able to get the following:

SELECT BL
FROM `details mid`
WHERE vessel & str(voyage) = 'ABC 123'
GROUP BY BL
HAVING count(BL)>1
ORDER by BL

This SQL give me all the BL that have more that 1 instance of them which is excellent, now based on the results of this SQL statement I need ALL the fields that are associated with these short listed BL's how do I do this ??

If I try SELECT *, (SELECT BL ....) as A
I get you will get only 1 record !!!

Please help.
Regards
gowflow
ASKER CERTIFIED SOLUTION
Avatar of Deepak Chauhan
Deepak Chauhan
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jacques Geday

ASKER

YOU ARE SIMPLY a GENIUS deepakChauhan !!!! tks very much you saved me greatly.

Fantastic thank you
gowflow
a small amendment  here "WHERE vessel & str(voyage) = 'ABC 123' "

                                             >>   where vessel + str(voyage) = 'ABC 123'

If space between Vessel & voyage

>>  where vessel +' '+ str(voyage) = 'ABC 123'
for the where its fine it works well as it is. if was the double SELECT I had a problem with you opened large possibilities for me as I use SQL a lot in my applications that I develop but never had to go complex which is very good now as it will open a lot of new combinations for me. Tks again
gowflow