MySQL query with join, two tables with "id" field

[eluser]internut[/eluser]
Hey All,

I have which works great:

Code:

$data['query']   = $this->db->query("SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `groups_exp` WHERE `group_id`='$filter_group' AND status='$user_status' AND $field LIKE '$keyword') ORDER BY $sort $direction LIMIT $start,$config[per_page] ");     // query time

Only issue i'm having is both tables have an "id" field. And on a LIKE search its grabbing the "id" field of the table i'm joining, not the original.

I want if LIKE '$keyword' to be the "id" of table "users", not "groups_exp"

Any thoughts?

[eluser]crumpet[/eluser]
why not
users.id
groups_exp.id
?

[eluser]gullah[/eluser]
I had my tables set up like this and it will be a lot less confusing when handling results if you name your fields uniquely. It is a major pain when you are trying to pick out the users.id field when you could just have user_id and group_exp_id.

[eluser]internut[/eluser]
[quote author="drewtown" date="1228211206"]I had my tables set up like this and it will be a lot less confusing when handling results if you name your fields uniquely. It is a major pain when you are trying to pick out the users.id field when you could just have user_id and group_exp_id.[/quote]

I questioned myself about this about having multiple tables with an "id" field for each record number.

Is it a bad practice?

[eluser]gullah[/eluser]
you could do it with aliases or table.field, what is most important is that you follow a convention, be it yours or a standard. I personally like to use absolutely unique primary field names to avoid confusion and make sure there is no ambiguity.

Take a look at this, provides some conventions and links to other standard conventions.
http://justinsomnia.org/writings/naming_...tions.html

[eluser]kgill[/eluser]
Ok, let's see if we can't shed a little light on things here.

First you're using an IN clause & subquery in place of a join, for the most part that's horribly inefficient.

Second, the reason you're getting the id from the groups_exp is because that's what's in your subquery - let's dissect it a bit here:

Code:

SELECT *
FROM `users`
WHERE `id` IN -- this is the only filter on the users table, the id must be in the subquery
  (
    SELECT `user_id`
    FROM `groups_exp`
    WHERE `group_id`='$filter_group' -- these 3 clauses
    AND status='$user_status'        -- apply to the table
    AND $field LIKE '$keyword'       -- groups_exp
  )
ORDER BY $sort $direction
LIMIT $start,$config[per_page]

If you wanted your LIKE clause to apply the the users tables you'd need to move it out of the subquery. The other issue is the naming, as the others in this thread have said when you have two fields with the same name you should either reference the table name or use an alias.

e.g.

Code:

SELECT users.name, groups_exp.name
FROM users INNER JOIN groups_exp ON users.id = groups_exp.id

Using an alias:
SELECT a.name, b.name
FROM users AS a INNER JOIN groups_exp AS b ON a.id = b.id

Finally, this is pretty basic SQL, if you plan on doing any further development the best thing you can do for yourself is to pick up a primer and read up a bit, you'll save yourself some major headaches.

- K

[eluser]m4rw3r[/eluser]
You don't need to select things to be able to use them in a WHERE or ON clause.
Select does only "filter" what to return.

[eluser]kgill[/eluser]
I think you're referring to my example there, I guess I didn't make it quite clear what I was attempting to show. You're correct, I was just tossing out a quick example of how to deal with two fields with the same name, it was late and I didn't consider how my choice of fields could be confusing.

Fixed now, id's have become names.

- K

[eluser]m4rw3r[/eluser]
In the case of names, the columns cannot have the same name or they will overwrite each other.

Examples:

Code:

SELECT users.name, groups_exp.name FROM...
// returns only a single column: name
// (the table name is ignored in the binary drivers, hence is the second column overwriting the first)

SELECT users.name AS user_name, groups_exp.name AS group_name FROM...
// returns two columns: user_name and group_name

SELECT users.*, groups_exp.name AS group_name FROM...
// returns many columns: all columns in the users table and also "group_name"
// (unless "group_name" already exists in users table)

So alias your columns if you have duplicate names.

[eluser]internut[/eluser]
m4rw3r,

Good post, very clear. I did wind up going through my code / tables and making all the ID fields
unique just so this is not an issue. Thats the better way to go anyway.

Now I'll work on doing the new join code today from the slop I originally had.