473,323 Members | 1,589 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,323 software developers and data experts.

problem running a query

Hi All,

I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco

Feb 6 '07 #1
6 1680
On 6 Feb 2007 01:15:24 -0800, Za****@gmail.com wrote:

You'll need to decide what to do with people with multiple jobs. Show
only the first one? The last one? The best paying one? The one with
the highest title (beware for ties)? etc.
Then create a query that pulls that job, and in the final query join
with it, rather than with the jobs table.

-Tom.

>Hi All,

I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco
Feb 6 '07 #2
Tom,

What if I just wanted to have the first instant of the person,
regardless what kind of job it shows.
How would I have to define the query. I tried LEFT, RIGHT and INNER
JOIN but no luck.
I end up with too much or too less records.

Marco

On 6 feb, 17:50, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On 6 Feb 2007 01:15:24 -0800, Zai...@gmail.com wrote:

You'll need to decide what to do with people with multiple jobs. Show
only the first one? The last one? The best paying one? The one with
the highest title (beware for ties)? etc.
Then create a query that pulls that job, and in the final query join
with it, rather than with the jobs table.

-Tom.
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Feb 6 '07 #3
Hi Marco,
The join is probably not the issue here. Look into aggregation (min,
max, first, last, sum, group by, etc). In your query builder, look
for the sigma icon in the main menu at the top of your window.

select person, min(job) from table
group by person

that will give you one person and the minimum job name.
HTH
P

Feb 6 '07 #4
Hi,

u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.

"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"

the Jobs.JobId i have used refers to any primary key field of the Jobs
table.
Regards,
Seema
On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,

I have a problem with running a query.

Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "

Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.

Regards
Marco

Feb 7 '07 #5
On 7 feb, 10:28, seemanthi...@gmail.com wrote:
Hi,

u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.

"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"

the Jobs.JobId i have used refers to any primary key field of the Jobs
table.

Regards,
Seema

On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -
I tried the query but get an error message saying: syntax error.

Marco

Feb 8 '07 #6
On Feb 8, 10:32 am, Zai...@gmail.com wrote:
On 7 feb, 10:28, seemanthi...@gmail.com wrote:


Hi,
u can try with the below query. However if the result set, in a real
time scenario is very heavy, then i suggest this query not be used,
since i have used "IN" as well as "Group By" clauses.
"SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID AND Jobs.JobId IN (SELECT MIN(JobId) FROM
Jobs GROUP BY Jobs.BJID)"
the Jobs.JobId i have used refers to any primary key field of the Jobs
table.
Regards,
Seema
On Feb 6, 2:15 pm, Zai...@gmail.com wrote:
Hi All,
I have a problem with running a query.
Str = "SELECT Biographies.BID, Biographies.family_name AS [family
name], " & _
"Biographies.first_name AS [first name],
Biographies.sonof AS [son of], " & _
"Biographies.alias, " & _
"Biographies.pob, Biographies.dob,
Biographies.nationality, " & _
"Biographies.ethnicity, Biographies.religion,
Biographies.sex AS gender, " & _
"Biographies.age, Biographies.height,
Biographies.weight, " & _
"Biographies.eyes, Biographies.hair,
Jobs.position_title AS [position], " & _
"Jobs.employer FROM Jobs RIGHT JOIN Biographies ON
Jobs.BJID=Biographies.BID "
Using my query this way gives me 270 records, where I have only 262
in
the table.
I think it happens because some persons have multiple Jobs, but I
don't know how to prevent them being showed more then once.
Regards
Marco- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -

I tried the query but get an error message saying: syntax error.

Marco- Hide quoted text -

- Show quoted text -
Please check whether you have a field called JobId in your Jobs
table... if not please use the primary key column of the Jobs table
instead of JobId.

also can you please provide more details about the syntax error u have
received?

Feb 8 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: thomas-lists | last post by:
Hey folks, before posting to the bugs-list I would like to try it here to get some help :). Every now and then MySQL-4.0.13 crashes on my Dual-Athlon-MP-machine. Its not reproducible nor I know...
6
by: Vance Kessler | last post by:
I am sure this is a configuration or permissions problem, but I cannot figure out what it might be. I have 2 SQL 2000 database servers: one is a linked Windows 2003 based server using a...
8
by: Squirrel | last post by:
Hi everyone, I've created a mail merge Word doc. (using Office XP) , the data source is an Access query. Functionality I'm attempting to set up is: User sets a boolean field to true for...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
7
by: Salvador | last post by:
Hi, I am using WMI to gather information about different computers (using win2K and win 2K3), checking common classes and also WMI load balance. My application runs every 1 minute and reports...
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
6
by: billmiami2 | last post by:
I'm experiencing a strange problem that I believe is related to ADO.NET but I can't say for sure. I have a simple ASP.NET reporting interface to a SQL Server 2000 database. One report that we...
29
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
9
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
9
by: Dave | last post by:
Hi guys, I have just set up a duplicate server running: apache 2.54, mysql 5.04 and php 5.04 This is the same setup as as the server we are using now, apart from the hardware inside. I have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.