Inner Join 1

[Jimmy2128]

I am creating a query with the following spects.

BT_ID = Member ID
BT_IDCompanyName = Company Name
ST_ID = Member ID
ST_IDCompanyName = Company Name

The query pulls the Advertiser and Advertising Agencies, If the member advertise directly with us the BT_ID and ST_ID will be the same including CompanyName===
but if it comes thru an Advertising Agency the BT_ID is assigned to the Agency and ST_ID is assigned to the Member(Advertiser).

I wrote the following

Select o.ST_ID,n.Company,o.BT_ID, n.Company
FROM name AS n INNER JOIN orders AS o
ON n.ST_ID = o.ST_ID

The problems is when o.ST_ID is different than o.BT_ID the company name remains the same. Canyou please assist.

Regards,

Jimmy

 

[BSman]

The tables and their layouts are unclear, so I have to make some major assumptions.

Are you trying to display the Advertising agency name and the company name for each order? If so, then create a query using the design grid. Order would be the main table and (assuming that the companies and advertising agencies are in the same lookup table) you would use the table that lists the companies/agencies twice, once to link with BT_IDCompanyName and the other to link with ST_IDCompanyName.

Bob S.

 

[bborissov]

I hate one letter aliases

SELECT Name.ST_ID,
       Orders1.Company,
       Name.BT_ID,
       Orders2.Company
FROM Name
LEFT JOIN Orders AS Orders1 ON Name.ST_ID = Orders1.ST_ID
LEFT JOIN Orders AS Orders1 ON Name.BT_ID = Orders2.ST_ID

Borislav Borissov