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

Query help joining 2 tables

Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,
LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' and
LOCSubClassHave.LCNT = '0')

This query seems to be ignoring the 'and' part of the clause.

Basically I want select from table1, table2 where LCNT in table1 is not
0 and
LCNT in table2 is 0.
I have verified the LCNT's numbers
This query returns 2 columns with 1700 rows
It needs to only find a few rows.
What am I missing, any ideas, thanks for any help.

Jul 23 '05 #1
4 1439
It looks like you need to specify an additional criteria to join the two
tables. Without that, your query will join EVERY row from the one table that
is <>0 to EVERY row in the other table that is = 0. Of course I can only
guess at your actual requirements so if you need more help with a query
please: 1) Post CREATE TABLE statements for the tables, 2) Post some INSERT
statements to generate a few rows of sample data, 3) Show us what result you
expect to get from that data.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
On 18 Jan 2005 11:09:51 -0800, sd********@msn.com wrote:
Select LOCGeneralHave.LSCH, LOCSubClassHave.LSCH from LOCGeneralHave ,
LOCSubClassHave Where (LOCGeneralHave.LCNT <> '0' and
LOCSubClassHave.LCNT = '0')

This query seems to be ignoring the 'and' part of the clause.

Basically I want select from table1, table2 where LCNT in table1 is not
0 and
LCNT in table2 is 0.
I have verified the LCNT's numbers
This query returns 2 columns with 1700 rows
It needs to only find a few rows.
What am I missing, any ideas, thanks for any help.


Hi sdowney,

Awfully hard to tell without knowing anything about your tables, data and
expected results. Please post your table structure (as CREATE TABLE
statements, including all constraints but excluding irrelevant columns), a
few rows of sample data (as INSERT statements) and the expected output
based on that sample data. Describe the rationale as well.

Check out this site for more info on how to post: www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3
your right it is joining the tables into one row

I need to rethink my method for what I want to do in this proceedure.
It really comes down to a logical thinking error on my part.
I'll let it simmer for a while
Thanks for the help

I am developing and maintaining a Library system at
www.members.cox.net/bookstorelibrary

Jul 23 '05 #4
In case it helps, here's a guess at what you actually intended, including
some sample data:

CREATE TABLE LOCGeneralHave (lsch INT PRIMARY KEY, lcnt INT NOT NULL)
CREATE TABLE LOCSubClassHave (lsch INT PRIMARY KEY, lcnt INT NOT NULL)

INSERT INTO LOCGeneralHave VALUES (100,1)
INSERT INTO LOCGeneralHave VALUES (200,1)

INSERT INTO LOCSubClassHave VALUES (100,1)
INSERT INTO LOCSubClassHave VALUES (200,0)

SELECT G.lsch, S.lsch
FROM LOCGeneralHave AS G,
LOCSubClassHave AS S
WHERE G.lsch = S.lsch
AND G.lcnt <> 0
AND S.lcnt = 0

--
David Portas
SQL Server MVP
--
Jul 23 '05 #5

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

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
3
by: Robert | last post by:
I am having performance issues on a SQL query in Access. My query is accessing and joining several tables (one very large one). The tables are linked ODBC. The client submits the query to the...
0
by: apb18 | last post by:
A bit of query plan strangeness. Suppose you have an inheritance tree such that the columns 'ID' and 'field' appear in the top level table, call that table XXX. tables YYY and ZZZ both inherit...
3
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
1
by: hmiller | last post by:
I'm sorry to populate the server with yet another question about linking multiple tables and queries, howerver I have not been able to find the right criteria. My problem. I am trying to...
5
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends,...
5
by: Bob Bridges | last post by:
Start with two tables, parent records in one and child records in the other, a one-to-many relationship. Create a select statement joining the two. Display the query in datasheet mode. When I...
2
by: Stephenoja | last post by:
Hello Guys, I have a problem that has to do with Aging Accounts Receivable. I have three tables with customer account numbers, customer names, customer balances for the years 2003, 2004 and...
4
by: dsdevonsomer | last post by:
Hello, I have one simple query joining two tables with left outer join on 3 fields and using MIN on two fields. These two tables have lot of data about 3 mil in total. I am trying to migrate db...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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.