Jump to content
  • Advertisement
Sign in to follow this  
chbrules

[MSSQL] JOIN Agony

This topic is 4870 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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

Share this post


Link to post
Share on other sites
Advertisement
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.

Share this post


Link to post
Share on other sites
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):

/* Additional emails */
select distinct c4.contact, c4.phone, c2.contsupref+isnull(c2.address1, '') email, organizationname, industry, c3.state, companyid
from gm_sales.dbo.contact1 c1
join 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.recid
join
(
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.

Share this post


Link to post
Share on other sites
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:

select distinct c4.contact, c4.phone, c2.contsupref+isnull(c2.address1, ') email, organizationname, industry, c3.state, companyid
from gm_sales.dbo.contact1 c1
join 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.recid
join 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] C1

join
{
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.Email
FROM customers INNER JOIN contacts ON customers.companyid = contacts.companyid
WHERE OrganizationName LIKE '%CFP%' OR OrganizationName LIKE '%IRA%'

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!