Adding another select statement to a LEFT JOIN select statement

Question

I have the following select statement:

SELECT
  *
FROM
  jobs
  LEFT JOIN bids
    ON jobs.userID = bids.jobID
WHERE
  bids.bidder = '$userName'

But this statement does only show the results from the "jobs" table. I also want to show results from the "bids" table, for example the column "bids" from the table bids.

How do I combine that in the above select statement?

What is the structure of the tables jobs and bids? What is the definition of the foreign key between the two tables? What does 'show the results' mean? The * shows all columns from the tables listed in the FROM clause. — Pred
– Pred, Commented Sep 15, 2014 at 9:07
You should see the results of the two tables with your current query. — Jérôme Teisseire
– Jérôme Teisseire, Commented Sep 15, 2014 at 9:14

Edi G. · Accepted Answer · 2014-09-15 09:15:20Z

1

The * in Select * From... shows only the columns from your From-line. If you wanna Columns from your join-table, you have to add the column like this: bids.. if you use a alias (b and j in my example) it is easier to reading the statement... you also can use a "wildcard" in your select statement for your jointables.. like this bids.* (b.*)

in your case you need this:

   SELECT j.*, b.* 
     FROM jobs j
LEFT JOIN bids b
       ON j.userID=b.jobID 
    WHERE b.bidder = '$userName'

edited Sep 15, 2014 at 9:15

answered Sep 15, 2014 at 9:07

Edi G.

2,4207 gold badges25 silver badges36 bronze badges

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

3 Comments

Pred Over a year ago

@EdiG. Can you please send a link to us about this behaviour of the MySQL or PHP?

Edi G. Over a year ago

@Pred this is only MySql(Sql)-Symtax i hope it will help: dev.mysql.com/doc/refman/4.1/en/join.html

Pred Over a year ago

I know the JOIN syntax. The linked page does not mention that you have to use aliases for the joined tables. I know that your answer solved the OP's problem, but the explanation is a bit insufficient. At the end of the linked page (before the comments), the examples are using * in the SELECT clause and there are no aliases.

Collectives™ on Stack Overflow

Adding another select statement to a LEFT JOIN select statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related