SQL to One to Many

1

Hello All,
I am looking for
B.T as BT and A.ID or AID
BT, AID
ETz 111
ETz 111
ETz 111

ETl 121

ETx 333
ETx 333

CDx 331
CDx 331
I have 2 tables, one two many
Table A containing a ID
and Table B containing the many table
Table B contains the ID that is the same ID from Table A but can contain many.
with these sting search conditions WHERE TYPE LIKE '%ET%' and Type != 'CD'
and order by A.ID to order descending to hope to find duplicates to prove one to many
I expect to have possibly duplicates in the ID in Table A. can you please help is this query correct.

Select A. ID as AID, B.T as BT
from A
full outer join B ON A.ID=B.ID
WHERE TYPE LIKE '%E%' and Type != 'CD'
order by A.ID

is the query correct? is the outer join the correct join? any other way
Also if possible. Get a count of the many in the same query. Thank you

any suggestions

2 

Select A. ID as AID, B.T as BT, BCount
from A
full outer join (
    Select ID, T, COUNT(*)
    From B
    WHERE TYPE LIKE '%E%' and Type != 'CD'
    Group By ID, T
) as B ON A.ID=B.ID
order by A.ID
3

HI Scott thank you for your help

I go this error, No column name was specified for column 3 of B

4

OOPS, sorry, quite right:

Select A. ID as AID, B.T as BT, BCount
from A
full outer join (
Select ID, T, COUNT(*) as BCount --<<--
From B
WHERE TYPE LIKE '%E%' and Type != 'CD'
Group By ID, T
) as B ON A.ID=B.ID
order by A.ID

5

Thank you Scott. It worked. thanks

6

It'll be interesting to see how performance is as the row counts go up. Leading wildcards can be really tough on queries. Can you simply not include the leading wildcard and just search for 'ET%' or 'E%' depending on what you're looking for).