Selecting items from one table that is not found in another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Selecting items from one table that is not found in another

Post by james20 »

I have two tables and I want to pull all values from the first table that do not have their ID in the second table.

To get all the items from table1, I do this (which works fine):

$db->CacheGetAll("SELECT * FROM `{$tables['items']['name']}` WHERE {$user_where}");

TO get all the items from table2, I use this query:

$db->CacheGetAll("SELECT * FROM `{$tables['item_edits']['name']}` WHERE {$user_where}");



The table 'items' has a field 'ID' that I want to compare with the field 'ITEM_ID' in the 'item_edits' table. If the 'ITEM_ID' in any row in 'item_edits' equals the 'ID' field from the 'items' table, then I want to exclude those from the query.

I have tried different things, but without any success. Can someone help me on getting this query right?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

you should read up on joins, which allow you to fetch data from multiple tables by stating (or not) the relationships between them (such as the one you gave between the items and item_edits tables).
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

I looked at join, but it doesn't seem to do what I want. This is the closest I got, but there's syntax errors I can't figure out:


"SELECT * FROM `{$tables['items']['name']}`,`{$tables['item_edits']['name']}` WHERE
`{$tables['items']['name'].USER_ID}` = '{user_id}' AND
`{$tables['items']['name'].ID}` != `{$tables['item_edits']['name'].ITEM_ID}`";
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

You can't quote both the table and the column together with back-ticks, they need to be quoted separately (this is probably the syntax error you are receiving).
In addition, your second condition is not a good one. Try running this query:

[sql]SELECT * FROM `items`LEFT JOIN `item_edits` ON `items`.`ID` = `item_edits`.`ITEM_ID`WHERE `items`.`USER_ID` =  {user_id} AND `item_edits`.`ITEM_ID` IS NULL[/sql]

By the way, in the future please wrap your queries with [sql] tags
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

Thanks a lot! That works perfectly when I query through phpMyAdmin, but for some reason the EXACT same code will not work when run through php. I know for a fact my syntax is correct and my looping is fine.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

can you paste here the code you have?
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

query:

Code: Select all

$results = $db->CacheGetAll("SELECT * FROM `items` LEFT JOIN `item_edits` ON 
                                `items`.`ID` = `item_edits`.`ITEM_ID` WHERE 
                                `items`.`USER_ID` = {user_id} AND 
                                `item_edits`.`ITEM_ID` IS NULL");
 
make the variable available in my template:

Code: Select all

 
$tpl->assign('results', $results);
 

loop through each item (smarty code):

Code: Select all

 
   {foreach from=$results item=elem name=items}
    {$elem.ID}<br />
   {/foreach}
 

I use the exact same method for all of my queries and things work fine, except for this one. Again, the query you provided works perfect when I run it through phpMyAdmin. I can't spot any reason why it shouldn't be working.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

In your query, you forgot the dollar sign in front of the variable.

Code: Select all

{user_id}
should be

Code: Select all

{$user_id}
This is why I use concatenation instead of the curly brackets to add variables to a string.

Code: Select all

"`items`.`USER_ID` = " . user_id . " AND "
Would have given you a warning
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

Thanks. That gets me to the point of being able to look the correct number of times, but there's still a slight problem.

I tried:

Code: Select all

 
{foreach from=$results item=elem name=items}
    ID: {$elem.ID}<br />
   {/foreach}
 

and it just prints "ID:" eight times. So it loops the correct amount of times, but I'm not able to pull the data like I used to.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

I don't use smarty, so I can't help you with smarty-specific syntax. perhaps someone else here knows more.
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

Well, that smart code works fine if I do a regular query. Do you think the Join may be returning a different "style" of record set?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

You are using some sort of database abstraction library, so it's possible. Can't you dump the results to see what they return?
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

Yes. I can loop through and print the key, value pairs. The key is the field in the database (such as ID or ITEM_ID) and the value would be what's in there. It prints all of the keys for each iteration just fine, but there's no value to go along with it.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Selecting items from one table that is not found in another

Post by Eran »

Try to change the beginning of the query to indicate the table the columns are selected from:
[sql]SELECT items.* FROM ...[/sql]
If that doesn't work, try to specify the columns you want instead of using the wildcard
james20
Forum Newbie
Posts: 9
Joined: Fri Aug 21, 2009 8:54 am

Re: Selecting items from one table that is not found in another

Post by james20 »

pytrin, Specifying the table before the wild card worked! Thanks, a lot!!
Post Reply