SELECT [OrganizationName]
FROM [customers] C1
WHERE [OrganizationName] LIKE '%CFP%' OR [OrganizationName] LIKE '%IRA%'
join
{
SELECT [ContactName],[Email]
FROM [contacts]
WHERE [companyid] = [customers].[dbo].[companyid]
} [contacts] ON (C1.companyid = contacts.companyid)
The top 3 lines work just fine, it retrieves the OrganizationName column just fine, but when I get to the join statement, god knows if I can do that right. Basically, all I need is the 2 fields ContactName and Email from the Contacts table where the companyid's on both tables match from the latter half of the query. I tried using INNER JOIN but the errors were no different. Help? :<
[MSSQL] JOIN Agony
Again with the JOIN, I'm horrible with this. This is a far easier example for me to comprehend JOIN since I wrote this query myself. (Last time a peer of mine just spat out like a huge 20 line query I didn't even understand) The first half of my query works, but I need the second table's columns in this query, so that's where JOIN comes in.
You didn't respond the last time I tried helping you with this.
The short answer:
You do not understand the JOIN syntax, at all, not even close.
You really, really need to read some basic tutorials on JOIN. JOIN is used within the tables area of the select statement. A JOIN is in effect taking 2 or more tables and creating one big imaginary table, from which you then draw records.
SELECT ... FROM Table1 [JOIN Table2 ON ... [JOIN Table3 ON ...]] WHERE ... ORDER BY ....
The way you are trying to use JOIN right now is in fact how you would do a UNION, however your identifers make me believe you do in fact want to make a join (combine the information in the customers table with the corresponding information in the contacts table)
EDIT: To help you, the always useful for beginners w3schools JOIN tutorial.
The short answer:
You do not understand the JOIN syntax, at all, not even close.
You really, really need to read some basic tutorials on JOIN. JOIN is used within the tables area of the select statement. A JOIN is in effect taking 2 or more tables and creating one big imaginary table, from which you then draw records.
SELECT ... FROM Table1 [JOIN Table2 ON ... [JOIN Table3 ON ...]] WHERE ... ORDER BY ....
The way you are trying to use JOIN right now is in fact how you would do a UNION, however your identifers make me believe you do in fact want to make a join (combine the information in the customers table with the corresponding information in the contacts table)
EDIT: To help you, the always useful for beginners w3schools JOIN tutorial.
I've been reading some tutorials and I understand what it does, but I'm not sure how to actually impliment it, I don't know why. The reason my syntax looks like that is because I keep trying to look at one of the examples on of my peers wrote (which works perfectly fine):
See how the join is just sitting there? I don't get it either, but I guess MSSQL has some F'ed up proprietary syntax. I was even looking on MSDN for it, and they don't even have it like this guy did.
/* Additional emails */select distinct c4.contact, c4.phone, c2.contsupref+isnull(c2.address1, '') email, organizationname, industry, c3.state, companyidfrom gm_sales.dbo.contact1 c1join gm_sales.dbo.contsupp c4 on c1.accountno = c4.accountno and c4.rectype = 'c'join gm_sales.dbo.contsupp c2 on c1.accountno = c2.accountno and c2.contact like 'e-mail%' and c2.contsupref like '%@%' and c2.linkacct = c4.recidjoin( select distinct c1.companyid, c1.organizationname, c1.state, c3.industry from customers c1 join latestlsap c2 on c1.companyid = c2.usercompanyid and latestlsap = 1 join industrytypes c3 on c1.industryid = c3.id where customertype = 'enduser' and c2.expiredate >= getdate()) c3 on c1.u_key4 = c3.companyid
See how the join is just sitting there? I don't get it either, but I guess MSSQL has some F'ed up proprietary syntax. I was even looking on MSDN for it, and they don't even have it like this guy did.
What he is doing is making a table on the spot (a subquery). The be honest I would find it confusing too, which is why I always use AS syntax to clearly show that a table is being created or a column/expression is being [re]named.
To remove the confusion from his query creates, look at it like this:
In this case, "c3" is treated just like a table, the only catch is that he decided to declare what it is in that table on the spot:
(select ... from customers where ...) AS c3
The key difference to what you are doing however is that he is still putting that table inside the FROM area of the SELECT statement. You are placing the the WHERE section of your SELECT inbetween the first and second table (your second table being defined as a subquery).
Again, to refresh, a normal SELECT statement must go in this order:
SELECT columns
FROM tables
[WHERE conditions]
[GROUP BY columns]
[ORDER BY columns]
[HAVING conditions]
What you want to do is put both tables into the FROM section
And normally people would just write it like this:
To remove the confusion from his query creates, look at it like this:
select distinct c4.contact, c4.phone, c2.contsupref+isnull(c2.address1, ') email, organizationname, industry, c3.state, companyidfrom gm_sales.dbo.contact1 c1join gm_sales.dbo.contsupp c4 on c1.accountno = c4.accountno and c4.rectype = 'c'join gm_sales.dbo.contsupp c2 on c1.accountno = c2.accountno and c2.contact like 'e-mail%' and c2.contsupref like '%@%' and c2.linkacct = c4.recidjoin c3 on c1.u_key4 = c3.companyid
In this case, "c3" is treated just like a table, the only catch is that he decided to declare what it is in that table on the spot:
(select ... from customers where ...) AS c3
The key difference to what you are doing however is that he is still putting that table inside the FROM area of the SELECT statement. You are placing the the WHERE section of your SELECT inbetween the first and second table (your second table being defined as a subquery).
Again, to refresh, a normal SELECT statement must go in this order:
SELECT columns
FROM tables
[WHERE conditions]
[GROUP BY columns]
[ORDER BY columns]
[HAVING conditions]
What you want to do is put both tables into the FROM section
SELECT [OrganizationName]FROM [customers] C1join{ SELECT [ContactName],[Email] FROM [contacts] WHERE [companyid] = [customers].[dbo].[companyid]} [contacts] ON (C1.companyid = contacts.companyid)WHERE [OrganizationName] LIKE '%CFP%' OR [OrganizationName] LIKE '%IRA%'
And normally people would just write it like this:
SELECT customers.OrganizationName, contacts.ContactName, contacts.EmailFROM customers INNER JOIN contacts ON customers.companyid = contacts.companyidWHERE OrganizationName LIKE '%CFP%' OR OrganizationName LIKE '%IRA%'
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement