Select From Inner join - PHP/MYSQL

Question

I am struggling with a MYSQL query - I have 2 tables :

Table 1 (info) containing UID, first_name, last_name. Table 2 (card) containing UID, pic .

What I am trying to do is get all results into an array:

WHERE UID IN '$ids' AND LEFT(last_name,1) = '$letter' ORDER BY last_name, first_name ASC

I figured an INNER JOIN so my current code is:

("SELECT UID, first_name, last_name, pic FROM 
    (SELECT info.first_name,info.last_name,card.pic FROM info 
     INNER JOIN card ON info.UID=card.UID)
  WHERE LEFT(last_name,1) = '$letter' ORDER BY last_name, first_name ASC")

This is producing the following error though:

'Every derived table must have it's own alias'.

Am I going about this the right way with inner join, and how do I give the derived table an alias? Thanks in advance!

D'Arcy Rittich · Accepted Answer · 2012-02-08 15:49:26Z

2

select b.UID, g.first_name, g.last_name, b.pic
from user_data.general_info g
inner join user_data.Bcards b on g.UID = b.UID
where LEFT(g.last_name, 1) = '$letter'
order by g.last_name, g.first_name asc

answered Feb 8, 2012 at 15:49

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zac Over a year ago

Great stuff, Thank you very much. Apologies for the inconsistent table/DB names. But thanks for the solution. Much appreciated.

Roger · Accepted Answer · 2012-02-08 15:52:10Z

1

The inner query should be named.

    SELECT users.UID, users.first_name, users.last_name, users.pic FROM 
    (SELECT info.first_name,info.last_name,card.pic FROM user_data.general_info 
    INNER JOIN user_data.Bcards ON general_info.UID=Bcards.UID) users
    WHERE LEFT(users.last_name,1) = '$letter' ORDER BY users.last_name, users.first_name ASC

answered Feb 8, 2012 at 15:52

Roger

4181 gold badge5 silver badges11 bronze badges

Collectives™ on Stack Overflow

Select From Inner join - PHP/MYSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related