sql help

  • Thread starter Thread starter Daniel M
  • Start date Start date
D

Daniel M

Can someone tell me why the following 2 statements do not contain the same
amount of line items?

SELECT boardid, min(dateinput) AS FirstDate, min(id) AS FirstID
FROM boardid
WHERE [component reference]='NA' and ReasonCode='NA'
GROUP BY boardid;

SELECT *
FROM boardid AS t1, [Batterycheck pt3] AS t3
WHERE t1.boardid=t3.boardid and
t1.dateinput = t3.FirstDate and
t1.id=t3.FirstID
ORDER BY t1.boardid;

The first one just selects boardid, dateinput and id and the second one just
selects all columns where boardid, dateinput and id are the same as the first
query. I dont understand the results. The first one pulls 13868 records while
the second one pulls 13797 records. If i pull out the
't1.dateinput=t3.FirstDate' they pull the same amount of records.
 
The first query only pulls records from one table. The second pulls from two
tables and both must have same Id & dates. Apparently they do not.

Run an unmatched query on first query to [Batterycheck pt3] on dates and IDs.
 
I understand one comes from 1 table and the other from 2.

Query1 pulls from boardid
query2 pulls from boardid and query1.

I would expect them to have the same data. I pull boardid, id and date from
table boardid, then the next query i pull * from boardid if boardid, id and
date match boardid table and query1 which comes from the boardid table.

It shouldnt have data in query2 and not in query1?

KARL DEWEY said:
The first query only pulls records from one table. The second pulls from two
tables and both must have same Id & dates. Apparently they do not.

Run an unmatched query on first query to [Batterycheck pt3] on dates and IDs.

Daniel M said:
Can someone tell me why the following 2 statements do not contain the same
amount of line items?

SELECT boardid, min(dateinput) AS FirstDate, min(id) AS FirstID
FROM boardid
WHERE [component reference]='NA' and ReasonCode='NA'
GROUP BY boardid;

SELECT *
FROM boardid AS t1, [Batterycheck pt3] AS t3
WHERE t1.boardid=t3.boardid and
t1.dateinput = t3.FirstDate and
t1.id=t3.FirstID
ORDER BY t1.boardid;

The first one just selects boardid, dateinput and id and the second one just
selects all columns where boardid, dateinput and id are the same as the first
query. I dont understand the results. The first one pulls 13868 records while
the second one pulls 13797 records. If i pull out the
't1.dateinput=t3.FirstDate' they pull the same amount of records.
Click to expand...
Click to expand...
 
Did you run the unmatched query as I suggested?

Daniel M said:
I understand one comes from 1 table and the other from 2.

Query1 pulls from boardid
query2 pulls from boardid and query1.

I would expect them to have the same data. I pull boardid, id and date from
table boardid, then the next query i pull * from boardid if boardid, id and
date match boardid table and query1 which comes from the boardid table.

It shouldnt have data in query2 and not in query1?

KARL DEWEY said:
The first query only pulls records from one table. The second pulls from two
tables and both must have same Id & dates. Apparently they do not.

Run an unmatched query on first query to [Batterycheck pt3] on dates and IDs.

Daniel M said:
Can someone tell me why the following 2 statements do not contain the same
amount of line items?

SELECT boardid, min(dateinput) AS FirstDate, min(id) AS FirstID
FROM boardid
WHERE [component reference]='NA' and ReasonCode='NA'
GROUP BY boardid;

SELECT *
FROM boardid AS t1, [Batterycheck pt3] AS t3
WHERE t1.boardid=t3.boardid and
t1.dateinput = t3.FirstDate and
t1.id=t3.FirstID
ORDER BY t1.boardid;

The first one just selects boardid, dateinput and id and the second one just
selects all columns where boardid, dateinput and id are the same as the first
query. I dont understand the results. The first one pulls 13868 records while
the second one pulls 13797 records. If i pull out the
't1.dateinput=t3.FirstDate' they pull the same amount of records.
Click to expand...
Click to expand...
Click to expand...
 
I dont know what you mean about unmatched query. can you explain it?

KARL DEWEY said:
Did you run the unmatched query as I suggested?

Daniel M said:
I understand one comes from 1 table and the other from 2.

Query1 pulls from boardid
query2 pulls from boardid and query1.

I would expect them to have the same data. I pull boardid, id and date from
table boardid, then the next query i pull * from boardid if boardid, id and
date match boardid table and query1 which comes from the boardid table.

It shouldnt have data in query2 and not in query1?

KARL DEWEY said:
The first query only pulls records from one table. The second pulls from two
tables and both must have same Id & dates. Apparently they do not.

Run an unmatched query on first query to [Batterycheck pt3] on dates and IDs.

:

Can someone tell me why the following 2 statements do not contain the same
amount of line items?

SELECT boardid, min(dateinput) AS FirstDate, min(id) AS FirstID
FROM boardid
WHERE [component reference]='NA' and ReasonCode='NA'
GROUP BY boardid;

SELECT *
FROM boardid AS t1, [Batterycheck pt3] AS t3
WHERE t1.boardid=t3.boardid and
t1.dateinput = t3.FirstDate and
t1.id=t3.FirstID
ORDER BY t1.boardid;

The first one just selects boardid, dateinput and id and the second one just
selects all columns where boardid, dateinput and id are the same as the first
query. I dont understand the results. The first one pulls 13868 records while
the second one pulls 13797 records. If i pull out the
't1.dateinput=t3.FirstDate' they pull the same amount of records.
Click to expand...
Click to expand...
Click to expand...
 
The query wizard has an unmatched query but it will only check one field.
You can use it and then in design view drag the second field as you see the
first, double click the connecting line and select for all records from the
most side table. Pull down field from least side and set criteria to null.

Daniel M said:
I dont know what you mean about unmatched query. can you explain it?

KARL DEWEY said:
Did you run the unmatched query as I suggested?

Daniel M said:
I understand one comes from 1 table and the other from 2.

Query1 pulls from boardid
query2 pulls from boardid and query1.

I would expect them to have the same data. I pull boardid, id and date from
table boardid, then the next query i pull * from boardid if boardid, id and
date match boardid table and query1 which comes from the boardid table.

It shouldnt have data in query2 and not in query1?

:

The first query only pulls records from one table. The second pulls from two
tables and both must have same Id & dates. Apparently they do not.

Run an unmatched query on first query to [Batterycheck pt3] on dates and IDs.

:

Can someone tell me why the following 2 statements do not contain the same
amount of line items?

SELECT boardid, min(dateinput) AS FirstDate, min(id) AS FirstID
FROM boardid
WHERE [component reference]='NA' and ReasonCode='NA'
GROUP BY boardid;

SELECT *
FROM boardid AS t1, [Batterycheck pt3] AS t3
WHERE t1.boardid=t3.boardid and
t1.dateinput = t3.FirstDate and
t1.id=t3.FirstID
ORDER BY t1.boardid;

The first one just selects boardid, dateinput and id and the second one just
selects all columns where boardid, dateinput and id are the same as the first
query. I dont understand the results. The first one pulls 13868 records while
the second one pulls 13797 records. If i pull out the
't1.dateinput=t3.FirstDate' they pull the same amount of records.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top