By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Query help joining 2 tables

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.