473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How to correctly use join i sql queries

28
Hi,

I am working on a project that requires me to generate reports from multiple tables, namely:
  • School
  • Student
  • Team
  • Province
  • Entry
  • Language
  • Game

I want to add data for the records that are related and that were created on a particular day and when i use inner join, i get between 7200 and 19000 but these records should not be more than 200, can you please take a look and help me refine the query
Attached Files
File Type: txt Query.txt (2.5 KB, 292 views)
File Type: xlsx Entries(2012_1_13).xls.xlsx (850.5 KB, 1725 views)
Feb 10 '12 #1
3 1661
jhardman
3,406 Expert 2GB
every table in your query is tied to the [school] table except for the [entry] table and the [game] table. these are tied to each other, but not to the rest of the tables. notice this line:
Expand|Select|Wrap|Line Numbers
  1. FROM [JSESchoolsChallenge].[dbo].[entry] 
  2. inner join [JSESchoolsChallenge].[dbo].[school]  on [JSESchoolsChallenge].[dbo].[school].schoolid = [JSESchoolsChallenge].[dbo].[school].schoolid
perhaps you meant to type
Expand|Select|Wrap|Line Numbers
  1. FROM [JSESchoolsChallenge].[dbo].[entry] 
  2. inner join [JSESchoolsChallenge].[dbo].[school]  on [JSESchoolsChallenge].[dbo].[school].schoolid = [JSESchoolsChallenge].[dbo].[entry].schoolid
Let me know if this helps.

Jared
Feb 10 '12 #2
Rettla
28
@jhardman
Hi Jared,

I did that deliberately since I need values from the school table to appear on the report and there is no column in the entry table to tie to the school table. If I omit:

inner join [JSESchoolsChallenge].[dbo].[school] on [JSESchoolsChallenge].[dbo].[school].schoolid = [JSESchoolsChallenge].[dbo].[school].schoolid

I get an error saying that the multipart variable [JSESchoolsChallenge]...[schoolid] is not bound

Thanx
Feb 11 '12 #3
jhardman
3,406 Expert 2GB
As far as I can see that line accomplishes nothing. Regardless, the problem you are getting is because you didn't tell how the entry table relates to the school table.

Jared
Feb 12 '12 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: jgalzic | last post by:
Hi, I'm having trouble doing joins correctly on two tables. I've read up a lot about the different types of joins and tried lots of variations on inner, outer, and left joins with no avail....
4
by: John Smith | last post by:
Isn't life a bitch! You know what you want but you don't know how to get it. I have produced 12 queries that calculate a payment profile over 12 months. For a number of the records (ie with...
4
by: Shahzad | last post by:
dear respected gurus, I would like to knew how to apply append,insert query for a self table where no primary keys issues. i do have problem say there are 5 rows of single record, this is data...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
1
by: Bob Alston | last post by:
I am trying to use an access sql statement in a module that does a left outer join on one table - joined to a table that has a selection criteria. The result is an inner join. If I do this in...
2
by: CSN | last post by:
Is there much difference between using subqueries and separating out them into separate queries? __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building...
17
by: bobo420 | last post by:
Hi! I have 4 tables (table1, table2, table3, table 4) I need to do select * from all four table and get them sorted all the tables have field named id, so I figured that's the field I should...
2
by: gubbachchi | last post by:
Hi, How to use foreign key with join queries. I have used the query SELECT first_name,last_name,user_email from Info,Registry WHERE user_id='1'; where user_id = foreign key...
1
by: Rich P | last post by:
Greetings, Left Join (outer join) queries are generally for excluding stuff. select t1.* from tblx t1 Left Join tbly t2 on t1.ID = t2.ID and t1.fld1 = t2.fld1 and t1.fld2 = t2.fld2... Where...
2
by: manmadhan | last post by:
hey im new for the database so plz anybody send info abt join queries
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.