Tengel
Posts: 49
|
| Posted: 11/11/2004, 5:41 AM |
|
this might be very easy, but I cant figure this out.
lets say I have 2 table, where table1 have ID from 1 to 10 and table2 have ID from 2 to 10.
What I try is to make it so it only show ID nr 1 from table1 as that on is not on second table.
Does any have answer how to do this?
_________________
---
Tengel |
 |
 |
Roman
|
| Posted: 11/11/2004, 6:50 AM |
|
Hi Tengel,
So, you would like to get all id values from Table 1 that do not exist in Table 2, correct?
SELECT distinct t1.id
FROM table_1 as t1
LEFT JOIN ON table_2 AS t2
WHERE t1.id <>t2.id
or
SELECT distinct t1.id
FROM table_1 as t1
WHERE NOT EXISTS
(SELECT t2.id FROM table_2 as t2 WHERE WHERE t1.id <>t2.id)
If you go against Oracle database
SELECT t1.id
FROM table_1 as t1
MINUS
SELECT t2.id
FROM table_2 as t2
(code was not tested)
Regards
Roman
|
|
|
|
Tengel
Posts: 49
|
| Posted: 11/11/2004, 7:01 AM |
|
Thats correct Roman, But first statment you postet, I have try, and that will just add up alot of same code. so non equal ( <> ) does not work in this cache, But I will try your second one with WHERE NOT EXIST. I hope that will work.
_________________
---
Tengel |
|
|
|
Roman
|
| Posted: 11/11/2004, 7:48 AM |
|
Tengel,
Try this one.
SELECT distinct t1.id
FROM table_1 as t1
WHERE NOT EXISTS
(SELECT t2.id FROM table_2 as t2 WHERE WHERE t1.id =t2.id)
|
|
|
|
Tengel
Posts: 49
|
| Posted: 11/11/2004, 3:33 PM |
|
your statment just gives syntax error
_________________
---
Tengel |
|
|
|
Roman
|
| Posted: 11/11/2004, 3:50 PM |
|
Remove duplicated WHERE from the last query. Sorry, I overlooked it.
|
|
|
|
Tengel
Posts: 49
|
| Posted: 11/11/2004, 5:19 PM |
|
I did that, and it will give the same error, try it in a query builder, and you will see it
_________________
---
Tengel |
|
|
|
peterr
Posts: 5971
|
| Posted: 11/11/2004, 5:22 PM |
|
Possibly your database doesn't support such syntax. It may help if you posted your database and version.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
mrachow
Posts: 509
|
| Posted: 11/12/2004, 12:38 AM |
|
This should work for a database that cannot do subselects
select A.ID from A
LEFT JOIN B ON A.ID = B.ID
WHERE A.ID is null
Regards,
Michael
_________________
Best regards,
Michael |
|
|
|
Tengel
Posts: 49
|
| Posted: 11/12/2004, 9:14 AM |
|
Well, that was I thought as well Michael, also in my query builder at ( Navicat ) this shows me correct results.
In this cache I make a DB for English against Norwegian.
Where English table have 3 ID more then Norwegian one.
Correct sql is:
SELECT english.id AS english_id
FROM english LEFT JOIN norsk ON
english.id = norsk.id
WHERE norsk.id IS NULL
in CCS Visual Query builder, I have this:
SELECT english.id AS english_id
FROM english LEFT JOIN norsk ON
english.id = norsk.id
WHERE norsk.id IS NULL
This should be the same; in my Navicat query builder it shows only those 3 ID English table have more. (ID: 1, 246 and 2000)
But when CCS generate php page, it shows all the codes.
http://awm.3d-nordic.com/norwegian.php
Shouldnt this only show those 3?
_________________
---
Tengel |
|
|
|
Tengel
Posts: 49
|
| Posted: 11/12/2004, 9:23 AM |
|
Peterr, my Mysql is ver. 3.23.49 PHP Version 4.3.7
_________________
---
Tengel |
|
|
|
peterr
Posts: 5971
|
| Posted: 11/12/2004, 1:25 PM |
|
I'm not sure why this happens.
Please debug and check if this SQL is indeed executed. See http://docs.codecharge.com/studio/html/ProgrammingTechn...yingOutput.html
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Tengel
Posts: 49
|
| Posted: 11/13/2004, 5:00 AM |
|
Well, I did figure out what happend. in code this was created:
$this->SQL = "SELECT english.id AS english_id " .
"FROM english LEFT JOIN norsk ON " .
"english.id = norsk.id ";
When I change last line as
"english.id = norsk.id where norsk.id is null";
it work as it should. have no idea why this not happend, when sql statment have that norsk.id is null.
_________________
---
Tengel |
|
|
|
