• 0

[MySql] Using two joins in a query


Question

Here is my situation.

One database, let's call it "members" and another called "orders".

Now, the "orders" database saves all the usernames as opposed to saving their real names, in case they make changes to them.

I'm trying to use a JOIN query to grab their real name. but since the table is saving two different usernames, I can't figure out how to grab both their real names in a single query.

So far I have this to grab one of their names:

mysql_query("SELECT orders.*, members.Username, members.realname FROM orders JOIN members WHERE orders.Username = members.Username 

This code works for 1 of the fields, but the other field, called "Assigned" doesn't want to grab their real name.

Link to comment
Share on other sites

7 answers to this question

Recommended Posts

  • 0

First of all... make sure that each table should have a primary key. In the members table, an auto incremental member_id can be used as the primary key.

This key can then be used as a foreign key in your orders table - this will cut down query time and allow members to change their username in the future.

Also, break down the members realname into forename and surname, might come in handy later.

This query should do the trick...

You were creating a cartesian join in your query - very resource intensive.

SELECT members.member_id, members.forename, members.surname, orders.* FROM members JOIN orders ON members.member_id = orders.member_id

Dont know what you mean by the last sentence... Is "assigned" another table in your databse?

Link to comment
Share on other sites

  • 0

First of all... make sure that each table should have a primary key. In the members table, an auto incremental member_id can be used as the primary key.

This key can then be used as a foreign key in your orders table - this will cut down query time and allow members to change their username in the future.

Also, break down the members realname into forename and surname, might come in handy later.

This query should do the trick...

SELECT members.member_id, members.forename, members.surname, orders.* FROM members JOIN orders ON members.member_id = orders.member_id

Dont know what you mean by the last sentence... Is "assigned" another table in your databse?

I have that schedule to update in the future, but in the middle of the code, I can't make huge changes until everything is working.

This query doesn't help me, I already have that working :p

I need orders.assigned = members.username to be included in the query, but I can't get it to work.

Ex.:

mysql_query("SELECT orders.*, members.Username, members.realname FROM orders JOIN members WHERE orders.Username = members.Username AND orders.assigned = members.username

for instance..

Link to comment
Share on other sites

  • 0

Do you mind posting the schema for the two tables and list a few possible values...

BTW, just updated my previous post. You should definitely use a real join rather than a cartesian join.

Link to comment
Share on other sites

  • 0

Just pretend there is two columns in "orders" and they are "username" and "assigned".

Both contain different usersnames of members found inside the "member" database..

Link to comment
Share on other sites

  • 0

Just pretend there is two columns in "orders" and they are "username" and "assigned".

Both contain different usersnames of members found inside the "member" database..

Ok, well that query is never going to work then. Only the rows that satisfy both conditions are returned...

Your query will only return the rows where the orders.username is the same as the orders.assigned.

What is the purpose of this query anyway?

Link to comment
Share on other sites

  • 0

If you want to use a table more than once in a query you need to assign each instance a unique table alias (member1 and member2).

SELECT orders.*, member1.realname AS ordered_realname, member2.realname AS assigned_realname FROM members AS member1 JOIN members AS member2 JOIN orders ON member1.username = orders.username AND member2.username = orders.assigned

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.